Looping through multiple servers in SSIS


October 31, 2012 by Kenneth Fisher

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.


Here are links to the other two posts in this series. Step three has the actual DTSX for download. You will have to rename it to get it to work as it has a DOCX extension on the end of it. I couldn’t figure out how to get a DTSX extension to load.

Looping through multiple servers in SSIS part 2
Looping through multiple servers in SSIS Part 3

15 thoughts on “Looping through multiple servers in SSIS

  1. Scott says:

    This all looks great, but I’m new to SSIS and I’m having a heck of a time getting connected to a dynamically defined Connection Manager. It won’t let me set up a Data Flow without screaming that it can’t connect to the database server because it has no idea what that full connection is.

    Any deeper help you can offer a frustrated newb to SSIS?

    • You start out by creating a Connection Manager that is pointing to a specific server/database. I would suggest using a development server. That way when SSIS goes through all of its verifications everything checks out. You don’t have it changing dynamically until execution time. I like to do my coding in layers. In your case I would first create the dataflow, and then only once that part is working add in the dynamic parts. FYI I have had little to no luck dynamically changing anything in the data flow other than the connection manager. Specifically changing the instance or database for database connections or filename for file connections isn’t a huge issue. Trying to change the table name, columns, mappings or anything else is a huge headache and while I have heard it can be done using a script I certainly haven’t done it.

      On a more general note I have a really good book on SSIS called Knight’s 24-Hour Trainer, Microsoft SQL Server 2008 Integration Services that I really like. I’ll go ahead and do a review on it this week and give a more specifics.

  2. swegs says:

    Do you know of a way to keep the loop from failing if it tries to connect to a server that it does not have access to?

    • Yep. Take a look at the MaximumErrorCount property. It will exist in each object and the package itself. You will want to increase it for both the package and any objects you are worried about. On the ones where I cycle through multiple servers I usually increase it to like 999 or something. If you do that adding some error handling would be a good idea also. I’ll try to post how to do that in the next few weeks.

  3. swegs says:

    I found that creating a script task that tested to see if the server was able to be connected to as a much more elegant solution. This allows me to still fail the package if I encountered legitimate errors. Thanks for the reply though, increasing the MaximumErrorCount is much too heavy handed for my tastes.

  4. john says:

    Step by step instructions please. This did not work for me.

    • John,

      I looked and I’m not sure what else I can add as far as step by step for this particular part of the process. I did add links to parts 2 and 3 and hopefully they will help you out. Also part 3 has a copy of the DTSX that I used for download. Be aware that it’s got a DOCX extension on the end of it since I couldn’t figure out how to upload a DTSX. Just download it and save it without the extra DOCX and you should be able to open it up and use it as an example. If that still doesn’t work reply with some specific issues that you are having and I’ll see what I can do.

  5. […] In case anyone is interested we took an SSIS package that loops through a list of instances and used it to collect some information. Here is an example of one of the queries we […]

  6. […] do with the information? Geez, what can’t you do! I’ve used our instance table to write SSIS packages that loop through a list of instances, populate a CMS (Central Management Server) and generate an XML file for Idera’s Toolset to […]

  7. pl80 says:

    This is great! I’ve used it to loop through databases on single server. I’ve used Foreach ADO Enumerator connected to an object variable and OLE DB Source and sql query to populate the Recordset Destionation mapped to that variable. This way the list of databases is dynamic. Works similarly. Great post. Tx.

  8. data enthu says:

    I need help in creating a package that iterates through databases in a given server, looks for a table name that contains the word ‘EventLog’ and delete all rows older than a year. The deletion should be in batch of 25000.

    • You don’t really need a package for that. Just create a cursor to loop through the users databases (sys.databases) then create another (inside the initial loop) that does a like on sys.tables. Inside that loop you have a loop that does a delete top 25000 and checks @@rowcount to see when you fall below deleting 25000 rows. That last part will have to be done using dynamic SQL. So in pseudo code something like this:

      CREATE CURSOR Cursor1 on sys.databases WHERE databaseId = 25000
      Another delete

  9. Shirley says:

    Alos, I”m trying to loop through servers and it seems as the the expression does not accept FQDN. Any recommendations?

Leave a Reply to Kenneth Fisher Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: