Looping through multiple servers in SSIS
16October 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.
- First create an OLE DB connection manager pointing to some random server.
- 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.- Under the Collection “tab” change the Enumerator to “Foreach Item Enumerator”.
- Add a single column.
- In that column list out the servers you want to loop through.
- 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.
- Next go to the properties of the connection manager. Look for one called Expressions. Select it and click on the ellipsis (…).
- Under properties select ServerName.
- Then Click on the ellipsis (…) under Expression.
- 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
SQL Studies 
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.
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.
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.
Actually I wouldn’t mind seeing the script if didn’t mind. What I do currently is use the on error event to log any errors that occur and fail if it’s something that I can’t continue past. It’s more generic since my errors arn’t always a failure to connect. That and only upping the MaximumErrorCount where appropriate of course.
Credit goes to the guy who replyed to my question. This also gives a much better description of the issue I was trying to tackle
http://stackoverflow.com/questions/15302274/ssis-looping-through-servers-and-handling-failed-logins
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.
[…] 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 […]
[…] 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 […]
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.
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
Alos, I”m trying to loop through servers and it seems as the the expression does not accept FQDN. Any recommendations?
[…] Using a fixed list and a for each loop that changes a connection manager with expressions. […]