Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          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:   |   Last Updated: 2015-04-08   |   Comments (32)   |   Related Tips: More > Integration Services Error Handling


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?


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:


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.


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 Updated: 2015-04-08

get scripts

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    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Thursday, August 08, 2019 - 6:35:53 AM - Mattias Wärdell Back To Top


Thank you for this great descrioption for solution, worked like a charm.

Have a great day,


Thursday, March 14, 2019 - 6:38:10 PM - Anne Cao Back To Top

I am in the event handler for errror, then go to variables and system variables.

I wonder if it is only available to certain task items?


Thursday, March 14, 2019 - 5:01:31 PM - Koen Verbeeck Back To Top

Hi Anne,

you need to be in an error event handler, then go to variables.

Thursday, March 14, 2019 - 2:19:26 PM - Anne Cao Back To Top

Thanks for the tips!

I tried to show system variables, but I cannot see  a one named Propagate.

Does it has to be shown in certain context?


Wednesday, February 27, 2019 - 3:02:14 AM - Koen Verbeeck Back To Top

Okay, here's what I would do. You can create a for loop where you check a certain condition: either you have looped X times or the connection was unsuccessful. Then you can use a conditional precedence constraint (where you check if the check has failed or not) to the send email task. For this task, you can set the ForceExecutionResult to Failure so that it will always fail (and thus fail the package).

There are some issue with this approach though:

* you are masking the true error in the logging of the package. The package fails because you tell it to, not because the connection failed.

* if the send mail task really fails, it'll be hard to tell.

An alternative is to do everything in a script task. You can check the connection there, send the email and decide if you want to set the execution result to success or failure.

Tuesday, February 26, 2019 - 12:59:16 PM - LeoG Back To Top

 The question is not about loops. I'm traping the error for connection but still need the package to fail after successfullemail send.

Tuesday, February 26, 2019 - 11:13:52 AM - Koen Verbeeck Back To Top

Hi LeoG,

what about a for loop instead of a foreach loop?
In my opinion, a script task is easier and more flexible for this type of scenarios.


Tuesday, February 26, 2019 - 10:04:33 AM - LeoG Back To Top

 Hi. I used the second method and set Propagate flag to false but I need to make the package fail. In my scenario, I have a loop that checks for a connection to a DB. If not successful then package waits for 5 minutes and tries again for a number of X times. After all X times exceeded, I'm sending an email and want the package to have a failed status. Any suggestion?

I was thinking to use script task to force failure. Will it work?

Monday, February 05, 2018 - 7:11:36 AM - Koen Verbeeck Back To Top

Hi SSIS_Rookie,

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.


Thursday, February 01, 2018 - 10:40:24 PM - SSIS_Rookie Back To Top

 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?


Monday, January 08, 2018 - 4:10:49 AM - Koen Verbeeck Back To Top

Hi Pendleton,

I tested it with a data flow and the set-up still works.

Did you set the Propagate variable in the event handler?

Thursday, December 21, 2017 - 4:13:35 PM - D. Pendleton Back To Top

It would appear that the Propagate method does not work when the tasks are Data Flows rather than Execute SQL Tasks. 


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



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? 


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.


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.


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?


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


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


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.


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