Triggering execution of SSIS package when Files Arrive in a Folder

Question

I have scenario in SSIS. I have a package which is simple data movement from flatfile to database. I have a specific location and I want to execute that package when file comes on the folder.

Answer 1

Step-By-Step using WMI Event Watcher Task

1. Create a WMI Connection manager. Use Windows credentials when running locally (you must be an admin to access WMI event info), and enter credentials when running remotely (be sure to encrypt your packages!)
2. Add a new WMI Event Watcher Task. Configure the WQLQuerySource property with a WQL query to watch a specific folder for files.

WQL is SQL-like but slightly off, here's the example I'm using to watch a folder:

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

Breaking down this query is out of scope, but note the directory name in the filter and the string escaping required to make it work.

3. Create a For Each Loop and attach it to the WMI Event watcher Task. Set it with a Foreach File Enumerator, and set the folder to the folder you're watching.

4. In the Variable Mappings tab of the For Each Loop editor, assign the file name to a variable.

5. Use that variable name to perform actions on the file (for example, assign it to the ConnectionString property of a Flat File connection and use that connection in a Data Flow task) and then archive the file off somewhere else.

In the diagram below, this package will run until a file has been added, process it, and then complete.

To make the package run in perpetuity, wrap those two tasks in a For Loop with the EvalExpression set to true == true.

You can also consider registering object events using PowerShell and kicking off your SSIS package when those events are triggered. This requires a little less continuous overhead of having your package constantly running, but it adds an extra dependency.