Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

50+ tables need to be copied using Data Flow task. Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:27 AM
Points: 337, Visits: 456
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.




Joe
Post #1469464
Posted Tuesday, July 2, 2013 7:10 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:51 AM
Points: 702, Visits: 2,176
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




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1469472
Posted Tuesday, July 2, 2013 7:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 2,257, Visits: 2,719
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’! **
Post #1469474
Posted Tuesday, July 2, 2013 7:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:02 PM
Points: 2,818, Visits: 2,560
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.
Post #1469516
Posted Tuesday, July 2, 2013 8:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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

Post #1469530
Posted Friday, July 5, 2013 12:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:27 AM
Points: 337, Visits: 456
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.

Joe
Post #1470589
Posted Friday, July 5, 2013 1:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 2,257, Visits: 2,719
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’! **
Post #1470593
Posted Friday, July 5, 2013 1:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
dfine (7/5/2013)
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.


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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1470595
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse