SQL Server Integration Services 2012初探(4)
在上期專欄中，主要介紹了 SQL Server 端為存放管與理 SSIS 2012 專案與封裝所新增的功能。接下來，將繼續說明如何執行專案部署模式的封裝。

執行封裝
若要直接透過 SQL Server Management Studio 執行封裝，可以展開物件總管下的「Integration Services 目錄」節點，直接滑鼠右鍵點選要執行的封裝，並選擇「執行」選項，其畫面如圖 1 所示：


▲ 圖1 透過 Management Studio 直接執行特定封裝。

圖1中「執行封裝」對話窗的「參數」頁籤可用來設定封裝或專案參數，若先前設計封裝時，有指定參數的屬性為「必要」，在「參數」頁籤需賦予值，否則會呈現紅色驚嘆號。可以利用對話窗下方的「環境」，選擇先前在「Integration Services 目錄」已經對「專案」設定到參考的「環境」，由於在專案設定參考環境時，可同時對應「環境變數」與封裝「參數」，所以在此對話窗勾選「環境」後，會自動套用參數值。
除了設定參數外，也可以透過「連接管理員」頁籤改變封裝的連接管理員設定。或是在「進階」頁籤，直接透過 SSIS 提供的「屬性路徑」定義封裝內各種屬性值，以及執行封裝的「記錄層級」，其設定值與記錄內容如表1所說明：

表1 執行封裝時，預設提供的記錄層次選項定義


透過上述設定，可調整執行封裝時同時要收集的事件資料，並放入到 SSISDB 內的[catalog].[operation_messages] 系統資料表，以供預設的報表呈現封裝執行狀況，或是用檢視、預存程序乃至於 T-SQL 自行分析。
在圖 1「執行封裝」對話窗最下方還可以定義：封裝執行期間發生錯誤時是否建立傾印檔，和是否以 32 位元的執行環境執行封裝[註：若你使用的連接管理員需要 32 位元的 Provider，則需要讓 SSIS 在32 位元的執行環境執行封裝。]。
當所有的設定執行完畢並按下對話窗右下方的「確定」鈕後，會彈出一個詢問是否要開啟執行封裝的作業報表對話窗，如圖 2 所示：


▲ 圖2 詢問是否要檢視執行概觀的報表。

圖2若回答「是」，將在 SSMS 看到 SSIS 2012 版為執行封裝所提供的報表，此一部分我們將在後文說明。此外，透過圖 1 「執行封裝」對話窗左上角的「指令碼」按鈕，可以產生執行封裝的 T-SQL 指令，內容如下：

範例程式碼 1：透過 SSIS 所內建的預存程序執行存放在 SSIS 目錄的封裝

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'CacheManager.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'Demo', @project_name=N'myDemo', @use32bitruntime=True, @reference_id=1
Select @execution_id
DECLARE @var0 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
DECLARE @var1 bit = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'DUMP_ON_ERROR', @parameter_value=@var1
EXEC [SSISDB].[catalog].[start_execution] @execution_id

SSIS 目錄(亦即 SSISDB 資料庫)中有內建超過百個預存程序和函數，可藉此撰寫T-SQL腳本，以批處理作業、整合其他作業或與他人溝通。當以 SSMS 工具程式藉由互動的方式執行封裝，或是透過類似範例程式碼 1 的 T-SQL 語法執行，以「Windows 工具管理員」觀察實際在背景執行的應用程式，可發現會呼叫「ISServerExec.exe」工具程式，並非以往我們所熟悉的 dtexec.exe 工具程式。因為實際介紹ISServerExec.exe 工具程式的資料不多，筆者在此僅能提出此現象。
除了透過 SSMS 以互動的方式執行封裝外，也可如以往，透過 SQL Agent 服務的作業，週期性執行封裝，接下來介紹此一部分。

透過 SQL Agent 服務執行封裝
展開 SSMS 「物件總管」視窗內的「SQL Server Agent」節點，滑鼠右鍵點選「作業」節點，選擇「新增作業」選項後，可以如圖 3 建立一個執行 SSIS 封裝的「作業」：


▲ 圖3 建立 SQL Agent 服務的作業，以週期性執行封裝。


在「新增作業」對話窗的「步驟」頁籤，點選下方的「新增」按鈕，以新增一個「作業步驟」。在隨後的「新增作業步驟」對話窗中，於右上方的「類型」下拉選單中選擇「SQL Server Integration Services Package」選項，則「新增作業步驟」對話窗右下方會自動出現設定封裝的頁籤。
在「封裝」頁籤中，於「封裝來源」選定「SSIS 目錄」，若「封裝來源」選擇以往 SSIS 2008 R2 版本之前提供的「SQL Server」、「檔案系統」或「SSIS 封裝存放區」等選項，則「新增作業步驟」對話窗右下方會自動轉成以往的設定方式。要選擇「SSIS 目錄」選項，才會採用新的設定方式。
接著在「伺服器」下拉選單中輸入「SSIS 目錄」所在的 SQL Server 執行個體名稱，並以下方的「封裝」文字方塊右方之「…」按鈕選擇封裝。
透過圖 3 選定待執行的封裝後，可以在「新增作業步驟」對話窗的中間切換到「組態」頁籤，如圖 4 所示：


▲ 圖4 設定 SQL Server Integration Services Package 類型的作業步驟組態。


圖4中，「新增作業」步驟的執行封裝組態設定方式與圖 1透過 Management Studio 直接執行特定封裝的設定方式相同，在此也就不重複說明。
而透過 SQL Server Agent 服務的「作業」執行 SSIS 封裝時，還是呼叫 dtexec.exe 工具程式執行，這與以往 SSIS 版本執行封裝的方式相同。需要強調一點的是，雖然 SSIS 團隊有更新dtexec.exe 工具程式以支援 SSIS 2012 的功能，但截至目前，筆者手中的 SSIS 2012 RC0 版，沒有更新另一個互動執行封裝的 DTExecUI工具程式。其畫面依然如昔，如圖 5 所示：


▲ 圖5 透過 SQL Server 2012 RC0 版本的 DTExecUI 工具程式只能以舊的方式執行 SSIS 封裝。


換句話說，不能透過 DTExecUI 工具程式執行存放在「SSIS 目錄」內的封裝，也無法藉由在 DTExecUI工具程式內，以互動的方式設定完畢相關屬性後，切換到圖 5 左方的「命令列」頁籤，取得欲透過 dtexec 工具程式執行存放在「SSIS 目錄」的封裝之參數設定值。
現今，若你想要自動產生dtexec 工具程式的執行參數，僅能靠 SQL Server Agent 服務的作業設定環境，先透過如圖 3、4 的方式設定好「類型」為「SQL Server Integration Services Package」的「作業步驟」後，再切換為「作業系統(CmdExec) 」類型，則在「命令」文字方塊中，會呈現 dtexec 工具程式執行該封裝時，所需設定的參數內容，如圖 6 所示：


▲ 圖6 將步驟類型從 SQL Server Integration Services Package 轉成作業系統類型(CmdExec)，以建立 dtexec 工具程式可用的參數。


你可以複製「命令」文字方塊中的參數定義到「命令提示字元」環境，透過 dtexec 工具程式測試，指令範例如下：

Dtexec /ISSERVER "\"\SSISDB\Demo\myDemo\CacheManager.dtsx\"" /SERVER "\".\"" /ENVREFERENCE 1 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /REPORTING E /CALLERINFO SQLAGENT

執行畫面如圖 7 所示：


▲ 圖7 以 dtexec 在「命令提示字元」環境執行 SSIS 封裝。



透過 dtexec 工具程式執行 SSIS 封裝可以增加許多資料整合時的彈性，例如透過命令提示字元、 T-SQL 或 .NET …等腳本語言/程式碼兜組命令，將封裝交由 dtexec 工具程式執行。以下稍微介紹 Dtexec 工具程式在 SSIS 2012 版新增的參數功能。

Dtexec 工具程式在 2012 版新增的選項功能

如同先前的專欄所描述，SSIS 2012 新增以專案為單位部署封裝到伺服器。以往用來執行封裝的DTEXEC.exe 工具程式除了繼續支援舊有功能外，也針對此增強，好執行存在「SSIS目錄」中封裝。例如：指定封裝在伺服器上的路徑，以及專案/封裝參數，和連接管理員的屬性值，以及伺服器執行選項等。由於我們僅探討 SSIS 2012 新增的功能，故 dtexec 工具程式的完整功能請參閱 SQL Server 的線上說明、搜尋網站或是坊間書籍。
DTEXEC.exe 工具程式用於執行專案封裝的新選項列於表 2，這些選項都非必要，你可選擇性使用：

表2 dtexec工具程式在 2012 版新增的選項功能


搭配表 2 與上述從 SQL Server Agent 服務的作業執行封裝產生的參數為例，去掉作業步驟轉換參數時，為標示雙引號產生的多餘溢出字元(\”)，同時拿掉呼叫 dtexec工具程式執行封裝的應用程式名稱 /CALLERINFO SQLAGENT，整個命令提示字元語法如下：

Dtexec /ISSERVER "\SSISDB\Demo\myDemo\CacheManager.dtsx" /SERVER . /ENVREFERENCE 1 /Par "$ServerOption::LOGGING_LEVEL(Int16)";1 /Par "$ServerOption::SYNCHRONIZED(Boolean)";True /REPORTING E

從 /SERVER 選項的 “.” 知道欲執行的封裝是存放在本機 SQL Server 預設執行個體內。ISSERVER 選項設定的「封裝路徑」可以看出該封裝是放在 SSISDB 目錄 Demo 資料夾下，myDemo 專案內的 CacheManager.dtsx 封裝。執行時，以 /Par 參數設定伺服器選項($ServerOption)的記錄層次(Logging_Level參看圖 1)是「基本」。
[註：此處的整數值(Int16)分別代表不同的「記錄層次」：
0 為「無」、
1 為「基本」、
2為「效能」、
3為「詳細資訊」。

至於伺服器選項的同步化(Synchronized)，我在當下找不到資料解釋該選項。但實際執行時，可以看到 dtexec 工具程式於執行封裝時會叫起 ISServerExec.exe 工具程式，若 Synchronized 設為 true，則 dtexec 工具程式會等封裝執行完才結束。反之，設為 false，則 dtexec 工具程式叫起 ISServerExec.exe 工具程式後就自行結束，還回執行權，徒留ISServerExec.exe 工具程式在背景執行完封裝。/REPORTING E 選項則是設定只傳回封裝執行時發生錯誤(E)事件。
[註：REPORTING 選項可以搭配其他參數，分別代表：
N為「無」、
E為「錯誤」、
W為「警告」、
C為「自訂事件」、
P為「管線事件」、
F為「資訊」、
V為「詳細資訊」。
換句話說，執行封裝的過程中，只有錯誤訊息會出現在命令提示列。

若dtexec工具程式是執行「Integration Services 目錄(SSIS 伺服器) 」內的封裝。則依然會叫用 catalog.create_execution、catalog.set_execution_parameter_value 和 catalog.start_execution等 SSISDB 內的預存程序，建立一個執行(execution)定義並設定參數值，最後開始執行。過程中，可以透過 T-SQL 語法查詢檢視、預存程序或預設的報表看到所有的執行記錄。所以除了DTEXEC錯誤訊息，使用者可以利用前述工具，在伺服器端排除造成執行封裝出錯之問題。

DTEXEC還支援從 .ispac 專案檔執行封裝。相關的選項是：/Proj[ect] 和 /Pack[age]，以指定專案路徑和封裝名稱。參數值也可以透過/ SET 和 / CONF 選項設定。要設定的參數值，可經由 $Project 或 $Package 命名空間定義不同層級的參數。
從 SQL Profiler 工具程式錄製 dtexec 工具程式執行存在 Integration Services 目錄的封裝時，發出對 SQL Server 執行個體的命令，可以看到與範例程式碼 1 近似的 T-SQL 語法：

declare @p6 bigint
set @p6=57
exec catalog.create_execution @folder_name=N'Demo',@project_name=N'myDemo',@package_name=N'CacheManager.dtsx',@reference_id=1,@use32bitruntime=0,@execution_id=@p6 output
select @p6
exec catalog.set_execution_parameter_value @execution_id=57,@object_type=50,@parameter_name=N'CALLER_INFO',@parameter_value=N''
exec catalog.set_execution_parameter_value @execution_id=57,@object_type=50,@parameter_name=N'LOGGING_LEVEL',@parameter_value=1
exec catalog.set_execution_parameter_value @execution_id=57,@object_type=50,@parameter_name=N'SYNCHRONIZED',@parameter_value=1
…

dtexec 工具程式相關的說明可以參照以下 blog：

http://blogs.msdn.com/b/mattm/archive/2011/07/24/using-dtexec-with-packages-on-the-is-server.aspx

最後，有一個有趣的問題討論，其網址為：

http://social.msdn.microsoft.com/Forums/en-US/sqldenintegrationservices/thread/e83ba532-6c5c-45a7-8c0e-a9e10e31cb2f

該問題強調，若執行封裝時，發生了 SSIS 處理不了的意外狀況，且因為執行狀態未回復而造成封裝無法執行，可以初始化 SSIS 的執行狀態，其指令如下：

Use master
execdbo.sp_ssis_startup

該指令等同重啟 SQL Server 服務，以重置各種 SSIS 的執行狀態。
本期專欄就介紹到此，在下期專欄中，我們將繼續說明 SSIS 2012 所強化的執行記錄與報告。