Execute or Trigger SSIS package when source files are available

In any mission critical ETL solution, watching out for source feeds (probably in the form of files) and triggering corresponding ETL package to process the feeds is a very regular requirement. In SSIS task repository, File Watcher task is missing and many would answer the File Watcher Task is one of the probable solution to this requirement. Whether this task performs good or weak is another matter, but I am not of the opinion that any task should be used for this requirement at all.

In my opinion, there is no point is keeping the packages on stand-by and in execution mode indefinitely to process the data feeds. Consider a scenario where a global stocks trading organization receives data feeds 24 x 7, and there may be ETL jobs for processing feeds from each country or city. It is not a sensible design at all to keep all the packages running on stand-by mode using some tasks.

The question that comes to the mind is what is the solution for this requirement? In my views, a windows service that can be developed using .NET is one of the best solutions. Instead of using a polling mechanism or keeping packages on stand-by and executing them indefinitely, a simple windows service can be developed using .NET code. Using Windows Management Instrumentation (WMI) one can subscribe to windows events to watch out for any file creation in a directory and the same is easy to implement in .NET code. WMI is one of the way I had used way back before many years, but I am sure there must be better ways of detecting this using the latest version of .NET Framework.

Windows service does not require any active logon to the server and it can be configured to keep running in background. Once this service detects any file creation, it can kick start the package. Also the execution schedule and logic to trigger the package can be made configurable by making the windows service to read parameters from a config file or from database.

I do not intend to say that there is no good use of File Watcher Task, but in cases of scenarios like the one I mentioned above, especially in mission critical production environments, if I were to make a decision, I would use the technique I suggested. It would be nice if Microsoft comes out with a nice scheduler level support for such requirements.