Continuously watch files with WMI Event Watcher Task by setting “Watch for the event again” as value for AfterEvent property.

This post is to show how to develop SSIS package that keeps watching a folder and sends email if somebody pasted a file in that specific folder... and this is to illustrate how to use “Watch for the event again” value for AfterEvent property of  WMI Event Watcher Task.

1.  Create a folder to be watched. let’s say “C:\ImWatchingYou”.

2.  Open your SQL Server Business Intelligence Development Studio and create a new Integration Services Project.

3.  Right click the Connection Manager and choose “New Connection…”, a popup window will appear titled “Add SSIS Connection Manager”, scroll to and choose the item called “WMI” connection manager for the WMI tasks … probably the last item in the list. Another popup window will appear, check the “Use Windows Authentication” check box and leave the rest as it is (unless you want to watch another computer).

4.  Again right click the Connection Manager, but this time choose “New Flat File Connection…”. This will open the Flat File connection Manager Editor. In the Connection Manager Name write “Connection to The Pasted File”. In the File name insert any dummy file path for example “C:\ImWatchingYou\file.txt” .  in the same editor go to the Advanced tab and click new to add a dummy column to enable the OK button  (as you can see it’s now disabled) … click OK.

5.  Create a new variable, name it pastedFile, of type string, and the value is “C:\ImWatchingYou\*.*” 

6.  Right click the “Connection to The Pasted File” (in Connection Managers tab) and select properties. Open the Expression, choose ConnectionString Property, then insert @[User::PastedFile] as Expression.

7.  While in the Control Flow tab, drag the “WMI Event Watcher Task” item from the Control Flow Items in the Toolbox to the designer surface.

    Double click the task to open the WMI Event Watcher Task Editor. Go to the WMI Options tab and set the following:

a.       WmiConnection : WMI Connection Manager.

b.      WqlQuerySource :
SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\ ImWatchingYou \""

c.       AfterEvent : Watchfor the event again

d.      AfterTimeout : Return with success.

befor you hit the OK button, go to the Expressions tab and add a property expression for the Timeout property. Insert the following in the Expression field after selecting the Timeout property:
DATEDIFF( "ss", GETDATE(), DATEADD("Mi",55,DATEADD("Hh",23,(DT_DBDATE)GETDATE())) )
this will compute the number of remaining seconds till 23:55 which is the time we want the package to stop watching.

8.  Create another connection manager and choose the connection manager type to be SMTP. In the SMTP Connection manager Editor set the information needed to access the SMTP server.

9.  Drag and drop “Send Mail Task” to designer surface, then go the Mail tab and fill the :

a.       SmtpConnection : choose the SMTP Connection Manager.

b.      From: any email .. for example me@work.com

c.       To: your email.

d.      Subject: “Task finished notification”.

e.      MessageSource: “Task finished”.

10.   Connect the WMI Event Watcher Task to Send Mail Task.

11.   In the Control Flow tab select WMI Event Watcher Task, then open the Event Handlers tab, after that choose WMIEventWatcherEventOccurred from the Even handler combo box.

12.   Again, do the same as you did in step 9, except in the Subject and MessageSource insert “Someone pasted a file in the folder”

Now you are done. you can test it, bu note that when running the package outside the BIDS you will not be able to stop it until the timeout occurred or you will be forced to open the task manager and end all the DTCexec.exe or DTCexecUI.exe there... that's the only way I know.
By the way, there is another way to continuously watch a folder, which is by using infinite for loop. For more details check the following link:
http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi_25.html

Good luck :)