Understanding MaximumErrorCount

Learning about how SSIS’s MaximumErrorCount property works can be challenging. There’s not much documentation describing this property and the behavior it controls. Here’s my attempt to help remedy this.


MaximumErrorCount

When the number of errors occurring inside a container during execution reaches its MaximumErrorCount, the container’s ExecutionResult is changed to Failure if it is not already set to that state. A value of zero sets the error count threshold to infinity, disabling this functionality.

Errors and execution result are distinct concepts. A container’s internal logic may set its result independent of whether errors have been raised. It’s possible for a container to return success and yet have raised errors or to report failure without having fired any errors. The behavior controlled by MaximumErrorCount bridges between these two concepts, overriding the container’s internal logic to coerce a failed result when the specified number of errors occurs.

MaximumErrorCount’s triggering of a failure result does not terminate the container’s execution. However, the state of failure may be used to influence control flow via precedence constraints. Also, in the case of For and Foreach Loop containers, a failed ExecutionResult disables further iteration.

Some documentation asserts that MaximumErrorCount defines the number of errors that can occur before a container stops running. Based on extensive testing using Microsoft SQL Server Integration Services Designer Version 12.0.2344.23 where I was unable to unable to reproduce MaximumErrorCount halting a container’s execution, I believe this documentation to be inaccurate.

Propagation

A single Script Task’s raising of one error causes a hierarchy of nested containers to report alternating ExecutionResults due to differing MaximumErrorCount thresholds.By default, errors bubble up from child to parent containers. Within a package, this propagation may be disabled for a particular container by having its OnError event handler set the system variable Propagate to false. Note that this variable only affects propagation inside a package. Even when set to false, errors raised in a child package are still passed to the parent package.

Each container in a container hierarchy makes an independent determination of whether a propagated error causes its MaximumErrorCount threshold to be met. For example, an error bubbling up may cause a parent container to fail even though its child container reports success because the parent container’s MaximumErrorCount is set to a lower threshold.

2 thoughts on “Understanding MaximumErrorCount

  1. eric81

    I setup an basic Data Flow to import data to/from a SQL table with an primary key on target table. I executed a simple test where I inserted record in source which existed in target table already. ETL failed as expected. I then altered the MaxErrorCount to 10000000 yet my ETL still failed and still did not insert records in target. Am I missing something?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *