SQL Server Integration Services 2012初探(2)
在上期專欄中，主要介紹了 SSIS 2012 封裝開發環境 SSDT 所提供的新介面，接下來，繼續說明針對 SSIS 專案所做的改良。

SSIS 專案
在 SSIS 2008 R2 版本以前，部署與執行是以個別封裝為主，雖 BIDS 的 SSIS　專案有「建置」功能，並搭配 SSIS 的封裝安裝精靈，將封裝與相關檔案統一複製到特定目的地，但也僅此而已。基本上，封裝是獨立的個體，封裝間的彼此呼叫、共通設定、執行狀況、追蹤歷程…等，都需要開發人員自行想辦法，這往往需要架構人員的巧思，設計一套封裝呼叫封裝的架構(Framework)、範本(Template)、規範(Policy)與流程(Process)，以讀取設定執行一套相關的封裝，回傳執行狀況並經由報表呈現進度與整體結果，以利追蹤與除錯。

在先前的版本中，封裝可以呼叫其他封裝，這在SSIS實作中常會用到。但 SSIS 對這部分支援不足，例如 ：
‧父封裝使用較難的機制傳遞參數值到子封裝：子封裝要透過組態設定讀取父封裝內的變數定義。
‧在SSIS每個封裝都有自己所定義的設定檔、記錄檔，而非共用設定。
‧若有一個系統要用到多個 SSIS 封裝，這要將封裝一個個複製到特定位置，若封裝間有互相叫用或是其內的設計有呼叫到外部的目錄檔案…等，則可能需要放到特定位置，避免封裝間彼此找不到，或找不到所需的資源。
‧在設計封裝時，沒有任何地方支援相對路徑。
‧SSIS 服務的管理介面並未支援多封裝的設定與執行架構，意即無法解決上述的問題

SSIS 2012 試圖改善此一問題，藉由「專案」讓你在建置、部屬或執行時，將多個封裝包成一個單一的項目，並搭配 SQL Server 2012 內新增的「Integration Services 目錄」，提供執行「環境」。接下來介紹此一部分。

若比較新舊版本開發環境的「方案總管」視窗，會發現SSIS 2012專案與前一個版本稍有不同。例如在新舊版本的SSDT/BIDS中，使用滑鼠右鍵點選方案總管中的專案，比較它所提供的功能。如下圖 1 所示，左邊是SSIS 2012的SSDT開發環境，右邊是SQL 2008 R2的 BIDS 開發環境：


▲ 圖 1：在「方案總管」視窗中，檢視專案所提供的功能


除了 SSIS 封裝節點下的子項目增加了「專案參數(Project params) 」和專案共享的「連接管理員」節點，並移除了以往不常用到的「資料來源」和「資料來源檢視」等子結點外，封裝所提供的屬性設定也增加了許多。

首先，點選左方快捷選單的「屬性」選項，叫出如圖 2 的「<專案名稱>屬性頁」對話窗：


▲ 圖 2：透過「<專案名稱>屬性頁」對話窗設定 SSIS專案的屬性


SSIS 2012 增加了專案等級的共通設定，讓相關的封裝從設計到執行，都能夠較方便地串連在一起。例如：圖 2　中專案層級的「Protection Level」屬性。如同以往的版本，個別封裝可以藉由「使用者金鑰」或「密碼」加密封裝的機密資料或整個封裝的 XML 定義。在 SSIS 2012 可以將此設定放到專案的「Protection Level」屬性，則新增到專案的封裝，其「安全性」下的「Protection Level」屬性預設會繼承此處設定。
你可以修改個別封裝的「Protection Level」屬性，使其與專案的「Protection Level」屬性不同，但若要透過專案部署該封裝，則會丟出如下的錯誤，而無法部署：

專案一致性檢查失敗。偵測到下列不一致: <封裝名稱>.dtsx 的 ProtectionLevel 與此專案不同。

此外，圖 2 中可看到專案本身也有「版本」和「識別」等相關屬性，藉此辨別整個專案的開發與部署。當部署到 SQL Server 的「Integration Services 目錄」後，會參照此處的「版本」提供整組專案之版本控管機制。

共享連結管理員
當多個封裝要一起開發、部署、執行時，多半開發環境所用的連接管理員之設定在正式環境無法使用，不管是伺服器名稱或是登入帳號/密碼都可能需要重新設定。以往需透過個別封裝統一讀取外部的設定，讓各個封裝一起更改連接屬性。SSIS 2012 後多了一個選項，使用專案等級的「共享連結管理員」，也就是圖 1 左方「方案總管」視窗內專案下的「連接管理員」結點。

若透過圖 1 左方「方案總管」視窗快捷選單的「轉換為封裝部署模型」選項，將專案的部署方式改回舊有的 SSIS 2008 R2 方式。或是在 SSDT 直接開啟 SSIS 2008 的專案，而未以滑鼠右鍵點選專案節點，並選擇「轉換為專案部署模型」選項，以更新部署模型，將不會有「連接管理員」結點。如圖 3 所示：


▲ 圖 3：透過專案提供的選項切換部署模型


透過圖 3 的選項啟動兩種模型間之轉換，會分別叫出不同的精靈，以進行設定與檢測，此處以討論共享連接管理員為主，在其後的文章再討論如何切換部署模型。

若要建立專案共享的連接管理員，可以滑鼠右鍵直接點選某個封裝內的連接管理員，透過「轉換為專案連接」選項以切換模式。其後在「方案總管」之「連接管理員」下，會呈現代表該共享連接管理員的節點，如圖 4 所示：
　

▲ 圖 4：「連接管理員」在專案與封裝間互相切換


圖 4 下方的步驟則是將在封裝內呈現的專案共享連接管理員轉成封裝專用之連接管理員，這可能會導致相同專案內其他的封裝出現問題，故有「轉換確認」的警告。

除了轉換在封裝內之「連接管理員」頁籤下既有的連接外，也可以透過「方案總管」直接加「共享連接管理員」，如圖 5 所示：


▲ 圖 5：透過方案總直接新增「共享連接管理員」


一旦專案建立了一個「共享連接管理員」後，它會自動出現在專案內的每一個封裝，讓你可以直接使用。但要小心的是，若從任何一個封裝刪掉共享連接管理員，或是將共享連接管理員轉換成某個封裝個別的連接管理員後，也會自動移除其他封裝所對應的「共享連接管理員」。換句話說，若任意刪除或轉換專案的「共享連接管理員」，可能導致其他的封裝因遺失了連結管理員而無法正常執行。

除了專案內的封裝可共用「共享連接管理員」的定義外，相互呼叫的封裝也可以透過「共享連接管理員」共用快取資料。其設計畫面如圖 6 所示：


▲ 圖 6：在父封裝與子封裝間共享記憶體中的快取連接管理員


圖 6 左方的父封裝中，在透過「執行封裝工作」呼叫子封裝前，先透過「資料流程工作」準備快取資料，如圖 7 所示：


▲ 圖 7：透過「快取連接管理員」將資料快取在執行 SSIS 封裝的工具程式之記憶體中


在圖 7 「快取連接管理員」的「一般」頁籤並未定義「使用檔案快取」，亦即指將資料快取在記憶體中。而被呼叫的子封裝之「資料流程工作」內，透過「查閱」來讀取父封裝「快取連接管理員」所快取的資料，將某個鍵值轉換成另一個值，其設定如圖 8 所示：


▲ 圖 8：在「查閱」內讀取「快取連接管理員」的資料，以轉換鍵值


在「查閱」的「一般」頁籤設定「連接類型」為「快取連接管理員」後，就可以在「連接」頁籤選擇專案共享的「快取連接管理員」。執行父封裝後，子封裝可直接透過共享的「快取連接管理員」取得父封裝快取在記憶體中的資料。
關於「共享連接管理員」的相關資訊可參考以下的 blog：
http://blogs.msdn.com/b/mattm/archive/2011/07/19/project-connection-managers.aspx

專案和封裝參數
參數(Parameters)是從外部，呼叫端傳進資訊給被呼叫端的一種方式。以往，SSIS 父封裝叫用子封裝時，若想要傳遞一些設定，必須從子封裝透過組態讀取父封裝的變數，這違反了撰寫程式基本的封裝(Encapsulation)概念，因為子封裝要了解父封裝的設計內容。SSIS 2012 增加了參數的功能，同樣地，也有分封裝層級和專案層級。封裝層級的參數只用在特定封裝，專案層級的參數可以被專案內所有封裝使用。

