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.

8 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
  2. pl80

    I’ve set the MaxErrorCount on a Foreach Loop Container to 0, which indeed allowed the loop and the package to continue. Nevertheless, the package reported a failure. What I’ve had to get done was to set up an OnTaskFailure event handler and (importantly) set the Propagate variable to true. IS THERE A SIMPLER WAY to make the package report success? Tx.

    Reply
    1. SR08

      I have hit this same problem but have read this is typical of SSIS that there is no way to promulgate package errors between child and parent packages.

      The only way to do this is set the MaxErrorCount=0 at the package level. This might not be feasible to do especially of you have logica which requires reporting on the error.

      Please can you confirm my findings?

      Reply
      1. Ben Gribaudo Post author

        I just did a quick test. Built a package with a task that returns an error, then referenced this package from another package. The parent package could tell that the child package had an error and followed its (that is, the parent’s) max error count setting to determine whether that package (the parent) should fail. Does that help?

        Reply
  3. Prashanth S

    @Ben Gribaudo,

    After i spent hours of manual trials..Here’s a simple trick below!

    As you said, the parent package could tell that the child package had an error(even if “propagate error” is false for a task in child package)…You can still make the parent package continue its progress by setting “Maximum error count” of “Execute package task” (which calls child) to 2 instead of default 1.

    Lemme know if this helps.

    Reply

Leave a Reply

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