Looping through multiple servers in SSIS

9

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.

EDIT:

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

About these ads

9 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.

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 305 other followers

%d bloggers like this: