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/

My summary

In order to suppress errors in a loop you need to do two things:

1. Setup an event handler for OnError for the loop container specifically
   1. Set the “Propagation” variable for this event to “False”
   2. Although not required, it is recommend you log your error here
2. Change the properties of the loop container for the following variables:
   1. FailPackageOnFailure: False
   2. FailParentOnFailure: True
   3. 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.