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...
Thanks for all the steps... I was exactly looking for the same. Will be trying this out tomorrow.
ReplyDeleteHow can I pass a value like "d:\\\\NewFiles\" in a variable to be used by WMI?
ReplyDeleteYou can create an expression on that property. See Expressions pane in the WMI Event Watcher Task Editor.
DeleteVery Very Helpful. I was wondering this type of task for ages. Thanks for this very good example
ReplyDeleteIs there a way to get the file name that the WMI watcher got triggered with? It seems that the only way is to then have another event/script that will look again through everything in that folder, but that seems a bit redundant. I'd like to actually get the file name and pass it to the following tasks.
ReplyDeleteAfaik it's only a trigger that something has happened, but I'm not a WMI guru.
DeleteHi, my WMI watcher works but then if I leave the package for too long it stays running but no longer detects events... any ideas?
ReplyDeleteNot sure what the cause could be... but what is too long? Hours? Days?
DeleteWhat about resetting it each day: http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi_25.html
Hi,
ReplyDeleteIs there a way to monitor for any file creation regardless of its parent folder?
Mahmoud
That would be easier with a Window Service
DeleteThanks for the post.The WMI task works fine locally.How do i set up path to a fileshare like '\\themachine\dropfolder' instead of 'c:\dropfolder'?
ReplyDeleteYou can only do that by mapping the drive: net use e: \\themachine\dropfolder
DeleteWhen you create this package. do you only have to run within BIDS or SSDT environment? I wondering how it can be used in SQL Agent so that it triggers a package when a new file lands.
ReplyDeleteHow do you combine this with another package to run outside of the development environment?
You can run this as a job with for example SQL Server Agent. Just like any other package, but the user that runs the job should have sufficient rights to execute a WMI query.
Deleteon your WQL query can you use variable(s) to build the syntax, like:
ReplyDeleteSELECT * FROM __InstanceCreationEvent WITHIN 10
WHERE TargetInstance ISA "CIM_DirectoryContainsFile"
AND TargetInstance.GroupComponent = "Win32_Directory.Name=\"+@[User::RootPath]
If so I'm having a little problem understanding the correct syntax
No you can't use variables like that. You have to create a string variable with an expression on it and then use that variable instead of direct input.
Delete