SSIS Check if file exists for 60 minute window

[JoeyArr]

Hello,

I am new to SSIS programming and am working on a project to check if a flat file exists on a file share between 5 and 6pm only. If not there, email failure status to be sent. If there, query flat file and output to another flat file and output to new folder. Then email success status. I've tried a ForEach container to loop for the duration window and then use script task to check for file, but have not successfully configured how to jump out of the loop when the file is found using a FileExists variable. Any pointers would be much appreciated.

Thanks,
J

 

[RiverGuy]

So you're saying that you are expecting a file to arrive to a specified location sometime between 5PM and 6PM each day? Why not just schedule the package to run once at 6PM, checking for the file then? Creating a package to run a loop for potentially an hour each day doing nothing doesn't sound like a very good idea.

 

[ESquared]

You should probably post this in the SSIS forum.

 

[AlexCuse]

IIRC, There is a FileSystemWatcher object you can use in SSIS for just this task. A windows service may be more appropriate though. If you do go the SSIS route, make sure you stop the package after an appropriate amount of time if the file does not get there.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[AlexCuse]

IRIC :-(

I think I had this in mind:

http://www.sqlis.com/23.aspx

You can use the .net FileSystemWatcher class (in the System.IO namespace) from within a script task though. If a windows service was not available I'd probably go that route.

Hope this one helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[JoeyArr]

Thanks for all responses. I agree looping for the sake of looping doesn't make much sense but that is protocol here. I believe the intention is for job to complete as soon as file is available. Then scheduling the package for the window and looping while check for existence.

Actually used some scripting to resolve problem and meet requirements for the package. Runs fine.

If I'd have found .net FileSystemWatcher sooner, that might be part of this package as well. Thanks again. First SSIS package.