How to Fail the package when for Each loop does not find files

Question

How to Fail the package when for Each loop does not find files

Karthika Raman

SSCrazy

Points: 2520
More actions
September 6, 2007 at 10:47 am

#153804

I have a package that has a For each loop container that has several tasks within it. The tasks are to modify the format of the file like removing delimeters, padding columns etc and bulk insert it into a loading table. The loop container is then connected to other tasks on success. These tasks are to check for datatype mismatches, apply business rules etc and move the data into a staging table.

The problem I am having is that when the loop container does not find a file, It still continues on to the tasks outside of the loop container and reports it is as a success. I want the package to fail if there are no files in the expected folder. How can I do that?

Thanks
KR

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

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

Karthika Raman SSCrazy Points: 2520 More actions · Answer 1

Since noone seemed to have any pointers, I created a script task that checks for the existence of files before the For Each loop container and fails the package if it finds none.

KR

Ken Gitta Grasshopper Points: 20 More actions · Answer 2

Hi Can you please display the package that you run. I could help with that!

Karthika Raman SSCrazy Points: 2520 More actions · Answer 3

I am not sure what you mean by display the package, are you asking me to send you a copy of the whole dtsx file?

By the way, I wanted to add that the script task if working fine, I can use it to send notifications for certain criteria, capture the count of the files, etc. However, I would like to know if the loop container would allow for failiures on set conditions.

Thanks

KR

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

I would suggest that you create a variable, set the variable initial value to 0, set the value to 1 inside the foreach loop container (it will set it to 1 repeatedly, but who cares), and then check the variable in the control flow connector after the loop.

Karthika Raman SSCrazy Points: 2520 More actions · Answer 5

I will look into that, but I do need a correct count of the files that the for each loop has processed - based on which certain notification needs to happen.

Thanks

Karthika

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

That would lean me more toward the variable. Each time you loop, increment the variable (instead of just setting it to 1). When you leave the loop container, your variable will have counted the number of files processed.

I tend to try to avoid the script component if I can. Sometimes it seems like an easy solution, but it tends to add complication that is best avoided.