SQL Server Integration Services 2012初探(5)
上期專欄中，主要介紹了執行 SSIS 封裝的各種方式，並在執行時，或於 SQL Server Agent 作業設定執行封裝時，在組態設定所需的「記錄層次」。本期專欄繼續介紹如何透過 SSIS 2012 預設提供的報表，檢視封裝的執行狀況以及過往的歷程記錄，而這些報表所使用的資料會受「記錄層次」的影響。

執行 SSIS 封裝時，需要了解歷來與當下封裝的執行狀況，以確認移轉資料的正確性、效率、成功失敗的原因，且在事後可以追蹤分析，好除錯與調整資料轉換流程。SSIS 2008 R2 版本以前，對於封裝的執行紀錄只有啟動記錄與否的能力，但並沒有提供分析功能，且需要了解完整的記錄機制，才能啟動所需要的客製化記錄，否則不是記錄太多，就是不足。

SSIS 2012 版後，針對此一部分強化如下：
‧自動擷取封裝執行記錄
‧改良設定與存放記錄資料的機制
‧內建的報表 -- 「Integration Services 儀表板」、「所有執行」、「所有驗證」、「所有作業」、「所有連接」等。在前述報表內，還可以點選連結，檢視效能、錯誤訊息等相關子報表。
‧當執行封裝時，設定要記錄「詳細資料」，則資料流程工作的執行記錄會呈現目的地寫入的記錄筆數。

透過 SQL Server Management Studio 的「物件總管」視窗內，滑鼠右鍵點選「Integration Services 目錄」節點下之SSISDB、資料夾、各個專案、各個封裝等節點，透過快捷選單的選項，都可以叫出內建報表。以下說明如何使用這些 SSIS 2012 內建的報表：

封裝執行報表
首先是「Integration Services 儀表板」，它會呈現過去 24 小時在伺服器所累積的封裝執行狀況，其叫出報表的畫面如圖 1 所示：


▲ 圖1 透過「Integration Services 儀表板」檢視 Integration Services 過去 24 小時至當下的執行狀況。

「Integration Services 儀表板」主要呈現的是概觀資料，如 24 小時內執行封裝成功失敗的總次數，曾使用的資料連接、那些封裝成功失敗等，其內容與你在執行封裝時，所選擇「記錄」設定無關。後續說明的一些執行細節之報表內容，則會跟你在執行封裝時，所設定的記錄方式有關。
若起始執行的封裝內有呼叫其他封裝，以及各種執行與設定的細節，在儀表板不會呈現，需點選下方「封裝詳細資訊(過去 24 小時) 」表格內的「概觀」連結，跳至「概觀」報表檢視。如圖 2 所示：


▲ 圖2 透過 SSIS 2012 預設的報表觀察封裝執行的狀況
在概觀報表中，可檢視該次封裝執行的總時間、參數與屬性的設定，各個工作的執行結果、時間、工作名稱…等資訊。圖 2 呈現了筆者透過主封裝呼叫一堆子封裝的執行工作，點選某個執行子封裝的「執行路徑」連結，叫出的「訊息」報表可以呈現該子封裝的執行內容。若是父封裝呼叫子封裝，而子封裝內繼續呼叫子封裝，則「執行路徑」連結會呈現這整個呼叫鏈[註：在大型的 ETL 流程中，這些父/子封裝的呼叫鏈宛若迷宮。現今，剛看到 SSIS 2012 報表的呈現方式。或許，要靠時間與經驗來學習是否可以不加工，或是經由某些加工，讓我們可以快速找到問題的癥結。]。例如筆者透過父封裝「GenerateMultiGarbage」內的「執行封裝工作 2」呼叫子封裝「GarbageData」，該子封裝再以「執行封裝工作」呼叫子封裝「DataFlow」，則「執行路徑」呈現如下：

\GenerateMultiGarbage\執行封裝工作 2\GarbageData\執行封裝工作\DataFlow

若要檢視封裝與工作所發出的訊息，可以點選圖 1 下方「封裝詳細資訊(過去 24 小時) 」表格內的「所有訊息」連結，跳至「訊息」報表檢視。如圖 3 所示：


▲ 圖3 檢視封裝執行時所發出的各種訊息

在圖 3 的訊息報表中，若要檢視各物件所發出較為詳細的資訊，如圖 3 中寫入資料目的地 「0 個資料列」，告知該資料目的地處理的資料量，這需在執行封裝時，「記錄層次」選擇「詳細資訊」，如圖 4 所示：


▲ 圖4 執行封裝時，「記錄層次」會影響報表可用的資料

「記錄層次」選擇「詳細資訊」將會產生大量記錄，除非要解決問題，一般應不需要使用。
若要比較該封裝歷來的執行時間，可以點選下方「封裝詳細資訊(過去 24 小時) 」表格內的「執行效能」連結，跳至「執行效能」報表檢視。如圖 5 所示：


▲ 圖5 檢視執行封裝的效能資訊

圖5中間左方的趨勢圖與右方的表格可以檢視該封裝多次執行所耗的時間。若要如圖 5 下方「資料流程元件資訊」的表格呈現各子元件所耗的時間，則需要如圖 4 上方，在執行封裝時，其「記錄層次」需要選擇「效能」，否則該表格將沒有資料呈現。

前述點選儀表板下方「封裝詳細資訊(過去 24 小時) 」表格內的不同連結，都是呈現該封裝最後一次執行的相關資訊，某個封裝若在過去 24 小時內執行多次，「Integration Services 儀表板」的「封裝詳細資訊(過去 24 小時) 」表格內也只會出現一次。

而在封裝每次執行時，都會有一個「作業識別碼」，因此前述的各種報表中，都可以看到細節報表內的左上角「執行資訊」表格，其內標示著哪個封裝哪次執行的作業識別碼。透過該「作業識別碼」便能串起各記錄資訊。

以往，為了串起多個封裝的資料轉換流程，其個別子封裝每次執行與父封裝間的關係，會傳遞、記錄與檢視父封裝的「System::ExecutionInstanceGUID」公共變數，因為每個封裝每次執行，「System::ExecutionInstanceGUID」公共變數都會產生獨一無二的值。

SSIS 2012 RC0 版本之後，新增了「System::ServerExecutionID」封裝變數。為了記錄這個 「System::ServerExecutionID」封裝變數值，筆者故意在父封裝與子封裝都加入「指令碼工作」，並讀取這兩個變數，如圖 6 所示：


▲ 圖6 設定「指令碼工作」可用的封裝變數

在「指令碼工作」中，加入如下以 C# 寫的自訂記錄程式碼：

Dts.Log("子封裝 Garbage 的 ExecutionInstanceGUID：" + Dts.Variables["ExecutionInstanceGUID"].Value.ToString() +
" ServerExecutionID：" + Dts.Variables["ServerExecutionID"].Value.ToString(), 0, );

在「指令碼工作」中，可以透過 SSIS 預設提供的 Dts 物件之 Log 方法，將自訂的訊息寫入到封裝所設定的自訂記錄位置，其設定方式如圖 7 所示：


▲ 圖7 透過「指令碼工作」自訂執行封裝的記錄

透過主選單「SSIS」的「記錄」選項叫出「設定 SSIS 記錄」對話窗，啟動封裝的自訂記錄時，除了勾選封裝名稱(圖 7 為 GenerateMultiGarbage)前的確認方塊，指定「提供者類型」為「SQL Server 的 SSIS 記錄提供者」，並在「組態」設定「SQL Server 連接管理員」外，須滑鼠連續點選「指令碼工作」前的確認方塊，直至正常勾選，而非灰底勾選(代表繼承父容器的記錄設定)或未勾選(代表不要記錄該工作發出的訊息)，並在右方「提供者與記錄」頁籤勾選封裝所建立的「SQL Server 的 SSIS 記錄提供者」。切換到「詳細資料」頁籤後，勾選最下方的「ScriptTaskLogEntry」事件，則前述 C# 程式碼所寫的資料才會記錄到 SQL Server 執行個體資料表內。
依照預設，可以在「SQL Server 的 SSIS 記錄提供者」所指定的 SQL Server 執行個體與資料庫內，從 SSIS 自動建立的「sysssislog」資料表查詢實際執行的記錄結果，「ExecutionInstanceGUID」和「ServerEnecutionID」兩個變數值分別如下，父封裝的值：

父封裝 GenerateMultiGarbage 的 ExecutionInstanceGUID：{146C9E21-894B-4730-B45A-749DF5AE5528} ServerExecutionID：111

子封裝的值：

子封裝 Garbage 的 ExecutionInstanceGUID：{D973DB1A-76FA-42F8-B5FE-70B85F687DFE} ServerExecutionID：111

每個封裝執行時，會產生代表該次執行的 GUID，每次得到的值都會不同，不管是否在同一次父封裝呼叫子封裝執行流程中，這導致很難追蹤某個子封裝某次的執行記錄是被父封裝哪次執行所呼叫的，串不起某次資料轉換流程的封裝呼叫鏈，將很難除錯。

而ServerExecutionID變數則在同一次一起執行的父/子封裝流程都會得到相同的識別碼，因此可以藉由此識別碼串起 SSIS 2012 提供的制式記錄與前述的客製化記錄。但要提醒的是，若未部署到「Integration Services 目錄」便直接執行封裝，則「System::ServerExecutionID」封裝變數將回傳 0。

點選「Integration Services 儀表板」右上方「其他 Integration Services 報告」表格內的「所有執行」連結，叫出「所有執行」報表，如圖 8 所示：


▲ 圖8 呈現某個 SQL Server 執行個體上所有封裝執行的狀況

透過圖 8「所有執行」報表可以檢視 SSISDB 資料庫內，有記載的全部封裝執行記錄，透過每一筆識別碼記錄後的「概觀」、「檢視訊息」、「執行效能」等子報表連結，可以分析該次封裝執行流程的細節。

