控制流如何處理錯誤

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