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.
2. Create a new project and package.
3. Create a temporary output file.
4. Create the connection managers.
5. Build the WMI Connection Strings list.
6. Add a Foreach Loop container.
7. Add the WMI Data Reader task.
8. Add a Data Flow task.
9. Add a Derived Column transformation.
10. Add an OLE DB Command transformation.

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:

• dbo.ServerList_SSIS. This table stores the list of servers that are going to be analyzed.
• dbo.SQL_Drives. This table stores the information that the SSIS solution collects.

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.

Listing 1: Code to Create the Maintenance Tables

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 (click to enlarge)