Trigger SSIS package when files available in a Folder

Recently I have goon through a scenario where my client needs to run the SSIS package when the files placed in folder. To run the package automatically when the file exists we have an external tool called FileWatcherTask which is a great user friendly (be aware that FileWatcherTask can cause memory leak and it’s been discussed in the SSIS community. It sounds like you can avoid using FWT altogether) but we can find similar task in Control Flow Tool box, which is WMI Event watcher task.

Different Approaches to run the Package when file exists.

• Use the WMI Event Watcher task to trigger the SSIS packages as files arrive.
• Create a Windows service that uses WMI to detect file arrival and launch packages.

Let’s discuss each of the available approach.

In this post I would like to show how to WMI Event Watcher task and other approaches to run SSIS package automatically when a file exists.

WMI Event Watcher Task:

WMI Event Watcher Task makes SSIS to wait for and respond to certain WMI events that occur in the operating System.

Scenarios where we can use WMI Event Watcher Task:

• To watch a directory for a certain file to be written.
• To watch for the CPU to be free.
• Wait for a given service to start.
• Wait for the memory of a server to reach a certain level before executing the rest of the package or before transferring files to the server.

The task uses a subset of SQL language which is WQL language; it allows us to view information through WMI.

My example is moving a file from Source folder to Archive folder with a FileSystem Task, WMI Event Watcher will pass the execution to FileSystem task when a file placed in Source Folder.

I took two variables to pass the Source Folder path dynamically.

1. User:: Quote = “

2. User::SourcePath = F:\\\\DellMeSoon\

Drag WMI Event Watcher task to the Control Flow Design pane, double click on the task.

In WMI Event Watcher Task Editor, we will need to create WMI Connection Manager, leave the Server name and Namespace options and select the check box to Use Windows Authentication

Click on Test button to test the Connection.

Select WqlQuerySourceType as DirectInput and for

WqlQuerySource keep . (dot)

Under Expressions tab and select WQLQuerySource property and use the following WQL Query as Expression.

Expression:

"SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "+@[User::Quote]+ "CIM_DirectoryContainsFile" + @[User::Quote]+"

AND TargetInstance.GroupComponent= "+@[User::Quote]+ "Win32_Directory.Name=\\" + @[User::Quote]+ @[User::SourcePath] + @[User::Quote]+ @[User::Quote]

There are three distinct classes:

• __InstanceCreationEvent class, representing creation of a new object of a WMI class,
• __InstanceDeletionEvent class, representing deletion of an existing object of a WMI class,
• __InstanceModificationEvent class, representing modification to an existing object of a WMI class.

These classes are designed with the intention of being paired up with another WMI class, providing the ability to monitor changes to its objects.

WITHIN keyword set the interval between the event taking place and its notification being delivered to the WMI-based application (WMI Event Task, in this case).

WHERE clause includes the ISA keyword followed by the name of a monitored class.

Other properties includes:

ActionAtEvent: Specify whether the WMI event logs the event and initiates an SSIS action, or only logs the event.

AfterEvent: Specify whether the task succeeds or fails after it receives the WMI event, or if the task continues watching for the event to occur again.

ActionAtTimeout: Specify whether the task logs a WMI query time-out and initiates an SSIS event in response, or only logs the time-out.

AfterTimeout: Specify whether the task succeeds or fails in response to a time-out, or if the task continues watching for another time-out to recur.

NumberOfEvents: To specify the number of events to watch for.

Timeout: Specify the number of seconds to wait for the event to occur. A value of 0 means that no time-out is in effect.

Drag FileSystem task to move the File from Source folder to the Archive folder once available.

Now, execute the package. WMI Event watcher task will start watching the Source Folder till the file available. The task will be in execution mode and watch the source folder for every nth time which we gave in the WQL Query.

When the file is placed in the source folder WMI Event Watcher task will pass the execution to the next connected task.

Keep the package in Infinity loop for continuous watching of Source Folder.

Lets discuss about Windows Service Approach in my next post.

About these ads