Expert SQL Server™ 2005 Integration Services

7.6. Proactive WMI Integration

SSIS has extensive hooks into Windows Management Instrumentation (WMI). WMI is an object that allows you to interface with Windows easily to add reliability to your package. For example, prior to attempting a large move of a file from one server to another by using the File System Task, you could use the WMI integration to check to ensure there's enough space on the target server. If there's not enough space, you could then just send an email to the administrator and gracefully exit.

This integration is achieved through the WMI Data Reader Task and the WMI Event Watcher Task. Each task uses the Windows Query Language (WQL) to look into the Windows operating system and determine things like the amount of disk space, the services that are running, if there are any errors in the event log, and if a file exists. The sky is really the limit. If you can click it in Windows, you can achieve the function through WQL. An example WQL query looks like the following one, which retrieves all the space information about the hard drives:

Select * from Win32_LogicalDisk

You can use tools like Scriptomatic to help you write WQL queries, or you can go to the following URL to find a list of samples that you can tweak. At the same URL, you can find Scriptomatic:

www.microsoft.com/technet/scriptcenter/scripts/storage/default.mspx.

There are two tasks that help you interface with the WMI objects. The WMI Data Reader Task will run a WQL query and then return the results ...