Continue a Foreach loop after an error in a SQL Server Integration Services package

Problem

I have a SQL Server Integration Services (SSIS) package with a Foreach Loop container. Inside that container I have a task that sometimes can fail. If it fails, the loop should just continue, skip the current step and go to the next iteration. Is this possible in SSIS?

Solution

This tip will describe how we can implement such error handling in a Foreach
loop within a SQL Server Integration Services Package. Two solutions will be presented: one using the
ForceExecutionResult and MaximumErrorCount properties and one using the
Propagate system variable.

SSIS Package Test Set-up

In this tip weíll use a simple package with the following control flow:

The Foreach container loops over a fixed set of numbers, using the Foreach Item Enumerator.

At each iteration, the current value will be written to an integer variable. In the first Execute SQL Task, that variable will be used as a parameter in the following SQL statement:

The task will wait 5 seconds in each iteration, so that the looping is more apparent while debugging the package in Visual Studio. As you may have noticed, the third item of the set is the number zero, which will make the SQL statement fail with a divide by zero error. The goal of this tip is to make sure that the loop will do all 6 iterations of the loop.

The last Execute SQL Task is just a dummy task that doesnít really do anything. It is connected to the first Execute SQL Task with an OnFailure constraint. This is done to study the effects of the solutions were going to implement in this tip.

When the package is executed without any changes, the first task will fail and the second task will be executed:

Notice that also the Foreach loop container fails (and the package as well), despite all tasks and containers having the properties
FailPackageOnFailure and FailParentOnFailure are set to False. These properties donít seem to have any effect at all, so we wonít bother with them in this tip.

ForceExecutionResult and MaximumErrorCount Options in SSIS

Letís start the first solution by setting the task property ForceExecutionResult to Success.

This property simply tells the task to succeed, no matter what it encounters. When we run the package, we get the following result:

The task itself didnít fail, but everything else still fails. The Foreach loop container did not continue the loop as we wanted. To figure out why, we need to take a look at the logging.

There we can clearly see the container and the package failed because the maximum amount of errors was reached (even though the property FailParentOnFailure is set to false everywhere). This is because errors are propagated to higher levels in the package, which we'll examine in more detail in the next section.

The default value of the MaximumErrorCount property is 1. If we change this property on the Foreach loop container to 0 Ė which basically means to ignore all errors Ė the following result is achieved:

In the logging we can clearly see that all iterations were performed.

However, the package still fails because the maximum amount of errors was reached. To avoid failure all together, the
MaximumErrorCount on the package should also be changed.

Using the combination of ForceExecutionResult and MaximumErrorCount we can continue the loop when an error occurs. However, this makes the package and the container insensitive to other errors, which is not an ideal scenario. Arguably, you donít even need the ForceExecutionResult property, you can just set MaximumErrorCount to 0 everywhere, but thatís not a good idea when it comes to decent error handling. Also notice that if you set ForceExecutionResult to Success, the OnFailure path is never called and the second Execute SQL Task is never executed.

The Propagate Variable in Integration Services

The second solution is a far more elegant solution to deal with errors in a loop. The problem with the first solution is that errors ďbubble upĒ from the failing task to the higher levels (containers) right until the package level. When you check out the logging of SSIS packages, itís possible that you see the same error message for each level in the package. This is because the error propagates through each level and each time a log message is sent. However, the propagation of the error can be stopped at the task level.

To do this, you need to add an error event handler to the first Execute SQL Task. You can do this by selecting the task and by going to the event handlers tab.

Click on the link to create the event handler. You can keep it empty. Go to the Variables pane and click on the
Grid Options.

In the dialog, enable the system variables.

Look for the Propagate variable and set its value to False.

This will stop errors from bubbling up to higher levels in the package. As you can see, the container and the package succeed, while the first Execute SQL Task fails and the second task is executed.

When we look at the logging, we can verify all iterations were executed.

The third iteration still failed and an error is logged, it just didnít crash the rest of the package.

Note that you could also put the second Execute SQL Task in the event handler, instead of using it in the control flow with the OnFailure constraint.

Conclusion

In this tip we presented two options to continue a loop in SQL Server Integration Services when an error occurs. You can either make the package insensitive to errors by using the properties MaximumErrorCount and ForceExecutionResult, or you could stop the propagation of errors to higher levels in the package by creating an empty event handler and setting the Propagate system variable to False. The last option is the preferred option for robust error handling.

Next Steps

If you want to see the magic yourself, download the test package
here.

When working with parent-child packages, the solution with the Propagate variable doesnít work. SQL Server MVP Joost
van Rossum has
a great blog post on how you can solve this.

I tested it out and it seems validation errors aren't caught by the mechanism described in this tip. They ignore the propagate variable and they cause the package to fail. Maybe you can submit a bug on feedback.azure.com/forums/908035-sql-server/?

The only work around I can think of right now means doing some pre-validation in a script task, where you have absolute control over what fails and what doesn't.

I also had issues using the data flow and they are many examples online showing data flow. Only way I could make it work is set the MaximunErrorCount = 0 on both the data flow and for each loop.

Maybe is the type of error within data flow that is cuasing it not to work or the SSIS version but I'm not sure.

I would like to make it work with the propagate flag.

pkg set up

I have a execute sql that returns a record set

a for each loop that loops thru the record set

within the for each loop there is a data flow task which is querying various sql db. the connection string is built in the expression so is dynamic and is part of the record set return from the first execute sql task

the data flow sometimes errors on the validation as the sql statement maybe invalid that is passed in from the record set but I want to continue thru the rest of the record set and the only way I could do this is by setting the maximunErrorCount to 0 on both data flow task and for each loop

any suggetions on getting the propagate flag to work? other then the propagate flag = false is there any other properties that need to be set or changed?

I tried it out and it worked just fine, as explained in the tip. I dowloaded the sample package (first link in the Next Steps section) and took it from there.

I put the original loop in a for loop which I executed 3 times. The for loop was then placed in a sequence container. I didn't change any setting in the package (which has of course the variable Propagate set to False in the OnError event handler of the SQL Task), I just added a counter variable for my for loop.

The package executed as expected: 18 executions of the SQL Task, of which 3 failed.

This Does not seem to work when doing a loop in a loop situation; even if i set the inner loop to max error count 0, add the propagate solution up, my outer loop also fails .. looks like it actually propagates up;

If i set the outer loop's max error to 0, it works, but this will let through ALL errors, which i don't want to.

I tested it and it indeed fails when just executing the container.My guess is because this doesn't call the OnError event handler (event handlers are not used when just executing a task/container?), so the Propagate variable can't take effect.

This behaviour described does only seem to work when you execute the complete package (i.e. in Visual Studio). When you execute the foreach Loop Container it does not show this behaviour and it fails. So for testing your package you need to execute the whole package and not only the foreach Lopp Container.

Maybe you know of a setting which will show the same behaviour during debugging inside the package as opposed to execute the whole package?

Hi Thanks for great post ,
I have used for loop instead of for each loop , Once error occurs at first task its navigating to failure task and executed successfully but iteration not continuing .. I have set propagate to fail. No idea where i am doing mistake can you please help me.
My for loop expressions

So now the sequence container has ForceExecutionResult set to Success?

Nope, I let the ExecutionResult default. (I trap all the errors, set my flag then stop error propogation). If there is some other error, that would be unusual and I'd like the package to fail. Shouldn't really happen though.

Sorry about the name mixup, Koen. (Ik heb veel Kees's gekend maar weinig Koen's). Actually the expression in step 5 does not do what I wanted it to. I had to change it to a conditional constraint after the sequence container. There, I check ErrorOccurred, log the error and fail the package.

Thanks Kees! I actually need something like this with something extra:

I use a Sequence Container to group a bunch of related Execute Package tasks. There are no dependencies between the tasks so I have no connection contraints within the container: they execute in parallel.

What I want to do is allow as many of the tasks to complete as possible but still know if one or more of the Exec Pkg tasks failed (each records its own success or failure in an audit table for analysis). Here's what I did:

1. Define a variable "ErrorOccurred" with a default value of False.

2. add an error handler event for the sequence container.

3. in the error handler event add an Expression task (SQL 2012).

4. In the Expression task set the ErrorOccurred variable to True.

5. In the Sequence container, define an expression for the ForceExecutionResult property to:

@[User::ErrorOccurred] ? 1 : 0

With that in place, I added two script tasks to the container to simulate the scenario. Both just set a Failure response and return, but I added

MessageBox.Show("Hit OK when ready");

line to the second one to delay its execution.

Works like a charm!

Just wondering: do you see pitfalls with this approach I may have missed?

I too prefer the Event Handler approach. I go one step further and compute the Propogate System variable, setting it to True or False depending on whether the error is "acceptable" or not. Basically check the error code and set the Propogate variable accordingly.

I use ForEach Loop container a lot and I do two things for the process to continue regardless of errors: set MaximumErrorCount = 0 and add an Execute SQL task with the logging I'd like to have while enabling event handler on error. Works like a charm!

I discovered that Propagate flag a while back when struggling with this same problem but I keep forgetting about it since it's really buried. You not only reminded me about it but also clearly explained the whole process of error propogation and management. Just wanted to say thanks!