How To – Tasks and Transformations: WMI Data Reader

After discussing the Send Mail task last time, I’m going to continue the theme of workflow tasks and review WMI Data Reader.

Background:

WMI stands for Windows Management Instructions, and is an infrastructure that lets you manage data and operations on Windows systems. More simply put, WMI provides an interface for interacting with the Windows OS and hardware components. This interface can be used from C/C++, VBA, .NET, PowerShell, and SSIS.

The WMI Data Reader task enables you to use WMI in your package workflow. A common scenario for the WMI Data Reader task is to query a machine for information.

Common examples of the type of information you might retrieve with WMI include:

Data from the system’s event logs Obtain the status and properties of hardware components The applications, and their versions, installed on the system Realize that WMI can go much deeper than the above examples. For instance, you can learn the capacity, type (e.g., SRAM, SDRAM, DDR, DDR-2), and operating status of physical memory in a machine. Or you can retrieve performance data about physical disks, such as how much data the disk is reading and writing per second.

Getting at this data requires using the WMI Query Language (WQL). Microsoft describes WQL as a dialect of SQL and it does look similar to T-SQL. As you’ll see in our example, a basic WQL statement can have a familiar pattern of:

SELECT FROM WHERE

With that in mind, let’s check out how to add a WMI Data Reader in BIDS and Mist.

Using BIDS:

Start by finding the WMI Data Reader task in the Toolbox.

When you drag and drop the task onto the package’s design surface, you’ll see the red X icon, indicating that there are errors.

Opening the error list, you’ll see the sole error is:

To solve that problem, double click on the WMI Data Reader task in the designer, to bring up its properties dialog.

Since BIDS is unhappy with the lack of a query, you can start by adding one. The query’s content can come from different sources; the WqlQuerySourceType property lets you select the query’s source. Its dropdown allows you to choose from:

Type Description Direct Input Enter text directly into a property File Connection Specifies a file that contains the content. The file may already exist or may be created at runtime. You might choose to store your queries in a file that you can change outside of BIDS. Variable Create or choose a variable that contains the content. Use Direct Input and enter a query in the WqlQuerySource field. The example query below returns the free space, capacity, device ID, and description of my computer’s D drive. Naturally, you’ll want to change the drive letter to reflect the machine you’re running this query on.

Now, based on the error list in BIDS, it would appear this is sufficient to make the task ready for use. However, if you were to press OK in the Task Editor dialog and open the error list again, you’d see:

This quirk, of identifying the lack of a connection only after solving the previous error, can be annoying. Later on, I’ll show you how Mist improves on this.

In the meantime, the next step is to create a WMI connection, so the task knows where to run the query. To do that, you can use the WmiConnection property, which lets you set the target system to run the WQL query on, as well as authentication for the connection. If you click inside the WmiConnection field, you’ll see an ellipsis button. Click on the button to open a dropdown.

Inside the dropdown, click on ‘New WMI connection…’ to open the Connection Manager Editor dialog.

This dialog creates a new connection manager for the WMI data reader; the important fields are Server name and Namespace.

Server name identifies the machine to run this query on. Currently, it’s set to localhost. Since I’ll be running this task on my local machine, I’ve checked the Use Windows Authentication checkbox. If I don’t check it and try to run the task in BIDS, I’ll get an error that ‘User credentials cannot be used for local connections.’ Pressing the dialog’s Test button would also indicate the problem.

Namespace is a key field for WMI. The brief explanation is that different namespaces provide different types of functionality, known as classes. The rootcimv2 namespace (cimv2 is an abbreviation for Common Information Model v2) allows you to access most physical and logical components in a system. You can browse additional namespaces and their classes using tools such as WMI CIM Studio and Scriptomatic 2.0.

Once you’ve set up your server name and namespace, press OK in the dialog.

The next step is to choose the format of the returned WMI Data. This is specified in the Output Type property and you can choose from Data Table, Property name and value, and Property value. You can keep the default Data Table. Further down, I’ll show you how the output looks when you use the different types.

Now that you’ve entered the query, the machine to run the query on, and the format of the query results, you need to specify where the data will go.

The Destination Type property specifies if the destination is a variable or a file. For this example, keep the default and save the data to a file.

To specify the file, click inside the Destination property’s text field and click on its ellipses button. Just like the WmiConnection property, click on New Connection to open the File Connection Manager Editor.

Change the Usage type to Create file, and specify where you want the file created.

Our final step is to set the OverwriteDestination property. This property controls what happens if your destination file already has data when you run a query. You can choose to:

Type Description Keep original Keeps the destination file’s data untouched Append to destination Appends your new data to the file Overwrite destination Overwrite the file with your new data Use the Keep original default.

With those steps done, the WMI Options are set as follows:

You can now press OK to close the dialog and return to the designer. The task’s red X icon is gone since it now has all the information it needs.

If you run this task in BIDS, by pressing F5, you should see it turn green. Furthermore, if you navigate to C:WMI Data, you should see a data.csv file. If you open it in notepad, you’ll see something like:

Note that if you try to run this task again, you’ll get an error since C:WMI Datadata.csv already exists. To prevent that, you’ll need to change the OverwriteDestination value to append or overwrite.

If you re-run this task with the ‘Property name and value’ output format, the generated file contains:

If you select just Property Value, the contents are:

Using Mist:

Now let’s see how to do the above in Mist.

To start, you’ll need to create a package. If you’re unfamiliar with how to create a package, you can follow the first couple steps in the Mist User Guide’s Creating a Package topic.

Once your package is open, navigate to the Toolbox tool window.

Select and drag the WMI Data Reader task onto the Package’s design surface.

This task has a red X icon, just like in BIDS, since it has errors.

However, you’ll notice that unlike BIDS, we list all errors upfront, along with recommendations for fixing them.

Inside the Package Details tool window, you’ll find the same properties as the WMI Data Reader dialog in BIDS.

Our first step is to enter the WQL query. In the Source area, there is the Method dropdown, which lets you set the task’s WqlQuerySourceType property. It’s already set to the default value of Direct. Beneath the drop down is a text area, where you can enter the query. Afterwards, update the Overwrite Destination property to Overwrite.

The next step is to set the two connections you’ll need.

If you click on the WMI Connection dropdown, it only contains a value for (No Selection). Thus, you’ll need to add a WMI Connection to the project. You can follow the steps in the Mist User Guide’s Create New Connection section to create a connection, although you’ll want to make a WMI connection rather than an Ole DB connection.

The designer for the WMI connection has two red bordered fields, clearly indicating what you need to enter.

From the BIDS sample, provide an appropriate namespace and server name, and set Use Windows Authentication to true.

Next, create a File connection and open its designer.

Matching the BIDS sample, switch the File Usage type to Create File. Clearly, you also need to enter a File Path. You can also rename this connection in case you want to use it elsewhere.

Now, reopen the package and select the WMI Data Reader task to view its details again. In the WMI Connection combo box, you can now select WmiConnection1. In the Destination’s File Connection combo box, you can select WMIDataCsvConnection.

And that’s it. Your task is done and ready to run. To test this package, let’s run it using DTEXEC. To do that, right click on the package in the logical view.

Select the Build & Run menu item. This menu item builds the Mist package, which produces a DTSX package that DTEXEC will execute, just like in BIDS.

Next Time:

In my next post, I’ll look at a sample using the WMI Data Reader task and a related task.

Links:

WMI Data Reader documentation for the BIML language

WMI Data Reader task – MSDN

WQL Keywords

WQL Operators

WMI Win32 classes

Craig