Using the Transfer Jobs Task in SQL Server Integration Services I discussed how to use the Transfer Job Task which is available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of the WMI Data Reader Task and WMI Event Watcher Task which is available in SQL Server 2005 and later versions.
If you are new to SQL Server Integration Services then I would recommend you to start with my introductory article – SQL Server 2008 Integration Services Tasks.
Overview of WMI Data Reader and WMI Event Watcher Task in SSIS
Windows Management Instrumentation (WMI) Tasks within SSIS can be used by to read WMI data and to watch for WMI events. There are two WMI tasks available with SSIS – WMI Data Reader Task and WMI Event Watcher Task.
WMI Data Reader Task: Can be used to run WQL queries against the Windows Management Instrumentation (WMI) to read the event logs, determine hardware information etc from a local or a remote computer system. You can use the WMI Data Reader Task to:
· Identify the amount of free/used space on a hard drive on a local or a remote computer system.
· Generate a list of all the applications and the application versions installed on a local or remote computer.
· Generate a list of all windows services running on a local or remote computer.
WMI Event Watcher Task: Enables SSIS to wait for a WMI event and then respond to that event. You can use WMI Event Watcher Task to monitor any of the below events.
- Keep polling a directory for the availability of files which are being written.
- Wait for the CPU utilization to come down below a certain threshold to trigger events.
- Wait for the server memory to reach a threshold before executing a SSIS package or any process which consumes a lot of server memory.
- Wait until a defined windows service to start before proceeding with a specific action.
Important to note is that in order to query a remote machine you must have Administrative privileges on the remote computer system or else run the SSIS package as an Administrator. In most cases, this will mean running the package as a network administrator or by running the package by supplying the relevant Administrator login credentials.
Example – Using WMI Event Watcher and WMI Data Reader Tasks
In this example, we will be using both the WMI Event Watcher and the WMI Data Reader Tasks to query the list of windows services which are running on a local machine. In order to use a WMI task you need to have administrative privileges on the local and remote machine from which you want to gather information.
Configuring WMI Event Watcher Task
1. Create a new SQL Server Integration Services Project and rename the default package WMITasks.dtsx
2. Double click the WMITasks.dtsx package to open it in Design mode.
3. Drag and drop “WMI Event Watcher Task” from the toolbox to the Control Flow window.
4. Double click the WMI Event Watcher Task to open up the WMI Event Watcher Task Editor and navigate to the WMI Options tab as shown in the snippet below.
5. In the WMI Event Watcher Task Editor, you will need to configure a WMI Connection by clicking <New WMI Connection…> from the drop down list. It will open up the WMI Connection Manager Editor as shown in the snippet below. In the WMI Connection Manager Editor provide the name of the connection as “WMI Event Watcher Connection Manager” and select the check box to Use Windows Authentication. Leave the rest of the values unchanged and click OK to save the WMI Connection Manager.
6. For WQLQuerySourceType there are three different options – Direct Input, File Connection and Variable. In this example, you need to choose Direct Input.