控制流如何處理錯誤

    

目前創(chuàng)新互聯(lián)已為1000多家的企業(yè)提供了網(wǎng)站建設(shè)、域名、虛擬主機、網(wǎng)站托管、企業(yè)網(wǎng)站設(shè)計、安定網(wǎng)站維護等服務(wù)，公司將堅持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

在Package的執(zhí)行過程中，如果在Data Flow中出現(xiàn)Error，那么Data Flow component能夠?qū)㈠e誤行輸出，只需要在組件的ErrorOutput中進行簡單地配置，參考《Data  Flow的Error Output》。相比Data Flow，Control Flow對OnError事件的處理更加復(fù)雜和精細，主要需要考慮到以下5個方面： 

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

• 2，OnError事件的 Event handler能夠捕獲Task或Container出現(xiàn)OnError事件，并對Error進行處理

• 3，Package的Execution Result 和 progress message 對于Error的處理

• 4，屬性FailPackageOnFailure 和 FailParentOnFailure 控制Error向上傳遞

• 5，OnError事件能夠向父組件傳遞，類似冒泡

一，默認情況下，當container 出現(xiàn)錯誤時，package執(zhí)行失敗

二，屬性MaximumErrorCount

屬性MaximumErrorCount Specifies the maximum number of errors before the executable fails，即指定Executable能夠容納的Error 數(shù)量，當達到屬性MaximumErrorCount設(shè)置的上限值時，Executable執(zhí)行失敗，拋出Error。默認值是1，只要發(fā)生Error，組件就會Fail。

設(shè)置Execute SQL Task的屬性MaximumErrorCount，沒有效果，這個屬性對Container 或 Package 不起作用。

1，設(shè)置Container的屬性MaximumErrorCount=2，其子Task發(fā)生一個錯誤，執(zhí)行情況如下圖所示

子Task（Execute SQL Task）執(zhí)行失敗，其父Container執(zhí)行成功，由于該Container 和 下游組件的優(yōu)先約束是Success，因此package繼續(xù)執(zhí)行下游的組件。

在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 最終的執(zhí)行結(jié)果是

失敗的原因是Error會繼續(xù)向父組件傳遞，直到傳遞到Root Level（Package），而Package的MaximumErrorCount=1.

2，修改Package的屬性，將Package的屬性MaximumErrorCount=2，查看執(zhí)行結(jié)果

 

在Progress Tab中查看執(zhí)行過程

[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：如果設(shè)置為True，那么只要單個task失敗，則整個package失敗。默認值是False

FailParentOnFailure：如果設(shè)置為True，那么只要單個Task失敗，則該Task的上層組件將將失敗，Task的Parent組件是Container 或 Package。默認值是False。

在發(fā)生錯誤的Task上，這兩個屬性都是false，通過執(zhí)行情況來看，這兩個屬性沒有發(fā)揮任何作用。

四，OnError事件的Event handler

在一個OnError Event處理程序中，如果將Propagate屬性設(shè)置為False，那么不需要修改Parent container的MaximumErrorCount屬性，就能保證在發(fā)生錯誤后包可以繼續(xù)運行。詳細請閱讀《Event的Propagate》。

 1，為Container下的Execute SQL Task創(chuàng)建OnError Event handler

2，將OnError的Event handler的系統(tǒng)變量 Propagate設(shè)置為False

3，查看package的執(zhí)行結(jié)果

 

從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中，查看執(zhí)行的結(jié)果，Satus是Succeeded，Error messages中顯示錯誤的信息。

五，結(jié)論

Control Flow中發(fā)生的任何錯誤，都會被SSIS Engine捕獲；不管如何設(shè)置Task或Container的屬性，只要發(fā)生錯誤，就會有Error Message產(chǎn)生。

Error 能夠向上傳遞，當Error被OnError事件的 Event handler捕獲，并且Propagate設(shè)置為False時，Error停止傳遞。

Package的Execution Result 和 Package中是否出現(xiàn)Error 無關(guān)。組件拋出Error，Package仍然可能執(zhí)行成功，只不過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.

標題名稱：控制流如何處理錯誤
鏈接地址：http://bm7419.com/article38/jcsssp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護、網(wǎng)站導(dǎo)航、電子商務(wù)、搜索引擎優(yōu)化、外貿(mào)建站、網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)