Looping through multiple servers in SSIS Part 3

14

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

  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
    CONSTRAINT pk_InstanceList PRIMARY KEY,
    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.

Here is a copy of the .dtsx.  Unfortunately I couldn’t figure out how to post it as is so I added a .docx extension on the end of it (Even though you don’t see it).  Just remove that it and it will open correctly.

Package1.dtsx

About these ads

14 thoughts on “Looping through multiple servers in SSIS Part 3

  1. jigar says:

    Hi, thankyou for this detail.. i was looking for something..do you have the entire code?

  2. Kenny says:

    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.

  3. Ighi says:

    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?

  4. Srikanth says:

    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.

      • srikanth tatipaka says:

        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.

  5. Rolson says:

    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.

      • Rolson says:

        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.

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 527 other followers

%d bloggers like this: