Windows Management Instrumentation Tasks In SSIS

SSIS includes two special tasks that enable you to query system information and monitor system events: the WMI Data Reader Task and the WMI Event Watcher Task. These tasks are especially useful for system management, as you will discover with examples later in External Management and WMI Task Implementation Topic. WMI uses a specialized query language known as WQL, which is similar to SQL, to obtain information about a Windows system. WMI has many features and capabilities, so we won’t be able to cover all of them, but here are a few common uses:

>> You can get information about 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 determine whether 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 is useful if your package needs to know whether a companion process is 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 has certain permissions to a resource. This is 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 is useful if your package needs to stop service during a data transfer.

Learn how to use SSIS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free "SSIS Training" Demo!

This is just a small sample of the information you can glean from a computer system. You can obtain information not only about the current system but also about remote systems. As you can see, this gives you access to a great deal of information that could be used in the execution of an 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 External Management and WMI Task
Implementation Topic you will see how to actually implement these two examples. For more information on the WMI system, visit here.

WMI Event Watcher Task in SSIS

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: This is a configured WMI Connection object.
• WqlQuerySourceType: This setting specifies where the WQL query is referenced. The query can be manually typed in or 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 in which the results of the WQL query are stored after execution.
• Overwrite Destination: This determines whether the previous results are retained or overwritten when the task is executed.
• Destination Type: This enables you to specify how the results will be stored.
• Destination: This enables you to specify the location of the destination type.

To start the 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 for most machines is the rootcimv2 namespace. This namespace contains the majority of WMI classes that can be called to get system information. The connection object specifies the target computer system that the query will be run against. By default, the SSIS WMI Connection points to the localhost machine, but remote systems can be specified as well by using the NetBIOS, IP address, or DNS name of the remote machine. Because 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. Because WMI is so extensive a subject, External Management and WMI Task Implementation Topic can’t explain the intricacies of the model. We suggest that you locate a good Tutorial on WMI scripting to learn the details of how WMI works. Another resource for free WMI tools is the MSDN downloads site. Two applications that are helpful for WQL query generation are the Scriptomatic V2 application available at download center, which enables you to browse the classes in WMI namespace and generate WMI queries in several different scripting formats. This package includes several sample 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 options: 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 you’re obtaining numeric information from the system, you must convert the resultant string to the appropriate data type for use in a mathematical expression. The file transfer example suggests one way to accomplish this transformation, but this is not the only way. 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 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 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, you will learn 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 (but any large file will do). If you don’t know how to create a backup of the AdventureWorks database, you can create any large file or use a file from one of many examples in this Tutorial. If you do use the AdventureWorks backup, it will tie into the WMI Event Watcher Task example later in External Management and WMI Task Implementation Topic.

Open a new Integration Services project and call it WMI_DataReader. 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; in this case, call it “WMI Data Reader Task – Read Free Space on C.” Now, right-click the task and select Edit from the pop-up menu to bring up the WMI Data Reader Task Editor. Click the WMI Options tab to render the editor, as shown in below screenshot.

Click in the WmiConnection parameter field and select the button to the right. Select from the dropdown list. The dialog shown in below screenshot will be displayed.

Give the new WMI connection a name and enter a description. You can also 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 rootcimv2. The setting of 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. This completes the WMI connection and adds it automatically to the WMIConnection property in the editor.

Back in the WMI Data Reader Task Editor dialog, leave the WqlQuerySourceType as DirectInput. Next, select the WqlQuerySource field and click the ellipsis button on the right. In the dialog 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. Set the DestinationType property to Variable. Click in the Destination field and choose the ellipsis button to the right and select. In the Add Variable dialog that appears (shown in below screenshot), enter FreeSpaceOnC in the Name field, set the data type to string, 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. We’ll explain the string data type in a minute.

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 for this new task. Click in the WMI Connector field and choose the WMI Connection Manager connection. Because 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 the ellipsis to the right to open the query editor dialog. Enter the following query:In the OutputType field, choose Property Value. In the DestinationType field, choose Variable, and then click in the Destination field and choose. 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. Hook them together so that you can add some logic to handle the data the WQL query will return. It was stated previously that the WMI Data Reader could only write to strings and Data Table objects. Well, when a string is returned, it has several extraneous characters at the end that 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 task turns green, go to the Variables tab and look at the data in the two user-defined variables. The value looks like this: “FileSizern45516800rn.”

To massage this data into a usable form suitable for conversion to an integer data type, you will create a Script Task to strip the extra characters from the string, leaving just numeric digits. To start, click the Event Handler tab of the package. In the Executables dropdown box, choose the WMI Data Reader Task called “WMI Data Reader Task – Read Free Space on C.” Next, 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 onto the page. Change the name of the object to “FileSizeOnC Data Massage.” Right-click the task and select Edit from the pop-up menu. On the left-hand side of the Script Editor dialog, choose the Script page. In the ReadWriteVariables property, select the variable User::FreeSpaceOnC. This will give you read/write access to the variable from within the Script Task.

Now, click the Edit 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 (see the WMI_DataReader folder):

As you can see, this code parses the string and uses the RegularExpressions library to strip the characters from the returned value. Then the cleaned-up string is cast to return an Int64 value as a string. In short, this code will strip all the extraneous characters from the string and return a numerical result into the same variable. As a result, the contents of the string are ready to be used in a mathematical expression. To finish, close the Script Host windows and click OK to close the Script Task Editor dialog. 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 are to set up the file transfer and add the precedence constraint that ensures 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 the task and click Edit in the pop-up menu. In the File System Task Editor, set the following properties as shown in below screenshot.

The final step is to link these tasks with precedence constraints. Link the tasks as shown in the below screenshot

After adding the links, right-click the constraint between the Read DB File Size Task and the Copy Db File Task. Click 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)@DBBackupFileSize

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 the String data type to the Integer data type would fail. Click OK to close the Precedence Constraint Editor dialog.

Now you are ready for the moment of truth: running the package. If all went well, all the tasks should run successfully and have a green checkmark icon on the top-right corner of the task, and the file should have been copied to the backup directory (assuming you had enough space available on the drive).

 

Frequently Asked SSIS Interview Questions & Answers

 

We mentioned earlier about ways you could improve this example. It seems a waste that you have to hardcode 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 parameter. One option is to build the WQL query on the fly with a Script Task. This would enable 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. For remote machines, use the NetBIOS name, the IP address, or the DNS name in the ServerName property instead of the localhost default setting.

WMI Event Watcher Task

As outlined earlier, not only can WMI obtain information about a computer system, it can also monitor that system for certain events to occur. This capability enables 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 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 should 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 specify what should happen after the WMI event occurs. The options are Return with Success, Return with Failure, or Watch for the Event Again.
• ActionAtTimeout: This defines the action that should be taken if the task times out waiting for the WMI event to occur. Options are Log the Time- Out and Fire the SSIS event, or just Log the Time-Out.
• AfterTimeout: This defines what action should be taken after the task times out. This option sets what should happen after the ActionAtTimeout occurs. The options are 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 reacts 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 passing to subsequent tasks a success or failure of the WMI Event Watcher Task or simply continuing to monitor for the event to occur again.

WMI Event Watcher Task Example

In the WMI Data Reader example, you used WMI to check the size of a file before you copied it to the drive. You would most likely perform this type of task after some other process created the backup of the database. In some cases, you can execute the package manually when you are ready to perform the actions in the package. However, if you need certain tasks to be performed in response to an event like the backup file being created, then use the WMI Event Watcher Task. This task can monitor any system event, including the creation of a file like the backup file you used in the WMI reader example.

In this example, you’ll use the WMI Event Watcher Task to look for the file, and then kick off the WMI Data Reader package created earlier. You could also use this example to look for incoming data files that need to be processed. You can see an example of this use of the WMI Task in SSIS Task Topic. 

To use this task to determine when the backup has completed from our first WMI example, create a new SSIS package called WMI Event Watcher Package. 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 the task and select Edit from the pop-up menu. You are now presented with the WMI Event Watcher Task Editor. Select WMI Options from the pane on the left and configure the properties as outlined in this section.

First, create a WmiConnection pointing to the machine where the backup file would normally be created. In this example that will be the root directory of the WMI_DataReader project. 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:

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 is 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 essentially sets up a monitoring loop that perpetually watches the file for changes as long as the package is running. Because you don’t care if the task times out, leave the time-out settings at their default values. The editor should look like below screen shot. Click the OK button to close the dialog.

Now that the task is configured, you need to configure the event handler that will be fired when a file change is detected. Click 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 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 Execute WMI Data Reader Package. The Execute Package Task event handler should look like below screenshot.

Right-click the task and select Edit from the pop-up menu. In the Execute Package Task Editor dialog, click 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 called 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. Therefore, 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. The Execute Package Task Editor should look like the below screenshot. Click OK to finish the configuration of this example.

When the copy is complete, the package will continue to monitor the backup file for change. When the next backup is found in the root WMI_DataReader directory, the package will initiate another file copy of the backup. The package is responding to WMI events that detect the change to the directory to copy the file. As you can see, these WMI features provide an extremely powerful capability that SSIS does a great job of abstracting for you in these WMI Tasks.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator