locked
Loading 50 tables from a source database to target database on daily basis RRS feed

  • Question

  • Hi,
       My requirement is,There are around 50 source tables in oracle source database, on daily basis, i have to load the 50 source tables to the target tables (same structure/name as Source tables) in target database. Before loading, I have to truncate all the data in the 50 target tables and then load the data from source tables..

    I am a learner in SSIS...Any suggestions from you expert guys..?
    Monday, September 7, 2009 10:00 AM

Answers

  • Thanks Nitesh...But if i have 50 tables, in the source abd dectination, how can I give 50 tables..normally we can ngive one table or view,right?....Pls clarify

    Hi,
    if you are just copying the data from DB1 to DB2(for multiple table:: having same table structure) then you can use import & export data.

    First way:

    1) go to DB1
    2) Right click:: select Tasks --> select export data

    OR

    Second way:

    1) Go to DB2
    2) Right click:: select Tasks --> select import data


    Save SSIS package for this. (save as file system.)
    if you want to edit that open this SSIS package in BIDS.


    Thanks-





    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    • Proposed as answer by Adam Tappis Tuesday, September 8, 2009 6:01 PM
    • Marked as answer by Bob Bojanic Monday, September 14, 2009 5:40 PM
    Monday, September 7, 2009 12:10 PM

All replies

  • Hi,
    You can use a truncate statement in one Execute SQL task in the control flow to truncate the detination table.

    After that u can have different DFT to move the data from each table to a destination table.

    Use an OLEDB Source, select the columns u wand to retrieve then use a OLEDB destination to dump this data in the detination.
    Hope this helps !!
    Please close the threads once answered

    Sudeep
    Monday, September 7, 2009 10:10 AM
  • If your source and destination are on same server then I would encourage the use of a SP with truncate and insert scripts.
    In case they are on different servers, you can use SSIS.
    Take a Execute SQL Task and truncate all the tables throught this.
    Then you can use DFT (data flow task) and inside DFT take Oledb Source and Oledb Destination and do the column mappings.

    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, September 7, 2009 10:21 AM
  • I agree with Nitesh.
    If the source and destination DBs are on the same server use an SP to get it done.
    Hope this helps !!
    Please close the threads once answered

    Sudeep
    Monday, September 7, 2009 10:26 AM
  • Thanks Nitesh...But if i have 50 tables, in the source abd dectination, how can I give 50 tables..normally we can ngive one table or view,right?....Pls clarify
    Monday, September 7, 2009 10:29 AM
  • hi Bavana,
    Are u using SSIS or SP?

    In both the cases you will have to write a script for each table.
    In case it is in an SP u can club them as one SP witht the truncate statement at the start.

    Had the tables metadata been the same for all the 50 tables u could use one DFT in a loop but that wont be the case.

    Hecne u need to have multiple OLEDB Sopurce & Destination, quite a tedious job.

    Else you could develop the package programatically and read the table schema from the source & destination & create the package.
    This way you have ur work a bit easy.
    Hope this helps !!
    Please close the threads once answered

    Sudeep
    Monday, September 7, 2009 10:34 AM
  • hi Bavana,
    Are u using SSIS or SP?
    In both the cases you will have to write a script for each table.
    In case it is in an SP u can club them as one SP witht the truncate statement at the start.
    Had the tables metadata been the same for all the 50 tables u could use one DFT in a loop but that wont be the case.
    Hecne u need to have multiple OLEDB Sopurce & Destination, quite a tedious job.

    Yes Sudeep,
    I also think that the source table's metadata wont be same.
    Bavana,
    You can have 50 truncate statements and all the 50 insert scripts in one SP.

    If you are using SSIS then you need 50 DFTs.
    You can reduce the number of DFTs by having 2 to 3 OLEDB Source and OLEDB Destinations.
    But again, I am not sure about the performance in later case.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, September 7, 2009 11:03 AM
  • Thanks Nitesh...But if i have 50 tables, in the source abd dectination, how can I give 50 tables..normally we can ngive one table or view,right?....Pls clarify

    Hi,
    if you are just copying the data from DB1 to DB2(for multiple table:: having same table structure) then you can use import & export data.

    First way:

    1) go to DB1
    2) Right click:: select Tasks --> select export data

    OR

    Second way:

    1) Go to DB2
    2) Right click:: select Tasks --> select import data


    Save SSIS package for this. (save as file system.)
    if you want to edit that open this SSIS package in BIDS.


    Thanks-





    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    • Proposed as answer by Adam Tappis Tuesday, September 8, 2009 6:01 PM
    • Marked as answer by Bob Bojanic Monday, September 14, 2009 5:40 PM
    Monday, September 7, 2009 12:10 PM
  • Use "Transfer SQL Server Objects Task" control flow item which provides a simple way to copy source tables to target tables if structure is same. It also gives an option to truncate and load.
    Tuesday, September 8, 2009 5:08 PM
  • I agree with ETL vs ELTL. The easiest way to circumvent tedious and repettative SSIS development (or in fact to learn) is to start off by using the import/export wizard and saving the result as a package and opening and editing in visual studio
    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)
    Tuesday, September 8, 2009 6:04 PM
  • Thanks Nitesh...But if i have 50 tables, in the source abd dectination, how can I give 50 tables..normally we can ngive one table or view,right?....Pls clarify
    You can have more than one truncate statement in an Execute SQL Task on the control flow, yes.
    Tuesday, September 8, 2009 6:06 PM