Foreach loop error handling behavior

Question

I have a foreach loop container that loops over some files in a directory. There are a TON of operations that need to be done on each file. There are any number of places where a failure is a show stopper and the end user gets an email notification. The thing is if any one particular file fails I want the system to continue processing the rest of the files. I managed to do this by setting the system variable propogate to false. While that makes the loop process the rest of the files, the problem is the loop wants to make more than one pass at processing a failed file. It wants to make two tries actually. How do I make the loop stop at only one attempt at file processing?

Answer 1

One flow that I used was to copy the file to a "Success" folder if the file did what it was supposed to and a "Failure" folder if the file didn't.  There were several ways that the file could fail.  Then it went to retrieve the next file.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

Hi Falcon,

What you can do is, Instead of marking as propogate to false, try using the script task , inside the script task what you have to do is.

1. Perform the checks for the file which you have taken.

2. If the file is valid, then continue processing the file.

3. if the file is invalid, then move the file to seperate directory which maintains list of files which are invalid.

By this way you continue processing entire files in the specified directory.

Regards

Naveen

Answer 3

No dice on either of these techniques. If a file blows up it wants to try again. It will take the error file and copy it out but then for some strange reason it will try to look for the moved file. Of course it's not there so I get a file not found message. Why does it try to look twice?!

Answer 4

You know, if the ForLoop works by creating an initial collection of file names in the folder, just deleting or moving the file won't stop the loop for looking for a deleted file. You have to find someway to remove the reference to the file in the forloop itself....

Answer 5

I would suggest go with two flows in For Each loop first do the checks if not possible use the same mechanism which you are using to load file and use error outputs at relevent stages , with the help of error outputs update the flags ro counts on the basis of flags decide whether you have to process the file or not.

Actually the above process depends upon the type of errors as well, what type of errors you are expecting Is the no of columns changing of a file or headers are getting changed for this check you have to use script task to validate such things. 

Then you can move the files to the respective folder as rejected or processed.

Thanks

Sumit

Answer 6

Actually as it turns out it's not the loop at all. Something is making the OnError fire twice. Going to start a new thread tomorrow.