SSIS - ETL - Transfer tables/databases from many s

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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