Looping through SQL Servers using SSIS or Dynamically build connection to multiple SQL Servers

Consider you are giver a task of gathering information from multiple servers. What are the different ways you can automate this?

1. Create linked servers in each server pointing to your central repository
2. Create Separate SSIS package in each servers which loads data to your central repository
3. Create one SSIS package with multiple data sources and duplicate the tasks for each data source.
4. Create one SSIS package which dynamically builds connection to each server and does the data loading tasks.

If you choose an option between 1 and 3 , then  you will have to do some extra work for gathering the server information when there are new servers added to your environment.

If you choose the 4th option then also you will have some extra work but it is very small and easy when compared to the first 3 options. Here you need to just add the new Server Names to the table and that is all and everything else will be taken care by the SSIS package.

In this post I am going to explain how to create a SSIS package which loops through multiple SQL server and gathers information  by building the connection to those servers dynamically.

1. Open “Microsoft Visual Studio”
2. Create a new “Integration Services Project”
3. Create a SSIS Package
4. Create 2 Connection Managers, 1 for the source (which will be set dynamically for each iteration of the loop)  and the other for destination (which will remain constant, in other terms your Central Repository)
   
   
5. Store All your Server Names in a table preferably in the Central Repository (Destination Connection)
6. Add 2 variables
    a. Name: ConnectionVariable (A name of your choice)
        Scope: Package
        Type: Object
        Value: System.Object
    b. Name: ServerName  (A name of your choice)
        Scope: Package
        Type: String
        Value: A Valid Server Name

7. Add an "Execute SQL Task" with SQL Statement like "select FullName from dba..tbl_ListOfServers".
   
   
8. Set the result set to Full Result Set.
   
   
9. On the Result Set page, Add a Result 
10. Set Result Name "0" and assign it to your Object variable (In our case it is “ConnectionVariable”).
    
11. Now add a “Foreach Loop Container” 
12. Connect it from the “Execute SQL Task”
13. Inside the “Foreach Loop Container” , add the required “Data Flow Task”
    
14. Now Right-Click on the “Foreach Loop Container” and click on “Edit”
15. Now Go to “Collection” page and Set “Enumerator” to “Foreach ADO Enumerator” and “Enumerator Configuration ” to the Object Variable(In Our Case “ConnectionVariable”) and Set “Enumeration Mode” to “Rows in the First Table”
    
16. Now go to the “Variable Mapping” page and Choose the “String Variable” (In our case it is “ServerName”) and set the Index=0
    
17. Now, in the “Data Flow Task” add a “OLE DB Source” (Dynamic Connection) and connect it down to a “OLE DB Destination” (which will be your Central Repository).
    
18. Now Select the Source Connection Manager (In Our Case it is “Source”) and Right-Click on this Source and choose “Properties”.
19. Expand the “Expressions” Option and click on the browse button (…)
20. Now in the “Property Expression Editor”, Choose the property “ServerName” and click on browse button (…). Now choose the String Variable (in our case “ServerName”) and Drag and Drop this variable into the “Expression” box and click “OK” and “OK”.

Now when the package runs, the “Execute SQL Task” will read the list of servers and stored in the table you specified and the “Foreach loop” will iterate over each record in that table, running the “Data Flow Task” each time while each time, the ServerName property of the Source Connection in the data flow will get a new value