Windows Management Instrumentation Tasks

SSIS includes two special tasks that allow you to query system information and monitor system events. These tasks are the WMI Data Reader task and the WMI Event Watcher task. WMI uses a specialized query language known as WQL, which is similar to SQL, to obtain information about a Windows system. I am not going to get into all the features and capabilities of WMI in this section, but here are a few possible uses:

• You can get information on files and directories, such as file size, or enumerate the files in a folder. You can also monitor the file system for events, such as whether a file has been modified recently. This could be required in a package if your package is importing data from a CSV or XML file. A change in the file could trigger tasks to fire in your package.

• You can find out if an application is currently running. In addition, you can find out how much memory that application is using or how much processor time it has used. This would be useful if your package needed to know if a companion process was running before creating some sort of output result.

• You can obtain information about users in Active Directory, such as whether a user is active or if they have certain permissions on a resource. This would be useful in a package if information about a user or machine on the network is required for your package's execution.

• You can control services that are running on a computer system and actually start and stop them as required. This would be useful if your package needed to stop a service during a data transfer.

This is just a small sample of the information you can glean from a computer system. You can obtain information not only on the current system but also on remote systems. As you can see, this gives you access to a great deal of information that could be used in the execution of a SSIS package. For example, you could determine if enough disk space existed on a drive before copying a backup file from a remote system to the current system. You could also monitor a file for updates and automatically import the changes into a database table. Later in this chapter you will see how to actually implement these two examples.

WMI Reader Task Explained

The WMI Data Reader task has the following parameters that must be configured properly for the task object to work:

• WmiConnection — A configured WMI Connection Object.

• WqlQuerySourceType — This setting specifies where the WQL query is referenced. The query can be manually typed in or can be stored in a file or a variable.

• WqlQuerySource — This field sets the actual source of the WQL query source selected in the WqlQuerySourceType.

• OutputType — This parameter sets the structure that the results of the WQL query are stored in when executed.

• Overwrite Destination — This parameter determines if the previous results are retained or overwritten when the task is executed.

• Destination Type — This allows you to specify how the results will be stored.

• Destination — This parameter allows you to specify the location of the destination type.

To start configuration of the WMI Data Reader task, you must first create a WMI Connection Manager object. The WMI Connection Manager specifies the WMI namespace that the query will run against. The WMI class used in the query must be contained within that namespace. The standard namespace is the \root\cimv2 namespace. This namespace contains the majority of WMI classes that can be called to get system information. Also, the connection object specifies the target computer system that the query will be run against. By default, the WMI Connection Object points to the localhost machine, but remote systems can be specified as well. As security is always an issue, the WMI Connection Object specifies the user that the query will be run against. Whether it is Windows Authentication or a specified user, the user must have permissions to query the WMI repository on the system for it to work.

Next, the WQL query must be designed. Since WMI is so expansive a subject, I couldn't possibly start to explain the intricacies of the model. I would suggest that you locate a good book on WMI scripting to learn the details of how WMI works. Another resource I suggest you check out are the free tools available through MSDN downloads. There are two applications I would suggest. The first is the Scriptomatic V2 application, which allows you to browse the classes in WMI namespace and generate WMI queries in several different scripting formats. The second is the WMI Administrative tools package. This package includes several apps to enumerate the classes in various namespaces and monitor WMI filter events, among other useful features. These two tools can help you derive WMI queries quickly and easily.

Once you have figured out the structure of your query, you must decide into which object type to store your query results. The WMI Data Reader Task Object gives you basically two choices, a String or a Data Table. Either object can be stored in a user-defined variable or in a file on the file system. When storing the result in a user-defined variable, the variable must be defined as a String data type or Object data type. This means that when obtaining numeric information from the system, you must convert the resultant string to the appropriate data type for use in a mathematical expression. My file transfer example will suggest one way to accomplish this transformation. When storing a Data Table to file, the result is a basic comma-separated file with the properties listed in the first row and the actual values returned in the second row.

WMI Event Watcher Task

As outlined above, not only can WMI obtain information about a computer system, but it can also monitor that system for certain events to occur. This capability could allow you to monitor the file system for a change in a file or monitor the Windows system for the start of an application. The WMI Event Watch task has the following options to configure:

• WmiConnection — This is a configured WMI Connection Manager.

• WqlQuerySourceType — This setting specifies where the WQL query is referenced. The query can be manually typed in or can be stored in a file or a variable.

• WqlQuerySource — This field sets the actual source of the WQL query source selected in the WqlQuerySourceType.

• ActionAtEvent — This option sets the actions that are to occur when the WMI event being monitored occurs. This option has two settings, Log the Event and Fire the SSIS Event or just Log the Event.

• AfterEvent — This field is used to determine what should happen after the WMI event occurs. This setting could be Return with Success, Return with Failure, or Watch for the Event Again.

• ActionAtTimeout — Should the task time-out waiting for the WMI Event to occur, this option details what action should be taken. This could be Log the Time-Out and Fire the SSIS event, or just Log the Time-Out.

• AfterTimeout — Should the task time out, this option sets what should happen after the ActionAtTimeout occurs. This could be Return with Failure, Return with Success, or Watch for the Event Again.

• NumberOfEvents — This option specifies how many events must occur before the specified action is taken.

• Timeout — This sets how long the task should wait, in seconds, before the specified time-out action is taken. A setting of zero (0) denotes that the task will never time-out.

The WMI Event Watcher task is similar to the WMI Data Reader task in that the basic query setup is the same in both cases. You must define a WMI Connection Object and create a WMI query to monitor for an event. The specific options available in this task define how the task will react when the event occurs.

There are two basic types of actions: what should happen when the event actually occurs, and what should happen if the event does not occur within a specified time. Both these actions can either log the event to the package log or, in addition to logging the event, fire an event that can be used to perform additional specified tasks. Also, both actions can dictate what happens after the event occurs or the task times out. These after-events can be to pass to subsequent tasks a success or failure of the WMI Event Watcher task or simply to continue to monitor for the event to occur again.

WMI Data Reader Example

The best way to explain the WMI Data Reader task is to see an example of it in action. The idea of this example is to query the file system for the size of a database file and for the amount of free space on a drive. With this information, you can then determine if the drive has enough space to handle the new file. For simplicity, this example will copy from directories on the same drive. At the end of the example, I will show you how to modify the WMI queries to query the same information from remote systems.

To set up this example, you must first create a file you would like to copy. This example uses a backup of the AdventureWorks database. If you don't know how to create a backup of the AdventureWorks database, you can create your own file or use a file from one of many examples in this book. I would, however, suggest that you do use the AdventureWorks backup, as it will tie into the WMI Event Watcher task example later in this chapter.

First, open a new Integration Services Project and call it WMI Data Copy. Drag a new WMI Data Reader task object from the Toolbox to the Control Flow page of the package. First, give this task a unique name; call it WMI Data Reader Task - Read Free Space on C. Now, right-click on the task and select Edit from the pop-up menu. The WMI Data Reader Task Editor will open, as shown in Figure 16-8.

Figure 16-8

Click in the WmiConnection parameter field and select the button to the right. Select <New WMI Connection…> from the drop-down list. The dialog box shown in Figure 16-9 will be displayed.

Figure 16-9

Give the new WMI connection a name and enter a description. Enter the computer system you wish to query. Leave the server name set to the default of \\LocalHost to query the local computer, and leave the default namespace as \root\cimv2. CimV2 is the main WMI repository that contains the core WMI classes to access information on the system. Finally check the box to use Windows Authentication or enter a user name and password that has rights to query the CIM repository on this computer. Click the test button to verify the settings, and then click OK to close the dialog box.

Back in the WMI Data Reader Task Editor dialog box, leave the WqlQuerySourceType as DirectInput. This means you will manually enter the WQL query into the WqlQuerySource field. Next, select the WqlQuerySource field and click on the ellipsis button on the right-hand side. In the dialog box that appears, enter the following WQL query in the WqlQuerySource window:

 SELECT FreeSpace FROM Win32_LogicalDisk Where DeviceID ='C:' 

This query will return the amount of free space that exists on drive C.

Next, change the OutputType to Property Value and leave the OverwriteDestination field set to Overwrite Destination. Now change the OutputDestination to Variable, and then click in the Destination field and choose the ellipsis button to the right and select <New variable…>. In the Create variable dialog box that appears (shown in Figure 16-10), enter FreeSpaceOnC in the Name field and give the variable a default of zero. Leave the rest of the fields at their default values and click OK to close the dialog box.

Figure 16-10

Now, you'll add another WMI Data Reader task and configure it to return the size of the AdventureWorks backup file. Call this task WMI Data Reader Task - Read DB File Size. Open the WMI Data Reader Task dialog box for this new task. Click in the WMI Connector field and choose the WMI Connection Manger 1 connection. Since the CIM class you will be using to obtain the file size of the backup file is in the same CIM namespace, you can reuse the same WMI Connection Object.

Leave the WqlQuerySourceType as DirectInput. Now, click the SqlQuerySource field and click on the ellipsis to the right to open the query editor dialog box. Enter the following query:

 Select FileSize FROM CIM_Datafile WHERE Name = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\AdventureWorks.bak' 

Now, in the OutputType field, choose Property Value. Then, in the DestinationType, choose Variable and then click in Destination field and choose <New Variable…>. Call the new variable DBBackupFileSize with a data type of string and an initial value set to zero (0).

That's all there is to configuring the tasks themselves, so now you'll add some logic to handle the data the WQL query will return. It was stated previously that the WMI Data Reader can only write to strings and Datatable objects. Well, when a string is returned, it has several extraneous characters at the end that will cause a data conversion from String to Integer to fail. You can see these characters by setting a breakpoint on the PostExecute event of one of the WMI Data Reader tasks and running the package. When the tasks turns green, go to the Variables tab and look at the data in the two user-defined variables.

To massage this data into a usable form suitable for conversion to an Integer data type, you will create a VB.Net Script task to strip the extra characters from the string, leaving just numeric digits in the string. To start, click on the Event Handler tab of the package. In the Executables drop-down box, choose the WMI Data Reader task called WMI Data Reader Task - Read Free Space on C. Now select the OnPostExecute event handler and click the hyperlink in the middle of the page to create the event. Drag a Script Task object from the Toolbox and drag it onto the page. Change the name of the object to FileSizeOnC Data Massage. Right-click on the task and select Edit from the pop-up menu. On the left-hand side of the Script Editor dialog box, choose the Script page. In the ReadWriteVariables property, type in "User::FreeSpaceOnC." This will give you read/write access to the variable from within the VB.Net script. Now, click on the Design Script… button in the bottom-right corner of the window. In the Script Host editor that appears, add the following code immediately after the start of the Main subroutine:

 Dim s As String s = CType(Dts.Variables("User::FreeSpaceOnC").Value, String) Dts.Variables("User::FreeSpaceOnC").Value = Int64.Parse(s).ToString() 

As you can see, this code parses the string to return an Int64 value as a string. In short, it will strip all the extraneous characters from the string and return the result into the same variable. The result is that the contents of the string are ready to be used in a mathematical expression. To finish, close the Script Host windows and hit OK to close the Script Task Editor dialog box. Repeat this same setup for the ReadDBFileSize task, making sure to change the variable references to the appropriate variable names.

You're now in the home stretch of this example. The final steps to complete are to set up the file transfer and add the precedence constraint that will ensure that you have enough space on the drive before you initiate the transfer. First drag a File System task onto the Control Flow page. Name this task Copy Db File. Right-click on the task and click on Edit in the pop-up menu. In the File System Task Editor, set the following properties as shown in Figure 16-11.

Figure 16-11

In the Source and Destination variable fields, create two variables called DBFile_Source and DBFile_Destination as string variables. In the default field of the DBFile_Source variable, enter the full path to the AdventureWorks backup file. In the DBFile_Destination, enter C:\Test\. Click OK to close the dialog box. At this time, you will need to go ahead and create a directory under the root of C called Test. The File System task will not create the directory automatically.

The final step is to link these tasks with precedence constraints. Link the tasks as shown in Figure 16-12.

Figure 16-12

After adding the links, right-click on the constraint between the Read DB File Size task and the Copy Db File task. Click on the Edit option in the pop-up menu to open the Precedence Constraint Editor. Set the Evaluation option to Expression and Constraint and then enter the following line of code in the Expression field:

 (DT_I8)@FreeSpaceOnC > (DT_I8)@DBFileSize 

As you can see, this is where the massaging of the data in the Script task pays off. If you had not stripped the extraneous characters from the string, then the cast from string data type to the Integer data type would fail. Click OK to close the Precedence Constraint Editor dialog box.

Now you are ready for the moment of truth: running the package. If all went well, all the tasks should green up and the file should have been copied to the C:\Test directory. That is assuming you had enough space available on the drive.

I mentioned earlier about ways you could improve this example. It seem a waste that you have to hard-code the WQL query with the path to the file being checked for size, especially since the path to the file is already stored in the DBFile_Source variable. One option is to build the WQL query on the fly with a Script task. This would allow you to construct the path in the WQL in the proper format, namely changing the single backslash in the path to double backslashes. Also, in a more advanced scenario, the file could be located on another computer system. This could easily be handled by creating a separate WMI Connection Object pointing to the second system and assigning it to the WmiConnection property in the WMI Data Reader Task - Read DB File Size task. You can check out these enhancements in the samples included with this book.

WMI Event Watcher Task Example

In the previous example, you used WMI to check the size of a file before you copied it to the drive. But what if you want this action to occur whenever the file is updated? To do that, you would use the WMI Event Watcher task to monitor the file and kick off the WMI Data Reader package created earlier.

To start, create a new SSIS package called WMI Event Watcher Package. Now add a WMI Event Watcher task to the Control Flow page of the package. Name this task WMI Event Watcher Task - Monitor DB File. Right-click on the task and select Edit from the pop-up menu. You are now presented with the WMI Event Watcher Task Editor. Select WMI Option from the listbox and configure the properties as outlined in the following code.

First, create a WmiConnection pointing to the machine where the backup file exists. You can use the same connection properties as outlined in the previous example. Next, enter the WqlQuerySource that will monitor the file system for changes to the AdventureWorks.bak file.

 Select * from __InstanceModificationEvent within 30 where targetinstance isa 'CIM_DataFile' and targetinstance.name = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\AdventureWorks.bak' 

As you can see, this query monitors the AdventureWorks.bak file for changes every 30 seconds.

The rest of the properties are specific to the WMI Event Watcher task. Set the ActionAtEvent property to Log the Event and Fire the SSIS Event. As you'll see in a moment, this event will be used to launch the Db Data File Copy package created in the previous example. Next, set the AfterEvent property to Watch for this Event Again. This setting will essentially set up a monitoring loop that will perpetually monitor the file for changes as long as the package is running. Since you do not care if the task times out, leave the time-out settings at their default values. Click the OK button to close the dialog box.

Now that the task is configured, you need to configure the event handler that will be fired when a file change is detected. Click on the Event Handler tab and select the WMI Event Watcher Task - Monitor DB File in the executable combo box and then the WMIEventWatcherEventOccurred in the Event Handlers combo box. Click on the hyperlink in the middle of the page to create this event. Now drag an Execute Package Task from the Toolbox to the event page. Rename this task as Execute WMI Data Reader Package. Right-click the task and select Edit from the pop-up menu. In the execute Package Task Editor dialog box, click on the Package item in the listbox. For this example, you will be referencing the package via the file system, but in real life you would probably be calling a package that had been deployed to a SQL Server instance. For demonstration purposes, the WMI Data Reader Package file will be referenced so that you can see the package execute in the Visual Studio IDE. So in the Location property, choose File System. In the Connection property, create a new file connection pointing to the WMI Data Reader Package.dtsx file. Leave the rest of the properties at their default values. Click OK to finish configuration of this example.

Now test out your new package. Run the WMI Event Watcher Package. The WMI Event Watcher task should turn yellow. The WMI Event Watcher is now monitoring the AdventureWorks.bak file for changes. Now open the SQL Server Management Studio IDE and initiate the start of a backup of the AdventureWorks database. Make sure you back up the DB to the directory you're monitoring. At some point during the backup process, you should see the WMI Data Reader Package kick off and copy the backup file to the C:\Test directory.

When the copy is complete, the package will continue to monitor the backup file for change. So by initiating another backup, the package will detect the change and copy the file again.