Obscure and not straight forward
This is one of those things I can never remember because it doesn’t come up that often. This is usually only a case where you are trying to introduce fault tolerance into a job that has to process something for many of something. In my case the loop can fail, but it cannot kill the whole job – it should just log failure and move on to the next element of the loop.
Credit where credit is due
This is where I learned my answer, I just wanted to summarize it here for safe keeping: https://www.mssqltips.com/sqlservertip/3575/continue-a-foreach-loop-after-an-error-in-a-sql-server-integration-services-package/
In order to suppress errors in a loop you need to do two things:
- Setup an event handler for OnError for the loop container specifically
- Set the “Propagation” variable for this event to “False”
- Although not required, it is recommend you log your error here
- Change the properties of the loop container for the following variables:
- FailPackageOnFailure: False
- FailParentOnFailure: True
- ForceExecutionResult: Success
It seems that you cannot just use one piece of this, you need all of it to work together. For example if you leave “ForceExecutionResult” to “None” the error will still propagate. I find this very confusing which is why it is hard to remember.