WMI Event Watcher Task in SSIS with example

We have discussed about WMI Data Reader task in previous article. In this article I will discuss about WMI Event Watcher Task in SSIS. WMI event watcher task gives ability to SSIS respond to certain WMI event.

The following are some useful things can be done with the task

• Wait for a specific file or directory to be processed in the control flow
• Wait for CPU to be free
• Wait for some services to start/stop to proceed
• Watch for installation of any application
• Watch for any specific error message to proceed.

Drag and drop WMI Event Watcher Task from tool box onto control flow design as shown in Figure 1

  Figure 1

Double click on the task which opens up configuration editor as shown in Figure 2

Figure 2

In General tab you have to specify name and description of the task. Next tab is WMI options tab where you have to specify required properties for the task as shown below Figure 3

Figure 3

Specify WMIConnection connection by selecting <New Connection…> option under WMIConnection which pops up WMI connection Manager editor as shown below Figure 4

  Figure 4.

• Name: provide a name for the connection manager.
• Description: Describe the connection manager.
• Server name:provide the name of the server to which you want to make the WMI connection.
• Namespace:Specify the WMI namespace.
• Use Windows authentication: Select to use Windows Authentication. If you use Windows Authentication, you do not need to provide a user name or password for the connection.
• User name and password should be provided if Windows authentication is not selected.
• Test Options allows you to verify connection.

For the purpose of example, WMI connection is created with stand-alone work station(Local host) server as shown in Figure 4. 

Root\Cimv2 is default namespace for writing WQL queries

Other options in WMI options tab are as follows.

• WqlQuerySourceType option specifies source type of WQL query which can either direct input or file connection or variable.
• WqlQuerySource option specifies source of WQL query. Source will be provided based up on source type specified.
• ActionAtEvent: Specifies the action to take when the event occurs
• AfterEvent: specifies the action to take after event
• ActionAtTimeOut:specifies action to take when the time out occurs.you will specify Time out value in time out property.
• AfterTimeOut: specifies the action to take after timeout.
• NumberOfEvents: Specifies the number of events that task should watch
• TimeOut: Specify time out in seconds.Default is 0 which mean there is no time out.

Example: Keeps on checking C:\SSIS directory for delivery of a file to proceed.

For the purpose of the example, Following query should be passed which gives notification that any file is added in the directory.

SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\SSIS\""

The task WMI options tab should be displayed as shown below after configuration.

 

If you execute the task with no files in the C:\SSIS folder, it keeps on checking for the file and the task will be in Yellow color. Once you copy any file in the folder, task color turns into Green color and completed successfully.

NOTE: Please leave comment if you have any doubts