SSIS: Dynamically Configuring the Data Connection Manager

In the last post I described using the WMI Data Reader Task. The data collected is by nature dynamic. Servers are stood up and decommissioned, as are databases. In this post I’ll demonstrate retrieving data from a changing list of servers.

Create Variables

To start, create a variable with a type of Object. Make sure the scope is package wide. The object is going to be created in one Data Flow and then used later in a For Each Loop container. Variables are by default created in the current scope. This caused me several headaches when I first started doing SSIS. A nice change in SQL Denali is the scope of variables will be package in scope by default.

Next create a variable of type string to hold the name of the server. Again, be sure the variable scope is package wide.

Server Variables

Populate the Server List Variable

Create a Data Flow and add a Data Source that retrieves your list of services. For the destination add a Recordset Destination set the variable to the server list variable.

Server List

Create Data Connection Manager

Create a WMI Data Connection Manager. In the last post I described using the WMI Data Reader Task. Now I am going to place this task inside a For Each Loop container and loop through a list of servers.

Connection Manager Settings

In the Data Connection Manager properties, click the Properties Expressions Editor. Select the Connection String and Server Name properties. Now add expressions to build the strings for these properties:

Connection String: “ServerName=\\” + @[User::ServerNameWmi]  + “;Namespace=\root\cimv2;UseNtAuth=True;UserName=;”

Server Name: “\\” +  @[User::ServerNameWmi]

Property Connection Manager

Now add a For Each Loop Container to a Control Flow. Set the Enumerator property to Foreach ADO Enumerator. Set the ADO Object Source Variable to the object variable created earlier. In the variable mappings set the server name variable.

For Each Loop Container

And now one can gather information on however many servers returned in the initial query.


This entry was posted in Business Intelligence, Data Integration, Database BI, SSIS, Uncategorized and tagged , , . Bookmark the permalink.

Comments are closed.