SSIS ForEach loop - change connection inside a for

Question

Task - There are 7 SQL servers, each of which have the same database. Consider a table Table_1 of the database. I want to take data from Table_1 of all the 7 servers and put it into Table_1 of Main server (called DataWarehouse in photo below). I created a data flow task to move data from one of these servers to main server.

Now, I want to put this data flow task inside a for loop and do the data flow from each of the 7 servers to the main server. How do I do it ?

Please see the attached photos below. I want my foreach loop to change the connection defined inside the data flow task "Data Mart - One Server" and then do the data transfer. Is this even possible ? If yes, then how ? I am hoping for something simple like we have in programming -

//pseudocode

foreach (String serverIP){

1 - connect to server using serverIP

2 - move data from server called serverIP to destination

}

Answer 1

I would probably goes for 6 data flow task and take the benefit of parallelism. Perhaps, if you are still want to go for a linear approach I would suggest you to make a dynamic connection manager for your OLEDB task of course with dynamic sql. (That would be time consuming, trust me, I had an experience with that). If you can linked those server then probably one SQL query can solve the problem for you.

Answer 2

Yes. I'm assuming that you can, as part of your foreach loop, populate a variable with the name of the server. From there, click on your connection manager and bring up the properties pane (hit F4 if it's not already up). Click on "Expressions" and set the ServerName property to your server name variable.