SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Looping through multiple servers in SSIS

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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...