參數的用途與 SSIS 以往提供的變數近似，可用來改變封裝內物件在執行時期的屬性，但在執行封裝時，無法變更參數內容值[註1]。在 SQL Server 2012 內的「Integration Services 目錄」也有專門提供存放這些參數設定的地方，提供執行該封裝時的預設值。

[註1]類似撰寫應用程式時，父函數呼叫子函數時，以傳值(By Value)的方式傳入參數，而非以傳址(By Reference)的方式傳遞。

若要在 SSDT 的封裝設計環境建立與設定封裝參數或專案參數，可以如圖 9 所示：


▲ 圖 9：在封裝設計環境建立與設定封裝參數或專案參數


若要增加一個封裝等級的參數，可以如圖 9 左上方，在封裝設計環境中的「參數」頁籤，點選左方第一個按鈕。若要設定專案的參數，則需滑鼠雙擊「方案總管」視窗內的「Project.params」節點，在新開啟的「Project.params[設計]」頁籤中編寫專案參數。
圖 9 的參數定義畫面中，其「區分」之翻譯有些奇怪，它的原文為「Sensitive」，或許翻為「機密」或「敏感」較為適合。若設定該參數為機密資料時，其存放於封裝的參數內容會自動加密，且在設計畫面上的「值」改以星號呈現。檢視封裝原始的 XML 內容，可以看到如下的加密結果：

DTS:CreationName=""
DTS:DataType="8"
DTS:DTSID="{8F5258B6-DD80-4D84-9CD4-DBC17DB5593D}"
DTS:ObjectName="strFile"
DTS:Required="True"
DTS:Sensitive="True">
DTS:Name="ParameterValue">
DTS:DataType="8"
DTS:Name="ParameterValue"
Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAQFqscUqX4UadKBjAkvk…

另外，「必要(原文為Required)」屬性若設為真，則在封裝執行時，該參數必須有值。
若要在設計封裝時，以 SSIS Expression 引用這些參數，其名稱格式如下：

$Project/Package::參數名稱

專案等級的參數使用 Project 字樣，封裝等級的參數使用 Package 字樣。若要取參數值，則須以 @ 開頭並以中括號括起參數，格式如下：

@[$Project/Package::參數名稱]

在此透過範例說明，筆者在封裝內建立了一個名稱為 intCount 的參數，則設計封裝內容時，在「For 迴圈容器」和「指令碼工作」引用該參數的方式如圖 10 所示：


▲ 圖 10：在「For 迴圈容器」和「指令碼工作」引用參數


圖 10 的範例中，筆者定義了一個封裝變數為 i，在「For 迴圈容器」的「EvalExpression」設定中，依照 SSIS Expression 的定義，透過 @ 符號取參數或變數值。因此，若執行迴圈的評估條件是：在變數 I 的值小於等於封裝參數 intCount 值時，迴圈繼續執行，其語法如下：

@i<=@[$Package::intCount]

在圖10左下方「指令碼工作」的「ReadOnlyVariables」屬性，可以透過「選取變數」對話窗分別選擇變數和參數，而在其內的 .NET 程式碼存取參數之方式與變數相同，範例語法如下：

MessageBox.Show(string.Format("{0}/{1}",Dts.Variables["i"].Value,
Dts.Variables["intCount"].Value));

除了上述透過「For 迴圈容器」和「指令碼工作」引用參數外，若在父封裝藉由「執行封裝工作」呼叫子封裝時，於「封裝」頁籤的「ReferenceType」選項使用的是「專案參考」[註2]，則在「參數繫結」頁籤可以設定子封裝的參數，其值需透過父封裝的參數或變數賦予，如圖 11 所示：


▲ 圖 11：透過「執行封裝工作」呼叫子封裝時設定子封裝的參數值

[註2]SSIS 2012 以專案為單位部署時，封裝彼此叫用可以不需實體完整路徑。部署後，專案中的封裝較容易找到彼此。若 ReferenceType 選擇外部參考，則需如以往的版本，以完整檔案路徑設定要叫用的子封裝。

除了在「執行封裝工作」設定子封裝的參數值，透過 dtexec 工具程式、Agent Job 的 SSIS 類型步驟或直接透過 Management Studio 執行封裝…等，都可以在執行封裝前提供參數值，而此部分將會在下一篇專欄介紹。
最後，關於封裝參數或專案參數的相關資訊可參考以下的 blog：
http://blogs.msdn.com/b/mattm/archive/2011/07/16/configuring-projects-and-packages-using-parameters.aspx