SSIS - handling errors within a for-each loop container

Question

Post reply

SSIS - handling errors within a for-each loop container

jeff_sherian

Mr or Mrs. 500

Points: 553
More actions
February 18, 2014 at 10:12 am

#283805

Hello,
I have an SSIS package with a for-each loop container. The loop queries records from a database, and for each record returned executes a number of tasks.
Within the for-each loop are a few sequence containers, and all tasks are within one of these containers. There are no 'Failure' constraints between any of the containers or tasks. Each container and task has MaximumErrorCount set to zero, and I also have an OnError event handler at the package level that writes error details to a table.
When an error occurs within one of the tasks, it is successfully recording the error details. In addition, the package flow continues after the error - this is by design, as we don't want any errors that occur for a given record within the loop to affect successive records.
However, when package flow continues after the error, it is continuing along to the next container within the loop (for the same loop record that generated the error). What I want it to do is stop processing the current loop record, and restart the loop for the next record.
Any help would be greatly appreciated - please let me know if you need more information. Thanks in advance!

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

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

jeff_sherian Mr or Mrs. 500 Points: 553 More actions · Answer 1

I think that I've answered my own question.

What I've done is:

- Create a new @continue Boolean variable at the package level

- Added a new Script Task at the start of the loop. The only thing that this task does is set @continue to true

- Within the package-level OnError event handler, added a new Script Task that will set @continue to false

- Changed all of the Precedence Constraints between the sequence containers to use 'Expression and Constraint'. They all check for Value=Success AND the Expression (@Continue == true)

This seems to be working, but I will update this post with any additional changes that may be required.

sh33dz SSC Enthusiast Points: 164 More actions · Answer 2

Hi Jeff,

I tried to do a similar thing to what you mentioned above, but the task fails and so does the loop, instead of starting again on the next iteration.

1)I created a boolean variable in the package.

2) Created a script task at the beginning of the Loop and set the variable to TRUE.

3) Created a script task in the event handler on the package and set this to false.

4) set the precedence constraints to expression and constraint but it doesnt seem to work.

jeff_sherian Mr or Mrs. 500 Points: 553 More actions · Answer 3

For each task and sequence container within the loop, one other thing that you will need to do is set the MaximumErrorCount value to 0 (I believe it defaults to 1, which is what causes the entire package to fail in the event of an exception).

Let me know if this helps.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

My question would be, why does this require a RBAR process? What is it that you're doing to "each row" that requires single row processing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sh33dz SSC Enthusiast Points: 164 More actions · Answer 5

[SOLVED]

Thanks for the prompt reply.

I have a number of folders each with multiple files within them. The files need to be imported on a per folder basis, therefore if one file failed, i needed the loop to rollback and go to the next folder to process the files within it and so forth.

I have a master package which calls the child packages which then process each file within the folder due to the structure of the flat files being different.

I have solved this within the FEL, by putting all the child packages within a sequence container and creating an event handler to resolve this.

🙂

gireesh585 SSC Journeyman Points: 75 More actions · Answer 6

Thanks sh33dz.!

your reply worked for me. I have similar requirement.. I want to bypass a bad file and keep moving to the next file in the same folder.

I followed the same trick. Placed a sequence container in For Each Loop Container and put my logic (Script task).

Thanks Again.!