Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (20)   |   Related Tips: More > Integration Services Error Handling

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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:

Test Package

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

Loop configuration

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:

DECLARE @denom INT = ?;
SELECT 1 / @denom;
WAITFOR DELAY '00:00:05'; -- wait 5 seconds so looping is better visible

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:

Default behaviour

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.

Setting the property

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

Success?

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.

Too much errors...

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:

Success! Or is it?

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

6 iterations were done

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.

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.

Selecting grid options in the variables pane

In the dialog, enable the system variables.

Enabling system variables

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

Disabling error propagation

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.

Success at last

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

Success at last - logging

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.
  • You can find more SQL Server Integration Services tips here.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, November 30, 2017 - 2:19:32 AM - Koen Verbeeck Back To Top

 

Hi Michael,

the current scope needs to be in an event handler. You won't see the variable when you're in the control or data flow.


Wednesday, November 29, 2017 - 4:15:08 PM - Michael A. Robinson Back To Top

 When I click the show system variables button, I don't see the propagate variable.

 


Monday, July 10, 2017 - 4:29:22 AM - Koen Verbeeck Back To Top

 

Hi Dani,

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.


Friday, July 07, 2017 - 9:06:05 AM - Dani Back To Top

 

Hi,

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.

Any suggestions? 

Thanks


Monday, June 05, 2017 - 6:23:07 PM - Steven J Neumersky Back To Top

 Propagate has to be one of the most under-utilized functionalities in SSIS, and it has always bothered me that:

 

1. There are no OBVIOUS BLARING indicators that an event handler is present in the package (probably not an easy thing to do since there are 12 types of them?).

2. The propagate functionality is not the most obvious functionality to access.

 

.


Tuesday, February 23, 2016 - 5:22:52 PM - Koen Verbeeck Back To Top

Hi Dim,

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.

So I'm not really sure you can solve this issue, unfortunately.

Koen


Wednesday, February 10, 2016 - 11:49:10 AM - Dim Back To Top

Hi Koen,

maybe to help others from poking around for hours:

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?

Thanks for the great article anyway.

Dim


Friday, February 05, 2016 - 11:36:08 AM - Lee Back To Top

 

 THANKS!  I used Solution #2 and it worked great!

 


Tuesday, January 05, 2016 - 9:48:46 AM - Koen Verbeeck Back To Top

Hi Abdul,

did you create the empty event handler for OnFailure on the failing task?
What are the expressions for the for loop?

Regards,
Koen


Sunday, December 27, 2015 - 7:28:38 AM - abdul Back To Top
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

Thursday, August 13, 2015 - 10:01:08 AM - Gerald Britton Back To Top

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.



Wednesday, August 12, 2015 - 4:00:35 PM - Koen Verbeeck Back To Top

@Gerald,

no problem :)

So now the sequence container has ForceExecutionResult set to Success?


Wednesday, August 12, 2015 - 9:32:34 AM - Gerald Britton Back To Top

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.


Tuesday, August 11, 2015 - 4:11:27 PM - Koen Verbeeck Back To Top

@Gerald,

the name is Koen, but I'll let that one slip ;)

The set-up seems fine I guess. It's hard to imagine when just reading the text and not having an actual package before you.
I guess the error event handler logs to the audit table as well?

Does the expression in step 5 prevent the sequence container from terminating prematurely when one of the tasks fails?
If yes, that's a very interesting set-up. 


Tuesday, August 11, 2015 - 11:37:49 AM - Gerald Britton Back To Top

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?


Thursday, April 23, 2015 - 10:07:57 AM - Gerald Britton Back To Top

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.


Thursday, April 23, 2015 - 7:35:39 AM - Greg Robidoux Back To Top

Shah, the file is available now for download.  Please try the link again.


Thursday, April 23, 2015 - 7:16:53 AM - Shah Back To Top

Thanks for sharing and its good post .

I want to access this code but some how, it seems that, files have been removed from below link.

http://www.mssqltips.com/tipimages2/3575_ContinueLoop.zip

Can I access this test package please?

 

 


Tuesday, April 14, 2015 - 1:28:38 AM - Julia Back To Top

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!


Wednesday, April 08, 2015 - 5:14:06 PM - Dave Back To Top

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!


Learn more about SQL Server tools