Looping through multiple servers in SSIS Part 3
29November 15, 2012 by Kenneth Fisher
Over the last couple of weeks I’ve set up a SSIS package that will loop through a group of servers and export the list of databases to a central location. I used a Foreach Item Enumerator to list the servers but honestly those require a bit too much maintenance for me. In my office we have 60-70 instances right now and are merging with a group with 60+ more. In my mind that’s a headache waiting to happen. So today I’m going to modify the package to pull and loop through a list of servers from a table. Still somewhat manual (entering records into the table) but at least I don’t have to open the package each time.
- Starting from Step 2.
- First let’s do something I should have done from the beginning. Rename all of the objects to something meaningful.
- Create a table in the DataStore instance to hold the list of instances.
CREATE TABLE InstanceList ( InstanceName varchar(255) NOT NULL CONSTRAINT pk_InstanceList PRIMARY KEY, GetDatabaseList bit NOT NULL, Active bit NOT NULL )
- Add rows to your table pointing to the servers you are interested in. Make sure that GetDatabaseList and Active are both set to 1.
- Create a new variable in the package called “ServerList” of type Object
- Add a new Execute SQL task to pull the list of instances from our new table.
- Under the General tab
- Set the name to “Get Instance List”
- Change the ResultSet to “Full result set”
- Change the Connection property to “DataStore”
- Put the following in the SQL Statement property:
SELECT InstanceName FROM InstanceList WHERE Active = 1 AND GetDatabaseList = 1
- On the Result Set
- tab Click the Add button
- Change the Result Name to 0
- Change the Variable Name to User::ServerList.
- Attach the task to the previous one.
- Under the General tab
- In the ForEach loop properties under the Collection tab
- Change the Enumerator to a “ForEach ADO Enumerator”
- Change the variable to “User::ServerList”
- Leave Enumeration Mode “Rows in the first table”
At this point we now have a package that will loop through a control table containing a list of instances. Then for each instance it pulls a list of databases and stores it into a central location. Hopefully I’ve managed to make these posts clear enough that everyone can follow and easily duplicate what I’ve done. If not then please feel free to comment and I’ll do my best to help you get up and running.
Note: Unfortunately the package file got corrupted somehow and I had to get rid of it. I’ll try to re-create it (and may even re-do this series) and get it reposted one of these days. Of course this was using the SQL 2005 version of SSIS which is no longer really up to date.
SQL Studies 
Hi, thankyou for this detail.. i was looking for something..do you have the entire code?
It may take me a couple of days to get to it, but I’ll check if I still have the .dtsx available to post.
It is nice one. with the same pakage, how to add one more task and collect any error output server names. For example i have numbe of servers with different domain and connection is timing out. Right now i am ignoring those errors. but i need to caputre those errors so i can troubleshoot them.
Not sure about different domains, I’ve never had to deal with something like that. As far as the errors go you can either implement logging: https://msdn.microsoft.com/en-us/library/ms140246.aspx?f=255&MSPPError=-2147217396
Or you can put something in the on-error event. https://www.simple-talk.com/sql/ssis/ssis-event-handlers-basics/
Hi Kenneth
do you perhapsps have the .dtsx available to post ?
Ok, I’ve added it to the post. Unfortunately I couldn’t figure out how to add it with the dtsx extension so it will have to be added and the docx extension removed. After that it should open correctly.
Thanks for the detail, it works perfectly, but I cant get the package to run from a SQL agent job, as the package throws up a non fatal error due to the source variable being a system:object. do you have any tips?
Can you post the specific error?
Thank you Kenneth, I have found my mistake now
When I try to setup OLEDB Source I don’t see any source tables from the Dynamic Connection
Check the variable that you are using to hold the instance name. Does it have the name of a valid instance? If not then the Dynamic Connection will not be able to find anything to connect to.
Hi Kenneth
Thanks for reply. Sorry I was caught up with something. So here is the scenario of the package.
1. My first part of the package creates staging tables and stores data in the respective instances(looping works fine) 2. Step2: Now we need to get that data from staging tables into my repository. I get error when I configure OLEDB Source(Dynamic connection). It wont show any source tables. I tired changing my data access mode to SQL Command and wrote the query but still wont work. Error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections
Why don’t we move this to email. You can email me at sqlstudent144@gmail.com.
How to handle the failed connection within the loop container? If I want to log the failed instance name to a SQL table then continue to finish the rest of instances. Thanks!!
You need to set the MaximumErrorCount higher than 1 and set up an Event Handler on the OnError event.
Thanks for your tip!! I am new to SSIS, so I don’t know how to do that. The big problem is that I don’t know how to use the User::ServerList variable in event handler.
Hey Kenneth Fisher
Really really great article . it almost solved my issue. The only difference I am having in terms of requirement is :- I have to select specific required tables from different server and then store there data in a central central server inside multiple table . Here is my complete requirement :-
Please help as I am running neck to neck with my deal line :-
I have to import multiple table from different servers and once I import it should load to my target table but first my package should create a staging table for all those target tables and then load into target table from staging. Now when I run the same package next time my staging table should automatically drop and recreate. The idea hear is that if there is a change in my source which leads to the change in my destination table ,so my staging table should also change only according to the target/destination table.
please help me to create dynamic connection between different servers and then dynamically fetch data from different tables available on different servers then create a staging table based on my destination table and then finally push data into my destination
Please note that different server may have more nos of table but I am only interested in some.
Say for example Server A is having 3 tables a1, a2 and a3 and server B is having b1,b2,b3,b4,b5 but I am only interested in fetching a2,a3 from server A and b2,b5,b4 from server B.
Please advice . Any kind of help will be greatly appreciated .
Thanks,
Rajeev Kumar
Try setting up your “instance” table as an “instance_table” table. IE
InstanceA TableA
InstanceA TableB
InstanceB Table C
InstanceC TableA
etc.
Use a distinct when you pull the list of instances. Then create a second loop inside of the instance loop that pulls a list of tables needed based on the current instance. Then create an expression for the InitialCatalog property of the connection manager. I’m not sure how you would create the new tables but possible a SQL Script task and some dynamic SQL?
Hope that helps
I have an example package on my blog illustrating these ideas in a ready-to-go free utility to help DBA’s monitor their servers: https://harmontodd.wordpress.com/2015/09/12/mmoap-lives/
I cant download the package
Neither can I. I’ll try to get it fixed. I may have to re-create the package & try to upload it in a different way. I’m not exactly sure what’s going on there.
i’m trying to get this to work. Was the docx removed? I am not seeing it. Are both variables staying the same with expressions, etc? It’s confusing starting from par 1 as I dont know that any of that is valid anymore?? Please advise.
It’s really more appropriate to v2005. I’ll see if I can’t re-do it in the next few weeks with a more updated version of SSIS. And unfortunately somehow the package was corrupted somehow and I don’t have it anymore.
Apologise if this is obvious, where do i find the download document, i cant see a link to it on the page.
It’s a good question. Somehow the file got corrupted and I had to ditch it. One of these days I really need to re-create it. In the mean time I’ll put a note at the end of the post. Sorry about that.
Righto, Thanks anyway. You’ve done exactly when I need to do, I was just going to be lazy 🙂
😀 You aren’t the first person to ask so I really should re-create the silly thing.
how to make oledb source connection manager dynamic, the loop is not running but for the same server only.
loop is running but for same server only.please help to get it fix.