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 isa 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 HaseldenAuthor "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)

Yeah, script task will allow you to do this. Check out System.IO.File.Exists() static method -Jamie Jamie, File.Exists() cannot be used as the package will take the alternative path if file is not found whent the package initiates. A System Watcher is required.Regards, Raunak J Please 'Mark as Answer' if found helpful

Jamie, File.Exists() cannot be used as the package will take the alternative path if file is not found whent the package initiates. A System Watcher is required. Sorry I'm not understanding. What alternative path?http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

I am sorry for the confusion caused. The alternative path here means the else clause. The script will not wait and poll the designated folder till the file makes itself available.Regards, Raunak J Please 'Mark as Answer' if found helpful

I am sorry for the confusion caused. The alternative path here means the else clause. The script will not wait and poll the designated folder till the file makes itself available. Regards, Raunak J Please 'Mark as Answer' if found helpful It will if you put it in a loop which breaks out of the loop when the file is found. I suspect that is pretty much what the File Watcher Task does. http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Exactly, but looping till file found ain't good A WQL will kep the package dormant till the file is found...thus low on resource usageRegards, Raunak J Please 'Mark as Answer' if found helpful

Exactly, but looping till file found ain't good Ok. So break when the file is found or (e.g.) until some defined amount of time has elapsed. A WQL will kep the package dormant till the file is found...thus low on resource usage Agreed. Putting Thread.Sleep(X) into the loop with some suitable value for X will also keep resource usage low.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Using this works too - cause it does all that. Talk to me now on

use script task in ssis. Declare a variable FileExists as a boolean variable and use the below code. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Public Class ScriptMain Public Sub Main() Dim di As DirectoryInfo = New DirectoryInfo("c:\") Dim fi As FileInfo() = di.GetFiles("*.txt") If fi.Length > 0 Then Dts.Variables("User::FileExists").Value = True Else Dts.Variables("User::FileExists").Value = False End If Dts.TaskResult = Dts.Results.Success End Sub End Class