22
Jan

Custom SSIS Component: File Watcher Enumerator

This page describes a custom SSIS ForEachEnumerator that has been created to provide continuous file watching functionality in SSIS by using a non-infinite loop which times out after a specified period of time. The source code for the component is available on CodePlex and has been tested with SQL Server 2008 R2.

Background

It is well known that file watching is not supported out of the box in SSIS. There are a number of attempts to introduce file watching in SSIS available on the internet. Using the built-in WMI task might work in some cases but it does not support watching UNC paths. Whilst this custom File Watcher Task does support UNC paths it would need to be embedded in some kind of loop (possibly infinite) in order to continually watch for files.

Component Design

Requirements

The component design had to meet the following requirements:

  • To provide continual file watching over a fixed period of time as opposed to firing on a single file watching event
  • To not require long running SSIS jobs
  • To not require the use of arbitrary or infinite loops
  • To be simple to implement and understand in SSIS designer
  • Support watching of UNC paths
  • Watch for file creation events but only file once the file has been fully created (rather than on the initial touch)

Features

The component has been designed with the following features:

  • Monitors a target directory for files created matching a provided filespec (e.g. *.txt).
  • Fires only once the file is available for an exclusive lock so that the SSIS job is capable of moving or otherwise processing the watched file.
  • Once processed will wait for another event.
  • Is capable of running continuously or until a specific TimeOut value (in seconds).
  • Can optionally initially process any existing files in the target directory that match the provided filespec.

Usage

Whilst it is possible to configure the enumerator to run indefinitely by using a TimeOut of 0 this usage is not recommended in production. SSIS jobs are designed to start and finish. Therefore the recommended setup is to schedule the SSIS job to run every minute and set a timeout on the loop of 50 seconds. Thus the job will watch files continuously for 50 seconds before completing. 10 seconds later the job will start again. If for any reason a watched file is missed (which is theoretically possible with the current implementation – see source code) then in this setup the file will be picked up within 1 minute in any case (provided the ProcessExistingFiles flag is also set to true).

Drag a ForEach container into the Control Flow and open the component properties

file-watcher-1

From the Collection section change the Enumerator type to ‘File Watcher Enumerator’

file-watcher-2

Now place the tasks you wish to execute on the created file within the loop container:

file-watcher-3

Limitations

  • There is currently no design-time component validation.
  • It is possible that the loop will miss a watched file in some cases. This is documented in the source code, as is the solution to resolving this issue.

References