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

Looping through multiple servers in SSIS Part 3

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.

  1. Starting from Step 2.
  2. First let’s do something I should have done from the beginning. Rename all of the objects to something meaningful.
  3. Create a table in the DataStore instance to hold the list of instances.
    CREATE TABLE InstanceList (
    InstanceName varchar(255) NOT NULL
    GetDatabaseList bit NOT NULL,
    Active bit NOT NULL
  4. Add rows to your table pointing to the servers you are interested in. Make sure that GetDatabaseList and Active are both set to 1.
  5. Create a new variable in the package called “ServerList” of type Object
  6. Add a new Execute SQL task to pull the list of instances from our new table.
    1. Under the General tab
      1. Set the name to “Get Instance List”
      2. Change the ResultSet to “Full result set”
      3. Change the Connection property to “DataStore”
      4. Put the following in the SQL Statement property:
        SELECT InstanceName
        FROM InstanceList
        WHERE Active = 1
        AND GetDatabaseList = 1

    2. On the Result Set
      1. tab Click the Add button
      2. Change the Result Name to 0
      3. Change the Variable Name to User::ServerList.

    3. Attach the task to the previous one.
  7. In the ForEach loop properties under the Collection tab
    1. Change the Enumerator to a “ForEach ADO Enumerator”
    2. Change the variable to “User::ServerList”
    3. 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.


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