I have 6-7 identical databases (almost). I want to copy the data from some of the tables of EACH of these servers into the corresponding table of ONE server. That is, multiple sources and one destination server. All the servers have different IPs. How do I do this task ? Would for loop be appropriate for this. If yes, then what would be a good way to do it ?
I might perform a bit of Transform. Not sure as of now. To be safe, I want to use SSIS.
Here is an overview of how you can set up a FOR EACH LOOP to loop through the databases, provided the tables are indentical.
It is overkill though.
SSIS Loop a list of connection strings in a master package, executing two other packages for each connection string
If you insist on using SSIS then it's simple.
1 Package
containing x (number of tables) data flows
each of which contains a data source (sql for query + source db info) and a data destination (mapping of columns + dest db info).
If you want to add a transform or two then it's simply the case of adding it to the data flow.
You can use FOR LOOP but it may be overkill if your load frequency is small.
Alternatively simple way is to use one DATA FLOW TASK with multiple SOURCEs, combine then in UNION ALL transform and the load into destination.
