Looping through multiple servers in SSIS part 2

Leave a comment

November 9, 2012 by Kenneth Fisher

Last week I posted a quick example of looping through multiple servers using SSIS and being in a bit of a hurry I didn’t really finish. Last time I created the loop and got it working, but didn’t do anything with it. So this time I’m going to demonstrate using the loop to pull a list of databases and some information about each from the three different servers I set up last time into a central location.

  1. Starting from the end of the previous post.
  2. Put the name of a valid instance into the ServerName variable. If you don’t do this then when you try to use the ConnMan1 connection manager to set up the source in the data flow you’ll get errors. The expression on the connection manager will blank out the server name when it tries to make a connection if the variable remains blank.
  3. Add a new OLE DB connection manager pointing to the instance and database where the data will be stored. I’ve called mine “DataStore”.
  4. Add a table on the “DataStore” instance.
    1. I used the following query to create the table:
      SELECT TOP 0 @@SERVERNAME AS Server_Name, sys.databases.database_id,
      	sys.databases.name, sys.server_principals.name AS database_owner,
      	sys.databases.compatibility_level, sys.databases.collation_name,
      	sys.databases.recovery_model_desc, sys.databases.page_verify_option_desc
      	INTO Database_List
      FROM sys.databases
      JOIN sys.server_principals
      	ON sys.databases.owner_sid = sys.server_principals.sid
    2. For those database purists out there here is a primary key:
      CREATE UNIQUE CLUSTERED INDEX pk_Database_List ON Database_List(Server_Name, database_id)
  5. Add an Execute SQL task to initialize the table.
    1. Under the General tab change the Connection property to “DataStore”
    2. Put the following in the SQL Statement property:
      TRUNCATE TABLE Database_List

  6. Connect the task to the loop.
  7. Add a dataflow task inside of the loop.
  8. Add an OLE DB source and an OLE DB destination to the dataflow and connect them.
  9. Configure the OLE DB source.
    1. Set the OLE DB connection manager to ConnMan1
    2. Set the Data access mode to SQL command
    3. Set the SQL command text to the following query
    SELECT @@SERVERNAME AS Server_Name, sys.databases.database_id,
    	sys.databases.name, sys.server_principals.name AS database_owner,
    	sys.databases.compatibility_level, sys.databases.collation_name,
    	sys.databases.recovery_model_desc, sys.databases.page_verify_option_desc
    	INTO Database_List
    FROM sys.databases
    JOIN sys.server_principals
    	ON sys.databases.owner_sid = sys.server_principals.sid

  10. Configure the OLE DB destination.
    1. Connection Manager tab
      1. Set the OLE DB connection manager to DataStore
      2. Leave the Table or view – fast load
      3. Set the name of the table or the view to Database_List.

    2. Click on Mapping tab to initialize the mappings

At this point you should be able to run the package and pull your database list for the servers set up in the loop. Next week I’ll demonstrate pulling the list of servers from a table.

About these ads

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

%d bloggers like this: