I have a requirement to copy 300 + tables from various DIFFERENT SQL Servers to One Destination SQL Server B.
These tables have diff structures . I wanted to use SSIS packages to do this, using a DATA FLOW TASK AND FOR EACH LOOP To loop thru all tables and copy data over. However Data Flow task is not dynamic hence cannot use.
So I cannot do DFD Task since structures are NOT same on these 300 tables. ANy suggestion for the best approach to do this?
- Moved by Kalman Toth Friday, February 21, 2014 9:29 AM Better fit
Here is a Technet gallery script to data from one database to other. You can copy data between different servers too if you have a linked server setup.
You can try to use of
Shridhar J Joshi Thanks a lot
You can try to use the Import and Export Wizard from SQL Server Management Studio.
How to: Run the SQL Server Import and Export Wizard
TechNet Community Support
>However Data Flow task is not dynamic hence cannot use.
I don't know what you mean. SSIS is your best shot.
Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012
with SSIS DATA FLOW TASK, We have to have these fields ie metadata already defined. so if new request comes to download a new table, we have to every time setup DFD Task. DFD Is not dynamic so cannot use that task . Not sure what task to use in SSIS To bring over tables daily, without having the fields static in the package.