ForEach Loop and nothing to process

Question

ForEach Loop and nothing to process

Jason Tontz

SSCommitted

Points: 1957
More actions
July 21, 2011 at 10:34 am

#145913

I have an SSIS package that I need to run every 30 minutes or so to check for the existence of a file in a specific folder and perform processes using the file as a flag and then deleting the file. However, the issue is when the package runs and there is no file to process, the ForEach Loop just hangs. Is there a way to tell it to not run if there are no files in the directory?
Thanks In Advance.
JT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

the way I would do this would be to use a script task prior to the dataflow in the loop , in this script tasks use the file.exists method to check if the file exists.

If the file exists then set a variable to true, otherwise false, then create a constraint from the script task to the dataflow for the varaible = true.

Phil Parkin SSC Guru Points: 246944 More actions · Answer 2

If a foreach loop has nothing to process, execution transfers to the next task. So I suspect that there is something in the logic of your package which is causing the hang, not the FEL per se.

Perhaps you could post a screen shot of your control flow to help us diagnose the problem?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jason Tontz SSCommitted Points: 1957 More actions · Answer 3

Thanks steveb. I was thinking the same. I am working on a VBScript to do this. I rarely write VBScript so I was hoping to accomplish via the FEL object.

Jason Tontz SSCommitted Points: 1957 More actions · Answer 4

Phil Parkin (7/21/2011)
If a foreach loop has nothing to process, execution transfers to the next task. So I suspect that there is something in the logic of your package which is causing the hang, not the FEL per se.
Perhaps you could post a screen shot of your control flow to help us diagnose the problem?

Thanks for the reply Phil Parkin, After taking a closer look, the loop does finish (turns green), but the next process does not kick off, but the package doesnt "stop" either. Is running this in the BIDS act differently than when published to the SSIS server? Could this be where it is "breaking"?

Jason Tontz SSCommitted Points: 1957 More actions · Answer 5

I think I got it! Phil pointed me in the right direction. When I run it (Start without Debugging) in BIDS it processes with a file there and finishes running without a file there. I appreciate everyone's help.:-)