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
}