CODEWITHSUNDEEP
ssis
user3864233
user3864233

Reputation: 514

Triggering execution of SSIS package when Files Arrive in a Folder

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.

Upvotes: 5

Views: 7988

Answers (2)

Mike Honey
Mike Honey

Reputation: 15027

The WMI solution is interesting, but the environment / setup requirements are a bit complex for my tastes. I prefer to resolve this using a ForEach Loop Container and a Execute SQL Wait task, both inside a For Loop Container.

I configure the ForEach Loop Container to loop over the files in a directory, pointing it at the expected file name. The only task inside this Container is a Script Task that increments a Files_Found variable - this will only happen when files are found.

Following the ForEach Loop Container is an Execute SQL task to wait between checks, e.g. WAITFOR DELAY '00:05:00' for a 5 minute delay.

Both that ForEach Loop and Execute SQL task are contained in a For Loop, which initializes and tests the Files_Found variable.

This solution is a lot simpler to manage - it doesn't require any particular setup, environment, permissions, stored credentials or knowledge of WMI syntax.

Upvotes: 2

Kyle Hale
Kyle Hale

Reputation: 8120

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.

enter image description here

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

enter image description here

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

enter image description here

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

Event watcher wired up to foreach file enumerator

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

enter image description here

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.

Upvotes: 11

Related Questions