A Tech Log

November 3, 2008

SSIS: do so much with WMI Data Task and WMI Event Task

Filed under: Development — adallow @ 12:00 pm
Tags: ,

great blog post in this link, some really interesting stuff:

http://www.databasejournal.com/features/mssql/article.php/3552621

Quote from the Article:

Probably the most common data processing scenario in which WMI events could be helpful, involve launching a sequence of tasks activities as soon as data to be processed becomes available (e.g. by being copied to target computer). This can be accomplished by employing a SELECT query against __InstanceCreationEvent class in combination with another class, for which creation of a new instance is equivalent to creation of a new file on a managed computer. The best match in this case (from the performance point of view) is the CIM_DirectoryContainsFile class (this class forms an association between CIM_Directory and CIM_File classes, which are referenced as its GroupComponent and PartComponent properties, respectively). In order to detect a new file appearing in a specific directory (in our example c:\Data), you would run the following query (note that the last entry contains extra backslashes as so-called escape characters, which ensure proper string parsing. Such characters need to be applied whenever you want the next character to be treated literally. For example, by placing it after "Win32_Directory.Name=\, you ensure that the following double quote does not indicate the end of this string, but rather the beginning of the path to target folder c:\Data. Similarly, we added it in front of each backslash appearing in the folder path):

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

Now it is time to look into incorporating this functionality into SSIS packages using the WMI Event Watcher Task. Start by creating a new project using SQL Server Business Intelligence Development Studio. Drag the WMI Event Watcher Task from the toolbox onto the Control Flow area of the package designer interface. Right-click on it and select Edit option from the context sensitive menu. In the resulting WMI Event Watcher Task Editor window, switch to the WMI Options entry.

Create a free website or blog at WordPress.com.