控制流如何处理错误

目前创新互联已为1000多家的企业提供了网站建设、域名、虚拟主机、网站托管、企业网站设计、安定网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

在Package的执行过程中，如果在Data Flow中出现Error，那么Data Flow component能够将错误行输出，只需要在组件的ErrorOutput中进行简单地配置，参考《Data  Flow的Error Output》。相比Data Flow，Control Flow对OnError事件的处理更加复杂和精细，主要需要考虑到以下5个方面： 

• 1，在Control Flow中，Package本身，Task 和 Container具有属性 MaximumErrorCount

• 2，OnError事件的 Event handler能够捕获Task或Container出现OnError事件，并对Error进行处理

• 3，Package的Execution Result 和 progress message 对于Error的处理

• 4，属性FailPackageOnFailure 和 FailParentOnFailure 控制Error向上传递

• 5，OnError事件能够向父组件传递，类似冒泡

一，默认情况下，当container 出现错误时，package执行失败

二，属性MaximumErrorCount

属性MaximumErrorCount Specifies the maximum number of errors before the executable fails，即指定Executable能够容纳的Error 数量，当达到属性MaximumErrorCount设置的上限值时，Executable执行失败，抛出Error。默认值是1，只要发生Error，组件就会Fail。

设置Execute SQL Task的属性MaximumErrorCount，没有效果，这个属性对Container 或 Package 不起作用。

1，设置Container的属性MaximumErrorCount=2，其子Task发生一个错误，执行情况如下图所示

子Task（Execute SQL Task）执行失败，其父Container执行成功，由于该Container 和 下游组件的优先约束是Success，因此package继续执行下游的组件。

在Progress 选项卡中，SSIS 报出Warning信息

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS Package 最终的执行结果是

失败的原因是Error会继续向父组件传递，直到传递到Root Level（Package），而Package的MaximumErrorCount=1.

2，修改Package的属性，将Package的属性MaximumErrorCount=2，查看执行结果

在Progress Tab中查看执行过程

[Execute SQL Task] Error: Executing the query "insert into dbo.test_env
values(1,N'test_error')" failed with the following error: "An explicit value for the identity column in table 'dbo.test_env' can only be specified when a column list is used and IDENTITY_INSERT is ON.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

三，属性FailPackageOnFailure 和 FailParentOnFailure

FailPackageOnFailure：如果设置为True，那么只要单个task失败，则整个package失败。默认值是False

FailParentOnFailure：如果设置为True，那么只要单个Task失败，则该Task的上层组件将将失败，Task的Parent组件是Container 或 Package。默认值是False。

在发生错误的Task上，这两个属性都是false，通过执行情况来看，这两个属性没有发挥任何作用。

四，OnError事件的Event handler

在一个OnError Event处理程序中，如果将Propagate属性设置为False，那么不需要修改Parent container的MaximumErrorCount属性，就能保证在发生错误后包可以继续运行。详细请阅读《Event的Propagate》。

 1，为Container下的Execute SQL Task创建OnError Event handler

2，将OnError的Event handler的系统变量 Propagate设置为False

3，查看package的执行结果

从Progress中查看到的Error Msg是：

[Execute SQL Task] Error: Executing the query "insert into dbo.test_env
values(1,N'test_error')" failed with the following error: "An explicit value for the identity column in table 'dbo.test_env' can only be specified when a column list is used and IDENTITY_INSERT is ON.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

3，将Package部署到Integration Services Catalog中，查看执行的结果，Satus是Succeeded，Error messages中显示错误的信息。

五，结论

Control Flow中发生的任何错误，都会被SSIS Engine捕获；不管如何设置Task或Container的属性，只要发生错误，就会有Error Message产生。

Error 能够向上传递，当Error被OnError事件的 Event handler捕获，并且Propagate设置为False时，Error停止传递。

Package的Execution Result 和 Package中是否出现Error 无关。组件抛出Error，Package仍然可能执行成功，只不过progress中会记录Error message。

Appendix：

引用《Understanding 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 reproduce MaximumErrorCount halting a container’s execution, I believe this documentation to be inaccurate.

Propagation

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  or 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.