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.