Expert SQL Server™ 2005 Integration Services

7.7. Building a File Watcher Task

Now that you know about the WMI tasks, let's try a real-world common problem. In this example, you want to create a process to constantly watch a directory awaiting for any file to arrive. You'll do this through the WMI Event Watcher Task. It's important to note that before you create this example, you would never want to create a process like this to endlessly loop. You'd instead want to have some sort of timeout so the package has deterministic start and stop times.

Let's start by creating a new package called FileWatcher.dtsx and create a directory under C:\ExpertSSIS called temp (C:\ExpertSSIS\temp). Start the package by dragging over a WMI Event Watcher Task and name the task Watch for File. Open the task to configure it. In the WMI Options tab, select <New Connection...> for the WMIConnection property. This opens the WMI Connection Manager Editor. Name the connection Localhost and then select the Use Windows Authentication check box, as shown in Figure 7-18.

Figure 7-18. Naming the connection in the WMI Connection Manager Editor

Click Test to confirm the connection is valid and click OK to go back to the task editor. In the WMI Options tab, copy the following query into the WqlQuerySource property. You can download this query from the Wiley web site (Chapter7WMIFileWatcher.sql):

SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ...