Check if file exists

In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:

a) The File Watcher task from www.sqlis.com

This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need.

b) The WMI Event Task

There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \\servername\path\file.txt) which I could not get to work.

c) Script Task using the File.Exists() method

I imported the System.IO namespace, and used a File.Exists(\\servername\path\file.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach.

I'm open to suggestions or ways to get options a) and b) to work for my needs.

Thanks!

Kory

Most folks use a special folder that only contains files that need to be processed. That way, you can have workflow that processes any files that exist in the folder without concern for whether it is the right type of file etc. because only the correct file types get dropped there.

Then, you can use the file watcher task effectively because you can have two processing sections in the package, a part that picks up whatever files exist in the folder and then another part that waits for new files to appear.

HTH,

Kirk Haselden
Author "SQL Server Integration Services"

|||

Your scenario descibes basically what we do. There is a single folder with all text files, but with extention of ".flg" These files do not actually contain any information (other than date/time/process) but are only for triggering other processes to began. The file names are associated with the names of tables loaded in our DW.

Unfortunately the flag file process we rely on is out of my control, and is managed by another entprise group within my company. I only have read-only access to monitor a single folder that contains about 100 files, each named corresponding to the table that has become available. I need to check this folder starting 3:00am every morning and continue to monitor it until a specific file appears. The folder is emptied at 3:00pm the next afternoon every day. If the file already exists at 3:00am, this means the table was ready earlier than 3:00am so the process can resume as normal.

So, I am still looking for a solution...

-Kory

|||Why not use the script file task to check if it existing and a file watcher to wait if not there. Some simple workflow should allow this scenario.|||

Yeah, script task will allow you to do this. Check out System.IO.File.Exists() static method

-Jamie

|||

The File Watcher Task has been updated to now check for an existing file that matches the criteria. This behaviour optional, with the default being to only look for new or changed files, as with previous versions. This can be controlled by the new FindExistingFiles property.

The current release (1.2.4.55) is fully backwardly compatible with previous versions, just uninstall the old version and then install the new version. It will add the new property on any subsequent package save, or you can force an upgrade within the Solution Explorer tool window, by right-clicking and selecting Reload with Upgrade, although this is not necessary.

File Watcher Task
(http://www.sqlis.com/default.aspx?23)