若要驗證封裝的設計與搭配環境變數是否正確，可以在物件總管視窗內，以滑鼠右鍵選擇某個 SSIS 專案或封裝節點，就可以在快捷選單中選擇「驗證」選項，啟動完驗證後，可以直接檢視呈現驗證結果的報表，或是選擇「Integration Services 儀表板」右上方的「所有驗證」連結，呈現如圖 9 的所有驗證報表：


▲ 圖9 檢視驗證報表設計與搭配環境的正確性

SSIS 服務執行的各種行為都是「作業」，除了先前探討的執行封裝外，部署專案、清空資料的保留週期作業、驗證…等，都是作業，當發生這些作業時，都會在「internal.operations」、「internal.executable_statistics」…等系統資料表內留下記錄，前述的執行和驗證封裝報表都是某種特定作業報表。點選「Integration Services 儀表板」右上方「其他 Integration Services 報告」表格內的「所有作業」連結，叫出「所有作業」報表，如圖 10 所示：


▲ 圖10 呈現歷來所有 SSIS 作業的執行資訊

從圖 10 的「作業類型」欄位可以分辨該筆記錄的識別碼是代表「驗證專案」、「驗證封裝」、「部屬專案」、「還原專案」、「執行封裝」、「保留週期作業步驟」…等不同類型的作業。
SSIS 2012 的作業資訊可以直接查詢「catalog.executions」、「catalog.operations」、「catalog.extended_operations_info」和「catalog.operations_messages」…等多個系統檢視，在下列網址的 Blog中，作者有稍微解釋這些檢視的用法：

http://sqlblog.com/blogs/jamie_thomson/archive/2011/07/16/ssis-logging-in-denali.aspx

該 Blog 的作者在 Codeplex 網站提供了他透過 SQL Server Reporting Services 開發的報表，呈現封裝執行的各項資訊，下載網址如下：

http://ssisreportingpack.codeplex.com/

直接下載並透過 SSDT(原 BIDS，在SQL Server 2012搭配Visual Studio 2010殼層，改名 SQL Server Data Tools)開啟該報表後，就可以學習並使用，如圖 11 所示：


▲ 圖11 Codeplex 網站上提供自訂報表，以分析 Integration Services 目錄內的封裝執行記錄

這些客製化報表做得很不錯，透過報表跳子報表可以檢視執行封裝的細節與效能資訊，最棒的是能夠部署到獨立的報表伺服器，並設定連接，以檢視不同的「Integration Services 目錄」之資料。
除了使用 SSIS 2012 提供的作業記錄資訊外，須注意的是如何清掉這些歷史記錄。本文最後稍微說明此一部分。

操作清除 Operations Cleanup
「Integration Services 目錄」內多種作業的狀態記錄，如上述「驗證專案」、「驗證封裝」、「部屬專案」、「還原專案」、執行封裝…等，所蒐集累積而成的報表資料，不可能長期擺放在SSISDB資料庫而不清掉，這將耗損 SSISDB資料庫所在的 SQL Server 執行個體之資源。預設，為了維護這些記錄資料的保存週期，SSIS 2012 安裝完畢後，會在SQL Server 執行個體的 SQL Agent 服務建立一個「SSIS Server Maintenance Job」作業，而你可以透過「Integration Services 目錄」下「SSISDB」 節點設定作業記錄的保留週期，如圖 12 所示：


▲ 圖12 設定 SQL Server 內 Integration Services 目錄保留作業記錄的週期

圖12 中，可以透過以下屬性設定「作業記錄檔」的保留週期：
‧定期清除記錄檔：當設定為 True，SQL Agent 作業啟動後才會清除保留週期外的記錄。
‧保留週期(天)：定義保留作業記錄最長的天數，SQL Agent 作業啟動後清除週期外的記錄。

而真正執行清除的 SQL Agent 作業「SSIS Server Maintenance Job」之設定，如圖 13 所示：


▲ 圖13 透過 SQL Server Agent 服務的 SSIS Server Maintenance Job 作業維護作業記錄與封裝專案版本

從圖13的作業「步驟」可以看到該作業實際有兩個「步驟」，一是清掉歷史記錄(SSIS Server Operation Records Maintenance)，另一是清掉部署在 Integration Services 目錄內，過老的 SSIS 專案版本，從圖 12 之「每一專案的版本數目上限」屬性可以看到預設為 5，而「SSIS Server Max Version Per Project Maintenance」步驟將會清掉過老的版本。另外，從圖 12 的「排程」可以發現這個「作業」預設每天上午 12:00 都會執行。

實際檢視這兩個步驟，可以看到分別呼叫 SSISDB 資料庫內的 [internal].[cleanup_server_retention_window] 和 [internal].[cleanup_server_project_version] 兩個預存程序，換句話說，若你不想只憑 SQL Agent 作業清除 SSIS 作業記錄或封裝版本，也可以自行呼叫這兩個預存程序。