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

By:   |   Comments (39)   |   Related: 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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, August 16, 2021 - 10:52:38 AM - Koen Verbeeck Back To Top (89131)
Hi Vinay,

so the package fails, but at the same time continues?
Did you change the value of the Propagate variable or did you leave everything to the default?


Friday, August 13, 2021 - 10:29:45 AM - Vinay Back To Top (89125)

My expected result is - Fail the package when any task fails.
My package structure is - Package > 1 Sequence container > 1 FOR Loop > 1 FOR Loop
My Problem is - if any task fails in the innermost FOR Loop, the package fails with Error but the loop continues & the OnError handler defined OnPackage is also not triggered

Can you please explain this behavior?

Friday, April 23, 2021 - 4:37:48 AM - Koen Verbeeck Back To Top (88594)
Hi Brent,

did you assign the event handler to the Execute SQL Task? An error is an error for SSIS, I don't think it makes a difference which error you throw in the stored proc. You can always download the sample package to test it out and see if it works on your machine.


Friday, April 16, 2021 - 1:21:18 PM - Brent Back To Top (88554)
I couldn't get this to work. I set up the error handler, got it to show the system variables, clicked on grid, set the propagate variable to false, but the error is still propagating up. I am executing a stored procedure in a sql task. Inside the stored procedure is a nested stored procedure that does a quality check. If it fails the quality check, it raises an error and sends an email (all within the stored proc). However, I want it to go on to the next client instead of failing the package. Should it work for all errors? Do I need to raise a specific error in the stored proc? Any help would be appreciated.

Friday, March 5, 2021 - 2:17:03 AM - Koen Verbeeck Back To Top (88341)
Hi Kiran,

if you have 5 different files (each it's own DFT), then you don't need a for each loop. You can just start the package and each DFT will run in parallel at the same time. When one crashes, the other DFTs will continue until they're finished. Then the package will crash.

Or do you mean you have 5 types of files, and each type has multiple source files over which you loop? That's a different story.


Tuesday, March 2, 2021 - 11:43:35 PM - Kiran Back To Top (88323)
Is it possible to handle the below case with Propagate property?
If we have 5 files to process with SSIS for each loop and each has different DFT created. If we dont have any issues with the files all the files should process and package should be successful , But if we have any one of the file failed with some issue, 4 files should process and one DFT should fail and the over all package should fail , so that we will know some issue in one of the file.

Is this possible with SSIS please?

Thursday, September 12, 2019 - 5:31:13 PM - Dillon Nugent Back To Top (82390)

Thank you Koen, 

Very helpful article and solved the issue we were running into. One quick note, if you use BIML Studio, it's quite easy to add an event handler through the BIML Designer view, however setting the System:Propgate variable to false is non-intuitive. The easiest method I've found is to then switch to the 'View Biml' view and then add the XML directly to the appropriate package so you end up with something that:

 <Event Name="OnError" EventType="OnError">
     <Variable DataType="Boolean" Name="Propagate" Namespace="System">false</Variable>

Thursday, August 8, 2019 - 6:35:53 AM - Mattias Wärdell Back To Top (82000)


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 (79302)

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 (79300)

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 (79297)

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 (79137)

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 (79134)

 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 (79133)

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 (79132)

 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 5, 2018 - 7:11:36 AM - Koen Verbeeck Back To Top (75102)

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

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 1, 2018 - 10:40:24 PM - SSIS_Rookie Back To Top (75085)

 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 8, 2018 - 4:10:49 AM - Koen Verbeeck Back To Top (74889)

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 (74315)

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 (73452)


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 (73432)

 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 (59177)


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 7, 2017 - 9:06:05 AM - Dani Back To Top (59011)



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 5, 2017 - 6:23:07 PM - Steven J Neumersky Back To Top (56802)

 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 (40761)

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 (40641)

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 5, 2016 - 11:36:08 AM - Lee Back To Top (40608)


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


Tuesday, January 5, 2016 - 9:48:46 AM - Koen Verbeeck Back To Top (40357)

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 (40312)
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 (38441)

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 (38435)


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 (38432)

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 (38423)


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 (38421)

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 (37011)

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 (37008)

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 (37007)

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 (36923)

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 8, 2015 - 5:14:06 PM - Dave Back To Top (36866)

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!

get free sql tips
agree to terms