WMI Event Watcher Task tips

I had a problem with a “inherited” (aka: no one knows something , i must discover what is running and what it does…) IIS 7.5 server with an SQL 2008 instance running Reporting services, SSIS jobs (that i should transfer to another server) where sometimes the processor was at 100% for minutes.

Obviously the users are complaining about the lack of service : is a mission critical server; so a first step was to know how many times the processor was over 80% along the work day.

On the same server was needed to copy files in a directory , where a “watchdog” should read and process when a files was uploaded.

Giving the fact that during the night on this server are running some heavy jobs and the directory watchdog is not needed in the day hours, it was requested that the job surveilling the processor was running only during the day and the file watchdog only the night, in order to avoid unuseful workloads.

I don’t know server management software (as Nagios for example) and similar so i thinked an quick&dirty solution for the processor running at 100% .

The WMI Event Watcher seems perfect for the job , but the SSIS world is very complex and i have discovered some things about the WMI Event Watcher.

The first step is to create from the SQL Server Data Tools a new project , it creates automatically a new .dtsx package , here in the Control Flow we can drap&drop from the SSIS toolbox an WMI Event Watcher:

Double clicking the new object we define the properties, where for the first thing we define a new WMI connection:

The WqlQuerySource is the earth of the WMI procedure, a Wql query is a SQL-like language; for example the query

Is the condition is reached (we drop a file in that directory), is launched an MessageBox with text “__InstanceModificationEvent event has occurred.”

This is a simple case , with the case of the directory watchdog the things goes complicated.

The Event Class in this case is __InstanceOperationEvent, the Target Instance CIM_DirectoryContainsFile, for which we should insert the directory name with the syntax “Win32_Directory.Name='<foldername>'”.

The problem is that the wizard is creating string delimited from ” , and we have the slash char \ (directory separator in ms-dos paths) that must be escaped, in SSIS we escape a single \ with \\\\ (4 characters).

The problem is complex because we must insert string with a mix of delimiters (‘ and “”) , for example my first attempt was:

(“SELECT * FROM __InstanceOperationEvent WITHIN 60 WHERE TargetInstance.GroupComponent = “Win32_Directory.Name=’g:\\\\WMIFileWatcher'””)

But this causes an error launching the code (lacking of a parentesis); note that after we must insert the working code in the WqlQuerySource property of the WMI Event Watcher .

In order to be not driven mad we could correct the generated code (is not readonly) as

Note for WITHIN : is the number of seconds between each WMI polling of a directory, a low value causes an high CPU load, an high value could lead to problems if the file in the meantime is changed or deleted: it is necessary to find an adequate value for the specific case.

But the problem with the WMI Event Watcher is related to the event : when it fires our WMI Event Watcher “dies” and the dtsx is terminated.

A first approach could be to insert the Watcher inside a loop:

The loop espression could be

And our dtsx is running forever.

Now we can construct the Wql query using first Script Task linked via a Precedence Constraint to the WMI Event Watcher:

And using the variable varScriptSrc as source of the WMI Event Watcher:

The variable must be , obvious, specified as a read write variable:

But our problem is that we need to stop the procedure at a certain hour, so we change the EvalEspression in

GETDATE() < DATEADD(“Mi”,30,DATEADD(“Hh”,18,(DT_DBDATE)GETDATE()))

What is this , that seems TSQL but is not TSQL? Is an example of the Wql syntax , a SQL dialect that supports the WMI infrastructure.

If this case the condition is true when the system date is over 18:30

How to construct these queries ? In MSDN there is an reference , but we need something to test the Wql queries , we can’t debug the EvalExpression…

So is very useful a software as the Expression Tester Tool , that can be downloaded from Codeplex.

Here we can create our Wql with the treeview wizard or write directly a Wql string and try what happens :

Now that we have a tested Wql query we can use it in our Loop:

But there is a problem : until does not happens that a file is copied in our guarded directory, the dtsx is running forever despite the EvalExpression, which is tested only at the first launch !

We can be resolve the problem using the Timeout property :

Note that is specified an timeout (20 seconds) that must be >= of the WITHIN time in the Wql query (in our sample 10 seconds).

What happens at this point? If a file is dropped at the end of the polling (WITHIN 10) interval the dtsx flows outside the WMI Event Watcher , we can do something with the event via a Script Task, for example, after the WMI Event Watcher:

otherwise is nothing happens (no file dropped in that directory) after 20 seconds the code goes in every case to the Script Task as when a file is dropped .

Here there is the problem : apparently with this approach there is nothing that we can test in order to understand if the Script Task is running because was happened an Timeout or a file was dropped in the surveilled directory.

And beware of the Expressions, you can create something as:

Using some test variable as the varResult of the example , the sample expression is compiled without errors but is absolutely wrong this thing (should be provided an warning…)! the expression serves to calculate the value of the property instead of a fixed value chosen drom the dropdown : in this case the AfterTimeout dropdown

is no more working (is not possible to change the value) because its value is calculated from the Expression; its result should be one of the enumerated values so the sample expression is a no sense (even if it is correct, from the syntax point of view).

In practice the correct approach is to use the Event Handlers, not a Script Task or other after the WMI Event Watcher:

where we can correctly manage if the WMI Watcher is exited because a timeout or because (with the WMIEventWatcherEventOccurred event) a file was dropped.

In any case (timeout or file dropped) our loop condition is tested at least every 20 seconds (the defined timeout) so the Loop condition is evaluate ; at least 20 seconds after 18:30 our final package:

is terminated; the same approach can be taken for the CPU surveillance.

For SSIS expressions some useful references here and from Microsoft, which has a dedicated blog.