July 2, 2013 at 7:00 am
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.
July 2, 2013 at 7:10 am
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
July 2, 2013 at 7:12 am
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.
July 2, 2013 at 7:56 am
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
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
[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]
July 5, 2013 at 1:08 am
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.
July 5, 2013 at 1:10 am
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