This is just a quickie post to answer a question on how to loop through multiple servers in SSIS.  There are a lot of options I’m not going to go into here due to lack of time.   If someone expresses interest I’ll try to do a more detailed post later.

1. First create an OLE DB connection manager pointing to some random server.
   
2. Next create a FOR EACH LOOP container. 

   For simplicities sake I’m going to show this loop using a Foreach Item Enumerator.  Personally, I like to use a table and a Foreach from Variable Enumerator but it takes longer to explain. 

   1. Under the Collection “tab” change the Enumerator to “Foreach Item Enumerator”. 
   2. Add a single column.
   3. In that column list out the servers you want to loop through.

   

3. Under the Variable Mappings “tab” add a new variable called “ServerName” with a value type of “String”.  Make sure the Index is set to 0.
   
4. Next go to the properties of the connection manager.  Look for one called Expressions.  Select it and click on the ellipsis (…).
   1. Under properties select ServerName.
   2. Then Click on the ellipsis (…) under Expression.
   3. Select the variable User::ServerName and drag it into the Expression box.

   

At this point when you run your Foreach Loop Container each loop it makes will cause the Connection Manager ConnMan1 to point to a different server.

I realize this was rather fast, and I would have loved to have shown some sample output.  Unfortunately as I stated at the beginning I’m rather pressed for time today and want to get this out quickly to answer a post.