Use SSIS to Access WMI Data on Multiple Servers

Information about SQL Server instances isn't the only type of information that DBAs need. They also need information about other software and the hardware installed on servers. SQL Server can give you access to the OS if you enable the xp_cmdshell option, use OLE automation, or use non-SAFE CLR code (i.e., CLR code in which WITH PERMISSION_SET is set to something other than SAFE). However, if you don't want to use these methods or you have servers that aren't hosting a SQL Server instance (e.g., they're running Oracle instead), you need alternative methods to access the OS and get the information you need.

Fortunately, Windows-based systems have a plethora of scripting languages and diverse methods to collect and analyze server data. One of the most exhaustive ways to get information from local and remote systems on a network is using Windows Management Instrumentation (WMI). Through WMI, the OS exposes a lot of information about the hardware and software installed on the machine. This information is accessible through WMI Query Language (WQL) queries.

Using WMI and WQL, I created a simple SQL Server Integration Services (SSIS) solution that collects information about the total amount of space and the amount of available free space on logical drives installed on networked computers. It stores the data in a SQL Server table for further usage. Although this solution collects information about logical drive space only, you can easily adapt it to collect different types of information on an almost unlimited number of computers so that you have a lot of useful information about the environment in which SQL Server operates.

I'll walk you through how to create this SSIS solution on SQL Server 2005. (You can apply the same procedures on SQL Server 2008.) Here are the steps involved:

1. Create the Maintenance Tables

The SSIS solution needs a database that's dedicated for monitoring purposes. So, if you don't already have such a database, you need to create it. For this example, I named the database DBACoolStuff.

In DBACoolStuff, you need two tables:

Listing 1 shows the T-SQL code you can use to create these tables. You can download this code as well as the SSIS solution by clicking the 102563.zip hotlink at the top of the page.

2. Create a New Project and Package

The next step is to create a new project. Open SQL Server Business Intelligence Development Studio (BIDS) and create a new Integration Services Project. Name it WMIDataCollection, and select the Create Directory for Solution check box. Rename the package from package.dtsx to WMICollectDrivesInfo.dtsx.

You need to define two variables for the WMICollectDrivesInfo package: WMIConnStrList and WMIConnStr. Select the Variable option on the SSIS menu. Enter the information that Figure 1 shows for each variable. These variables will be used to control the Foreach Loop container.

Figure 1: Entering the WMICollectDrivesInfo package's variables

3. Create a Temporary Output File

The main element in the WMICollectDrivesInfo package is a WMI Data Reader task. These tasks use WQL queries to obtain WMI information from machines. You can store the query results in a file or a variable. I'll describe how to use a file here. For information on how to use a variable, see the sidebar "Using a Variable to Store the WMI Data Reader Task's Output."

In the WMICollectDrivesInfo package, a file named E:all_drives_per_server.txt will contain the WMI Data Reader task's output. However, this file is just for temporary storage. The dbo.SQL_Drives table is where the WMI data will be permanently stored.

The E:all_drives_per_server.txt file must exist before you proceed to the next step (otherwise, you'll encounter errors), so use Notepad or another text editor to create it. In the file, add the header line

FreeSpace,Name,Size,SystemName

This needs to be a comma-separated list, so don't include any spaces in this line.

4. Create the Connection Managers

The WMI Data Reader task needs four connection managers to work. In SSIS Designer, click the Control Flow tab to make the Connection Managers area available. Create the following connection managers:

WMI Connection Manager. Right-click in the Connection Managers area and select New Connection. In the Connection Manager Type list, select WMI – Connection manager for the WMI tasks, then click OK. In the WMI Connection Manager Editor that appears, enter MyWMIConn in the Name field, a short description in the Description field (if desired), and rootcimv2 in the NameSpace field. Leave the Server Name field blank. Select the Use Windows Authentication check box and click OK.

After the MyWMIConn connection manager is created, right-click it and select Properties. Under Expressions, create a property named ConnectionString and give it the expression @[User::WMIConnStr], as Figure 2 shows.

Figure 2: Entering the ConnectionString property's expression

This expression ensures that the connection string for the MyWMIConn connection manager is updated from the WMIConnStr variable in each iteration of the Foreach Loop. This way, with each execution of the loop, one server will be contacted and the data collected from it.

OLE DB Connection Manager. Right-click in the Connection Managers area and select New OLE DB Connection Manager. In the OLE DB Connection Manager Editor, enter DBACoolStuff in the Name field and click OK. The DBACoolStuff connection manager will be used to connect to the database that hosts the two tables created in Step 1.

File Connection Manager. Right-click in the Connection Managers area and select New File Connection Manager. In the File Connection Manager Editor, select Create file in the Usage type drop-down list. In the File field, enter E:all_drives_per_server.txt. Click OK.

Flat File Connection Manager. Right-click in the Connection Managers area and select New Flat File Connection Manager. In the Flat File Connection Manager Editor, enter the settings shown in Figure 3.

Figure 3: Creating the Flat File Connection Manager

As you can see, this connection manager also points to E:all_drives_per_server.txt. Click OK.

If the OK button is grayed out and you receive a message that states Columns are not defined for this connection manager, click Columns in the left pane. This will force SSIS to read the content of the E:all_drives_per_server.txt file and activate the OK button. This file also helps identify the columns in step 9.

5. Build the WMI Connection Strings List

You're now ready to build the WMI connection strings list for the WMI Data Reader task. On the Control Flow tab in SSIS Designer, drag the Execute SQL task icon from the Toolbox's Maintenance Plan Tasks section to the Control Flow design surface, then double-click the task to edit it. On the General page of the Execute SQL Task Editor, configure the fields as follows:

Leave the rest of the fields unchanged (i.e., at their default values).

On the Result Set page of the Execute SQL Task Editor, enter 0 in the Result Name field. Under Variable Name, select User::WMIConnStrList.

6. Add a Foreach Loop Container

After you've built the WMI connection strings list, you need to add a Foreach Loop container by dragging the Foreach Loop container icon from the Toolbox's Control Flow Items section to the Control Flow design surface. Double-click it to bring up the Foreach Loop Container Editor. Configure the Collection page as follows:

Next, go to the Variable Mappings page. Select User::WMIConnStr from the Column variable drop-down list, then type 0 under Index.

Now you need to draw a precedence constraint from the Populate WMI Connection Strings list task to the Foreach Loop container. To do so, click the Populate WMI Connection Strings list task. A green arrow will show under it. Click the end of the arrow. With the mouse button still held down, drag it toward the Foreach Loop container until it appears glued to the container, then let go. The green arrow will now connect the two items. The green color and the arrow direction ensures that Foreach Loop container executes only after the preceding task is successfully completed.

7. Add the WMI Data Reader Task

The next step is to add a WMI Data Reader task to the Foreach Loop container. Drag the WMI Data Reader task icon from the Toolbox's Control Flow Items section to the Foreach Loop container. Right-click the task and select Edit to bring up the WMI Data Reader Task Editor. On the WMI Options page, configure the fields as follows:

8. Add a Data Flow Task

You now have to process the contents of the temporary output file E:all_drives_per_server.txt. Add a Data Flow task by dragging the Data Flow task icon from the Toolbox's Control Flow Items section to the Control Flow design surface. Draw a precedence constraint from the Data Flow task to the WMI Data Reader task.

Switch to the Data Flow tab. Drag the Flat File Source icon from the Toolbox's Data Flow Sources section to the Data Flow design surface. Right-click it and select Edit to bring up the Flat File Source Editor. In the Flat File Connection Manager drop-down list, select WMI output.

9. Add a Derived Column Transformation

The WMI Data Reader task doesn't provide the WMI data in the format needed for the dbo.SQL_Drives table, so you need to add a Derived Column transformation to format the data. You'll also use it to add a new column that will store the date and time the WMI data was collected.

To add a Derived Column transformation, drag the Derived Column transformation icon from the Toolbox's Data Flow Transformations section to the design surface of the Data Flow tab. Draw a precedence constraint from the Flat File Source to the Derived Column. Right-click the Derived Column and select Edit to open the Derived Column Transformation Editor. Enter the settings shown in Figure 4.

Figure 4: Entering the data transformation settings

10. Add an OLE DB Command Transformation

The last step is to add an OLE DB Command transformation that will insert the WMI data in the dbo.SQL_Drives table. Drag the OLE DB Command transformation icon from the Toolbox's Data Flow Transformations section to the design surface of the Data Flow tab. Right-click the OLE DB Command and select Edit to open the OLE DB Command Transformation Editor. On the Connection Managers page, enter DBACoolStuff in the OleDBConnection field. In the SQL Command field on the Component Properties page, enter the command: INSERT INTO dbo.SQL_Drives (Drive_Letter, [Server], MBFree, TotalSize, DayRun) values (?,?,?,?,?). On the Column Mapping page, configure the fields as follows:

Save the WMICollectDrivesInfo package by choosing Save Selected Items on the File menu. Figure 5 shows what the Data Flow component should look like.

Figure 5: The Data Flow task in WMICollectDrivesInfo

The entire package should look like the one in Figure 6.

Figure 6: The entire WMICollectDrivesInfo package

The Results

You need to run the WMICollectDrivesInfo package under an account that has permission to create files. Let's say the dbo.ServerList_SSIS table contains the server list shown in Table 1.

If these were real servers in a network, you might get data similar to that in Table 2.

As I mentioned previously, you can easily adapt the WMICollectDrivesInfo package to collect more than just logical drive space statistics. MSDN has a wealth of information about the types of data you can collect and how to do so. MSDN's coverage of WMI starts at msdn.microsoft.com/en-us/library/aa394582.aspx. For information about writing WQL queries, go to MSDN's "WQL (SQL for WMI)" web page.