Monday, May 21, 2007

Konesans’ File Watcher Task in SSIS

Last week I was asked to help a friend redesign some of his ETL processes. It was a great exercise and quite fun knowing that we had to have it completed in the same day due to some time constraints from his customers.

File watchers are programs that ‘listen’ for a file to be placed in a directory. I’ve worked with file watchers written in C, PERL, and manual kick-off processes. Thus there are many ways to do a file watcher programmatically, and today we are going to look at how it is done in SSIS.
The need was to create a file watcher that waited for two files to arrive, then process them in a loop. It looked like this.

The file watcher was created by Konesans – they have good writeup on their webpage about the properties so check it out for information. This post merely shows the product in a production environment.

The task has the following properties:

I’ll explain key variables.

1. Name = name of the task.

2. Description = put a meaningful description here (‘watches for the files’ is not meaning and states the obvious).
Filter = *.* looks for every file. *.induct would only pick up files like TEST.induct.
FindExistingFiles = true if there may be files in the directory already. If False, then it ignores existing files.
Output Variable name = the variable name we assign in the variable box below.

Then I created a Package level variable called User::FileName to store the file name. You must also create a connection manager to list the directory the file will watch for (or create a new one).

The file number variable was used for the looping mechanism – to loop until two files were loaded. Once the file was found, the filename was written into the variable FileName and available for access further down the chain.

Here’s a screen shot of the process running. It looped twice, running the data flow tasks below each time. This task inserted the file name into an audit table. Once the loop was finished, the last file inducted was processed.

Now you should have a better understanding of the excellent Konesas’ file watcher task and a real-world application of this tool.

1 comment: said...

Can u explain in detail,because if we paste multiple files in a folder I am getting only one file name as Out Put variable Name. Please help me in this manner.