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 ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\ExpertSSIS\\\\temp\""

This query looks in the C:\ExpertSSIS\Temp directory for any file to be created. Change the Timeout property to 60 (seconds). This causes the event to wait for 60 seconds prior to causing a failure. The final screen will look like Figure 7-19.

Figure 7-19: Final screen

Back in the control flow, drag over a Script Task and name it Stub Code. Connect the precedence constraint from the WMI Event Watcher Task to the Stub Code. Execute the package and then create or copy any file into the C:\ExpertSSIS\temp directory. You will have to do this within 60 seconds of the package execution in order to see the package succeed, as shown in Figure 7-20. When you do this, within 10 seconds the Watch for File Task will succeed and the next task will execute.

Figure 7-20: Seeing the package succeed

Of course, the Stub Code Task does nothing at all, but in reality, would be a data flow or a container of tasks to transform the newly arrived file. This example is a common problem that every SSIS developer must, at some point, solve, where a mainframe or partner may be sending you a file through FTP and you want to kick off the process after that. If you want to simplify this process, you can also download a free task for watching for files from www.sqlis.com.