WMI Data Reader and Event Watcher Task

Windows Management Instrumentation (WMI) is one of the best-kept secrets in Windows. WMI allows you to manage Windows servers and workstations through a scripting interface. The WMI Data Reader task allows you to interface with this environment by writing WQL queries (the query language for WMI) against the server or workstation (to look at the Application Event Log, for example). The output of this query can be written to a file or variable for later consumption. The following are some applications for which you could use the WMI Data Reader task:

• Read the event log looking for a given error.

• Query the list of applications that are running.

• Query to see how much RAM is available at package execution for debugging.

• Determine the amount of free space on a hard drive.

You can configure this task in the WMI Data Reader Task Editor in the WMI Options tab (Figure 3-22). First, select the WMI Connection Manager under the WMIConnection drop-down box. The WQLQuerySourceType option selects whether your query will be directly inputted, retrieved from a variable, or retrieved from a file. The WQLQuerySource is where you select the query that you wish to run against the connection. This may be a variable name, a text file name, or the query itself.

Figure 3-22

The OutputType option is where you specify whether you want the output of the query to retrieve just the values from the query or also the column names with the values. The OverwriteDestination option sets whether you wish the destination to be overwritten each time it is run or whether you want it to just append to the connection. Lastly, configure the destination as you have in every other task.

WQL queries look much like any SQL query. For example, the following query selects the free space, name, and a few other metrics about the C: drive:

 SELECT FreeSpace, DeviceId, Size, SystemName, Description FROM Win32_LogicalDisk WHERE DeviceID = 'C:' 

The output of this type of query would look like this in a table:

 Description, Local Fixed Disk DeviceID, C: FreeSpace, 32110985216 Size, 60003381248 SystemName, BKNIGHT 

This example of a WQL query selects information written to the Application Event Log after a certain date and about the SQL Server and SSIS services:

 SELECT * FROM Win32_NTLogEvent WHERE LogFile = 'Application' AND (SourceName='SQLISService' OR SourceName='SQLISPackage') AND TimeGenerated > '20050117' 

The results would look like this:

 0 BKNIGHT 12289 1073819649 3 System.String[] Application 3738 SQLISPackage 20050430174924.000000-240 20050430174924.000000-240 information BKNIGHT\Brian Knight 0 

The WMI Event Watcher Task empowers SSIS to wait for and respond to certain WMI events that occur in the operating system. The task operates in much the same way as the WMI Data Reader task operates. The following are some of the useful things you can do with this task:

• Watch a directory for a certain file to be written

• Wait for a given service to start

• Wait for the memory of a server to reach a certain level before executing the rest of the package or before transferring files to the server

• Watch for the CPU to be free

The last CPU example would look like the following query, which detects whether the CPU is less than 50% utilized:

 SELECT * from __InstanceModificationEvent WITHIN 2 WHERE TargetInstance ISA 'Win32_Processor' and TargetInstance.LoadPercentage < 50 

You configure this task in much the same way as the WMI Data Reader task. Open the task editor (shown in Figure 3-23) and go to the WMI Options page. The AfterEvent option shows whether the task will succeed, fail, or keep querying if the condition is met. You can also configure what will happen if a time-out occurs under the ActionAtTimeout and AfterTimeout settings. The NumberOfEvents option configures the number of events to watch for. Lastly, the Timeout option determines how long the task will wait for the event to be met.

Figure 3-23