A client wants to have a continuously running package watching for new files in a folder, so he can automatically process new files throughout the day without manually starting a package each time.
Solution
The solution includes two aspects: watching for new files and the continuously running part. Let us start with the watching for new files part.
Watching for new files
There are some third party filewatchers available, but we will use the standard WMI Event Watcher Task.
1) Drag the WMI Event Watcher Task to your Control Flow and give it a suitable name.
 |
| WMI Event Watcher Task |
2) Go to the WMI Options tab and create a new WmiConnection. Select Use Windows Authentication for this exercise. It will use the account that is running your package.
 |
| WmiConnection |
3) For the WqlQuerySourceType we will use the default Direct input so you will have to enter the WMI query under WqlQuerySource.
 |
| Wql (SQL for WMI) |
4) Enter the Wql below in the box. There are two points of attention. First is the WITHIN 10 part. This indicates the number of seconds between each check. A too low number could overload your system. Second is the Directory name; notice the extra backslashes in the path.
SELECT * FROM __InstanceCreationEvent WITHIN 10
WHERE TargetInstance ISA "CIM_DirectoryContainsFile"
AND TargetInstance.GroupComponent = "Win32_Directory.Name=\"d:\\\\NewFiles\""
Continuously running
5) The easiest way to create a continuously running package is to use a For Loop and set the following EvalExpression: "true == true". This will result in an infinite loop. Note: versions before SQL 2005 SP2 have a memory leek in the for loop, but this was fixed in SP2.
 |
| Infinite loop |
6) Drag the WMI Event Watcher Task into the Infinite loop.
 |
| Continuously watching |
7) Now add your own tasks behind the WMI Event Watcher Task. A possible solution could be a For Each Loop that loops through all files in d:\NewFiles\, processes them and moves them to an archive folder with the File System Task.
 |
| A possible solution |
Note: the package won't stop by itself (only errors or timeouts will). I will treat that issue another time...