Tuesday 21 December 2010

Continuously watching files with WMI Event Watcher Task

Case
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...

16 comments:

  1. Thanks for all the steps... I was exactly looking for the same. Will be trying this out tomorrow.

    ReplyDelete
  2. How can I pass a value like "d:\\\\NewFiles\" in a variable to be used by WMI?

    ReplyDelete
    Replies
    1. You can create an expression on that property. See Expressions pane in the WMI Event Watcher Task Editor.

      Delete
  3. Very Very Helpful. I was wondering this type of task for ages. Thanks for this very good example

    ReplyDelete
  4. Is 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.

    ReplyDelete
    Replies
    1. Afaik it's only a trigger that something has happened, but I'm not a WMI guru.

      Delete
  5. Hi, my WMI watcher works but then if I leave the package for too long it stays running but no longer detects events... any ideas?

    ReplyDelete
  6. Hi,
    Is there a way to monitor for any file creation regardless of its parent folder?
    Mahmoud

    ReplyDelete
  7. Thanks 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'?

    ReplyDelete
    Replies
    1. You can only do that by mapping the drive: net use e: \\themachine\dropfolder

      Delete
  8. When 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.
    How do you combine this with another package to run outside of the development environment?

    ReplyDelete
    Replies
    1. 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.

      Delete
  9. on your WQL query can you use variable(s) to build the syntax, like:

    SELECT * 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

    ReplyDelete
    Replies
    1. 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

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...