SQL Server Integration Services 2012初探(3)
在上期專欄中，主要介紹了SSIS 2012封裝開發環境 SQL Server Data Tools(SSDT) 針對SSIS新增的「專案部署模型」提供的共享物件。而透過SSDT的專案開發多個封裝後，可以「建置」成一個用來部署的 ispac 檔案，交由管理人員利用該檔案一次部屬多個相關的封裝，再以 SQL Server Management Studio(SSMS) 設定封裝的屬性。

接下來繼續說明 SQL Server 端為存放、管理、執行SSIS專案與封裝所新增的功能，但在介紹如何部署前須先說明SSIS 2012新增的「Integration Services 目錄」。

就SSIS封裝部署與管理而言，SSMS「物件總管」視窗的樹狀結構中多了「Integration Services 目錄(也有人稱為SSIS Server)」結點，並在 SQL Server 建立可以存放SSIS封裝的專屬資料庫，它就是 SQL Server 內一般的使用者資料庫，但SSIS團隊已經在其內建置好資料表、檢視、預存程序…等多種物件。由於SSIS在 2012 版大幅改變設定、部署與監控的架構，近似 SQL Server 內MSDB 系統資料庫搭配 SQL Agent 服務的作法。這讓SSIS 2012的管理有全新的風貌。
Integration Services 目錄
SQL Server 2012的「Integration Services目錄」是管理 Integration Services 專案、封裝、參數與環境的之處，其下的 SSISDB資料庫是儲存封裝的核心。
[註：為了向前相容性，SQL Server 2012 搭配SSIS 2012依然保有了以往透過 MSDB 存放封裝之架構，並以 SSMS 的「物件總管」視窗獨立連結某個 Integration Services 執行個體，以載入、維護個別的封裝，但不建議在新的SSIS開發專案沿用這舊的作法。]

一個 SQL Server 執行個體只會有一個目錄(catalog)，而SQL Server中的目錄與其他新功能是IS部屬與管理所衍生的。以下將透過操作步驟說明如何在SSMS中建立與檢視「目錄」，其下的「資料夾」、「專案」、「環境」等，以及相關的屬性介紹。值得注意的是，Integration Services目錄只支援SSDT的「專案部屬模式」。

建立Integration Services 目錄
剛安裝好的 SQL Server 2012 執行個體並沒有存放SSIS封裝定義的 SSISDB 資料庫。需在SSMS的「物件總管」視窗內手動建立目錄，如圖1所示。使用滑鼠右鍵點選「Integration Services 目錄」節點，選擇快捷選單內的「建立目錄」選項：


▲ 圖1 在 SQL Server 執行個體建立SSIS目錄


在「建立目錄」對話窗會要求輸入一組密碼，並利用此密碼保護加密金鑰。透過加密方式可保護目錄中的敏感性資料。需注意的是，要啟用資料庫引擎的CLR整合，才能建立目錄。
建立好目錄之後，SSMS 「物件總管」視窗的「Integration Services目錄」節點下會建立SSISDB 子節點，亦即代表 SQL Server 新增名為 SSISDB 的使用者資料庫。該資料庫內已經建立好各種物件，如資料表、預存程序…等，以存放與管理SSIS封裝。

建立完目錄後，可透過屬性變更「Integration Services 目錄」的行為，滑鼠右鍵點選 SSISDB 節點並選擇「屬性」選項，可叫出如圖2 的「目錄屬性」視窗：


▲ 圖2 設定Integration Services 目錄屬性


從圖2 可以看到 SSISDB 資料庫的版本，並設定保留「作業記錄」和SSIS「專案版本」的方式，後文的操作過程將會一併說明此二部分。

資料夾
接下來要在「目錄」內建立一個以上的「資料夾」，才可以存放SSIS的專案與封裝。在「SSISDB」節點下，利用滑鼠右鍵選擇「建立資料夾」選項，如圖3 所示：


▲ 圖3 在SSIS目錄下建立資料夾


如圖3 在「建立資料夾」對話窗賦予「資料夾名稱」後，就可以在 SSISDB 節點下建立資料夾，預設在該資料夾下有「專案」和「環境」兩個子資料夾，每個子資料夾都可以部署多個SSIS專案與建立多個環境定義。
SSISDB 節點下可以建立多個資料夾，以分門別類管理不同的SSIS封裝專案。每個資料夾可以有不同的安全設定，如圖4 所示：


▲ 圖4 賦予 SSISDB 內的角色與使用者存取目錄的權限


透過圖4 右上方的「瀏覽」按鈕，可以賦予 SSISDB 資料庫內的角色與使用者存取該資料夾及其下物件的權限。換句話說，若要增減可用的角色與使用者，需設定 SSISDB 資料庫的安全性。

專案
接下來若要將SSDT所開發的SSIS專案部署到當下的目錄內，可以如圖3 右下方直接滑鼠右鍵點選「目錄」節點，並選擇「部署專案」選項，接著找到透過SSDT建立出的 ispac 檔。或是如圖5 直接透過SSDT的「方案總管」視窗，將SSIS專案部署到目錄：


▲ 圖5 透過SSDT的「方案總管」視窗，將SSIS專案部署到SSIS目錄與資料夾


若該目錄與資料夾下已經存在同名的專案，部署到資料夾後會自動為該專案新增版本，並標示不同的「專案 LSN」。SSIS 預設會保留圖2 所設定「每一專案的版本數目上限」之數量，若「定期移除舊版本」設定為 true，則超過數量且部署時間較早的專案版本會被清掉。

但這需要靠 SQL Server Agent 的「作業」來維護，預設該「作業」名稱為「SSIS Server Maintenance Job」，排程為每日上午 12:00 執行。換句話說，若部署到 SSISDB 某個資料夾內的專案超過數量限制，但「作業」排程的時間未到，或SQL Server Agent 服務根本未啟動，則不會執行清除的動作。
若你直接檢視「SSIS Server Maintenance Job」作業的「SSIS Server Max Version Per Project Maintenance」步驟，可以看到它只是在 SSISDB 內執行以下的預存程序：

EXEC [internal].[cleanup_server_project_version]

換句話說，若你手動執行該預存程序，也可以清除過量的老舊專案版本。若要檢視各專案的版本，可以直接滑鼠右鍵點選代表該專案的節點，而後選擇「版本」選項，如圖6 所示：


▲ 圖6 檢視或還原某個SSIS專案的特定版本


圖6 的「專案版本」對話窗中可以看到「資料夾」下該專案放了幾個版本，你可以勾選任一個不是「目前的」版本，再點選右下角的「還原至選取的版本」按鈕，就可以將SSIS目錄內，該專案預設使用的版本還原到某個時間部署的版本。除了可執行該版本的封裝外，透過圖6 中快捷選單中的「匯出」選項，可以 ispac 檔案格式輸出不同的版本定義，透過SSDT重新匯入並建立專案。

環境
在資料夾下可以看到環境(environment)子節點，一個資料夾可以建立多「環境」，藉以定義一組「環境變數(environment variable)」。透過滑鼠右鍵點選「環境」節點，並選擇「建立環境」選項，如圖7 所示：


▲ 圖7 在「資料夾」下建立「環境」


給予名稱建立完自訂環境後，可再以滑鼠右鍵點選該自訂環境節點，並選擇「屬性」選項，在接下來的「環境屬性」對話窗中，點選「變數」頁籤，可以建立環境變數，如圖8 所示：


▲ 圖8 建立環境變數


圖8 中直接點選賦予新變數的「名稱」，資料「類型」，即可建立新環境變數，其後可以定義「值」，這裡的「Sensitive」依然翻譯作「區分」，如同上篇專欄所介紹的「專案/封裝參數」設定，仍代表此「環境變數」值是否為機密。若勾選為機密資料時，其存放於 SSISDB 資料庫內的變數內容會自動加密，且在設計畫面上的「值」改以星號呈現，若透過 SSISDB 資料庫內預設提供的「catalog.environment_variables 檢視」查詢該機密值，會自動變成 NULL。

每個「環境」可以用來儲存多個「環境變數」，在封裝執行時，將這些變數值繫結至「專案/封裝參數」。你可以針對不同的需求定義不同的環境來組織變數並賦予值，例如為相同的專案與封裝分別設計測試與正式的環境和環境變數。須提醒一點的是，這種環境、環境變數、專案、專案變數、封裝變數等設計與執行方式僅適用於SSIS 2012新提供的「專案部署模型」。

建立完環境變數後，可以滑鼠右鍵點選先前已經部署到 Integration Services 目錄特定資料夾下的專案節點，並選擇「設定」選項，而後在「設定-<專案名稱>」對話窗賦予專案所參考的「環境」，如圖9 所示：


▲ 圖9 設定專案所參考的環境


同一個SSIS專案可以參考多個「環境」。在圖9「瀏覽環境」對話窗中，與當下專案放在同一個「資料夾」內的「環境」會呈現在「本機資料夾(<資料夾名稱>)」的節點下(也稱為相對路徑參照)，而「SSISDB」節點下則呈現整個資料夾結構，讓你選擇不同資料夾內的「環境」定義(也稱為絕對路徑參照)。

若你可能移動專案到不同的資料夾，就需要考慮參照「環境」是採用相對或絕對路徑。由於「專案」和「環境」都可以在資料夾間移動，而執行移動時，SSMS、SSIS 或資料庫定義並未規範參照的完整性，也就是若某個「專案」已經設定對某個「環境」的參照，這時不管是移動「專案」或「環境」造成參照錯誤，執行「移動」時並不會有任何警告。

一旦專案參考了先前定義的環境後，圖9 中間的「設定-<專案名稱>」對話窗就可以切換到「參數」頁籤，透過環境變數賦予專案或封裝參數的值，如圖10 所示：



▲ 圖10 透過環境變數設定專案或封裝參數值

雖然一個 SSIS　專案可以對應多個「環境」，但個別封裝在執行時，一個參數僅能繫結某個環境內某個環境變數，以賦予變數的值。如圖10 中，筆者將myEnv環境中的 strFile 變數值賦予專案參數 strPackageUsedFile。

當環境變數與專案/封裝變數配對後，在實際執行封裝時，會帶進當時的變數值。當利用 SQL Server Management Studio 執行某個封裝時，也可以透過相似的設定方式指定該次執行時，其專案/封裝參數所要使用的「環境」。如圖11 所示：


▲ 圖11 透過 SSMS 執行特定封裝時，指定所要引用先前定義的環境


當實際執行該封裝時，透過圖11 的 SSMS 選擇特定封裝，指定先前定義的環境參照，則 SSIS執行引擎會依先前的定義自動搜索與配對「環境變數」和「專案/封裝參數」，若有對應不上的參數，或資料型別錯誤，則會放棄執行封裝。

圖11 右下方下拉選單內呈現所選擇的環境名稱是圖9 所設定的內容，若你已將「專案」或「環境」移動到不同資料夾，則此處下拉選單可能會因呈現以往設定的內容，而選不到可用的「環境」。

至此，已經介紹完SSDT的專案特徵與SSIS 2012所提供的伺服器環境，在下期文章中，我們再繼續介紹專案部署與執行。