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.
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.
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.
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.
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]
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.
And now one can gather information on however many servers returned in the initial query.