Post reply

50+ tables need to be copied using Data Flow task.

  • July 2, 2013 at 7:00 am

    #295876

    Hi,

    I need to move data from one server to another server, there are 50+ tables to be copied.

    For this, I am creating a SSIS package; however creating Data Flow task for each table is going to be a massive work. Is there any easy way or workaround?

    Source and destination database is in SQL Server 2008.

    Thanks for your comments.

    [font="Calibri"]Raj[/font]

  • July 2, 2013 at 7:10 am

    #1629052

    So you need to copy 50+ tables....

    1) Do you need to do this once, and once only, or is this going to become a regular thing?

    2) How many tables are there in the database in total?

    Thanks

    Simon


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • July 2, 2013 at 7:12 am

    #1629054

    Perhaps you can use the import/export wizard to create an initial SSIS package. Save this package in the last step of the wizard instead of executing it. Modify the saved package to your needs by adding/changing steps and tasks.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 2, 2013 at 7:56 am

    #1629091

    Skip the data flow. Create a for each loop based on an enumeration of source tables. Inside the for each loop put a Execute SQL Task with your SQL built using a varialble with an expression using the table name enumerated from the source to define your source and target.

  • July 2, 2013 at 8:09 am

    #1629101

    I am guessing that becuase the source data structure is different for each table you can't just dynamically assign the ole data source because the metadata will be different for each run.

    As already suggested, is this a one off excercise or are you going to need to repeat it.

    can you make the two servers linked, if so then your best bet may be to generate a dynamic SQL statement which gets the field details from the sys.columns and you just pass the tablename through to the stored procedure, in which case SSIS is probably overkill and you might as well do it directly insite SMMS as a script

  • July 5, 2013 at 12:56 am

    #1629948

    [font="Arial"]Yes, this is one time exercise. I planned to go with SSIS package because as per the company policy I should not use linked server approach. There are 129 tables but I am only interested in 70 tables.[/font]

    [font="Calibri"]Raj[/font]

  • July 5, 2013 at 1:08 am

    #1629951

    If the destination database is created upon running your transfer, then there is another possibility. You can create a backup of the complete source database and restore this as the target. After the restore you can remove the 59 tables you don't need, leaving the 70 tables you want.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 5, 2013 at 1:10 am

    #1629952

    dfine (7/5/2013)

    [font="Arial"]Yes, this is one time exercise. I planned to go with SSIS package because as per the company policy I should not use linked server approach. There are 129 tables but I am only interested in 70 tables.[/font]

    Just use the import/export wizard, this will be the fastest.

    When the SSIS package is created for you, remove all precedence constraints to force the package to run in parallel.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply