CODEWITHSUNDEEP
sql-serverssisdata-warehouseetl
gregnorm
gregnorm

Reputation: 319

SSIS - ETL - Transfer tables/databases from many servers?

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.

Upvotes: 1

Views: 2250

Answers (3)

Ahmed
Ahmed

Reputation: 101

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.

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19184

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

Upvotes: 1

Preet Sangha
Preet Sangha

Reputation: 65476

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.

Upvotes: 0

Related Questions