SSIS 處理錯誤的方法

2023-04-23 11:51:12

Package在執行過程中，不可避免地會發生錯誤，如果處理錯誤？簡單粗暴的做法，是Package直接停止執行。對於一個成熟的ETL工具，這顯然不是唯一的錯誤處理方法。如果在資料流中出現錯誤，那麼資料流元件可以把錯誤行輸出，這隻需要在元件的ErrorOutput中進行簡單地配置。跟資料流相比，控制流中包含錯誤處理程式OnError，對錯誤事件的處理更加複雜和精細。

使用者可以通過MaximumErrorCount屬性和OnError事件處理程式來對錯誤進行控制：

Package本身、Task 和 Container具有屬性MaximumErrorCount，用於控制錯誤之前元件可以出錯的次數；
OnError事件處理程式能夠捕獲Task或容器中觸發的OnError事件，並對錯誤進行處理。

一，屬性MaximumErrorCount用法

屬性MaximumErrorCount 指定Task元件在失敗之前可以出錯的次數，當累積的錯誤達到屬性MaximumErrorCount的值時，Task執行失敗。該屬性的預設值是1，也就是，只要發生錯誤，元件就會失敗。

1，測試容器的MaximumErrorCount

設定容器的屬性MaximumErrorCount=2，其子Task發生一個錯誤，執行情況如下圖所示：

容器執行成功，子Task執行失敗，這是由於容器的MaximumErrorCount屬性是2，還沒有達到失敗的閾值，因此容器的執行結果是成功的。在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 最終的執行結果是

失敗的原因是錯誤會繼續向父元件傳遞，直到傳遞到Root Level（Package），而Package的MaximumErrorCount=1。

2，測試Package的屬性MaximumErrorCount

把Package的屬性MaximumErrorCount設定為2，把容器的屬性MaximumErrorCount設定1，檢視Package的執行結果

在Progress Tab中檢視執行過程：

[Execute SQL Task2] 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.

二，OnError事件的錯誤處理程式

在一個OnError 事件的處理程式中，如果把系統變數Propagate屬性設定為False，那麼不需要修改父容器的MaximumErrorCount屬性，就能保證在發生錯誤後，Package可以繼續執行。

1，測試系統變數 Propagate

為容器下的Execute SQL Task建立OnError事件處理程式，把OnError的事件處理程式中的系統變數 Propagate設定為False

檢視package的執行結果，雖然Execute SQL Task 2執行失敗，但是容器的執行結果是成功的，Package的執行結果也是成功的，如下圖所示：

從Progress中檢視到的錯誤訊息是：

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

2，從SSISDB中檢視錯誤

把Package部署到Integration Services Catalog中，檢視執行的結果，Satus是Succeeded，Error messages中顯示錯誤的資訊。

四，錯誤對執行結果和訊息的影響

控制流中發生的任何錯誤，都會被SSIS引擎捕獲；不管如何設定Task或Container的屬性，只要發生錯誤，就會產生錯誤訊息。

錯誤能夠向上傳遞，當錯誤被OnError事件處理程式捕獲時，通過設定系統變數Propagate為False時，可以停止錯誤向父級別傳遞。

Package的執行結果受到屬性的控制，元件丟擲錯誤，Package仍然可能執行成功，只不過progress中會記錄錯誤訊息。