SQL Server Integration Services 2012初探(1)
從SQL Server 2005後，SSIS至SQL Server 2008 R2 版本皆沒有較大的變化。在 2012 版本終於有較為顯著的改變，其強化主要可分為兩方面：

1.改良封裝設計環境的使用者介面
‧協助新的使用者上手
‧增進 ETL 開發者生產力
‧共通設定，如共享連接管理員(Shared connection managers)、封裝參數
‧編輯設計環境的復原(Undo)/取消復原(Redo)
‧資料流程提供彈性的編輯順序

2.改善 SSIS 專案的部署、設定和管理
‧容易改變以專案為單位或是封裝為單位的部署格式
‧支援執行環境與參數
‧在 SQL Server 內建立專屬的 SSIS 資料庫，以存放中繼資料與執行狀況
‧能透過 SSMS 為一般的 SSIS 問題除錯
‧以 PowerShell 管理SSIS

我們就先從SSIS 封裝開發工具開始介紹。

SQL Server Data Tools

SSIS 封裝開發工具改以基於 WPF 重新開發的 Visual Studio 2010 為本，並重新命名為 SQL Server Data Tools(SSDT)，不再採用以往的開發環境名稱 Business Intelligence Development Studio(BIDS)，其設計畫面如圖 1 所示：


▲ 圖 1：透過 SSDT 設計 SSIS封裝



除了 Visual Studio 2010 本身提供的 .NET Framework 4.0、跨螢幕多視窗開發、按住 Ctrl 鍵就可以透過滑鼠滾輪放大縮小內容…等優點外，SSIS 小組也針對封裝開發環境增加了許多新功能。首先，以圖 1 右下角的「使用者入門(SSIS) 」視窗可以上網檢視關於 SSIS 的學習資料。設計封裝時，最常使用的「SSIS 工具箱」與「變數」視窗，現在只要可直接透過封裝設計環境右上角所新增的兩個快速按鈕(如圖2所示)開啟SSIS團隊所建立的工具視窗。



▲ 圖 2：以封裝設計環境右上角的「SSIS 工具箱」按鈕叫出設計封裝可用的工具箱視窗


「SSIS 工具箱」也可經由主選單「SSIS」-「SSIS 工具箱」選項叫出。若用 Visual Studio 預設的主選單「檢視」-「工具箱」選項，會叫出空的工具箱，而這個工具箱預設是提供開發 .NET 應用程式所用，以往採用此工具箱總是要載入許多用不到的元件，因而拖慢整個設計環境起始的時間。
「SSIS 工具箱」還提供了新的分類方式。其內個別的控制流程「工作」和資料流程元件(Workflow component)，若是在 SSIS 2008 R2 以往就已經存在，其使用方式與用途變動不大。而代表各元件的圖示全部重新畫過，一些元件有局部的改良而修改了對話窗，如「執行封裝工作」、「資料列計數」、「原始檔案目的地」、「樞紐」…等，無功能變更的個別元件其設定對話窗也就維持原樣，讓既有的經驗可以延續下去。
另外，「SSIS 工具箱」內新增了一些元件，例如 SSIS 團隊從 Attunity 公司購置的「ODBC Source」、「ODBC Destination」、「CDC 分隔器」、「CDC 來源」、「CDC 控制工作」，以及自行開發的「運算式工作」、「DQS 清理」等。並移除了以往的「ActiveX Script 工作」和「Execute DTS 2000 Package Task」，看來，在這一版真的揮別了 SQL 2000 時代。

據微軟 SSIS 團隊指出，整個 SSDT 封裝開發環境有 200 多處小改善，在此列舉數例：

‧重新繪製代表各類工作的圖示、「SSIS 工具箱」新增了更多的分類，以利於找到想用的工具。你可以擺放各元件到不同的項目下(如圖 3 中的「我的最愛」、「通用」、「其他工作」)。


▲ 圖 3：SSIS 團隊專為呈現封裝可用元件所提供的「SSIS 工具箱」



此外，可以在上圖 3 右下角，點選「尋找範例」。會自動跳出瀏覽器，列出目前網際網路上可用的範例。它所帶出來的URL範例如下：
http://code.msdn.microsoft.com/site/search?f[0].Type=Topic&f[0].Value=SsisDataflowTask

網址URL的參數會依據滑鼠所停駐的工具而有所不同。

‧封裝設計環境可以回復編輯(undo)[註1]或取消復原(Redo)。除了透過快捷鍵「Ctrl-Z/Ctrl-Y」或主選單「編輯」下的「復原/取消復原」選項外，也可以透過工具列上的按鈕一次復原/取消復原多個步驟，如圖 4 所示：


▲ 圖 4：透過工具列上的按鈕一次復原/取消復原多個封裝設計步驟


-----[註1]-----
此項功能號稱多個 SSIS 版本以來，排名第一的需求。因為以往若在設計封裝時，一不小心犯了個錯，就需要趕快關掉該設計中的封裝，不要儲存。在重新開啟封裝，否則就需要從版本控管處從新取得舊版封裝定義，或是重新再做一次了。
-----end-----

‧撰寫註解較為友善，按「Enter」鍵可以直接換行，之前版本在建立註解時，須搭配鍵盤上的 Ctrl + Enter 按鈕才能換行，單獨按 Enter 鍵會結束編輯，這讓許多新手誤以為是工具的Bug。。
‧封裝「變數」可透過對話窗更改「範圍」，不需刪除後重建。


▲ 圖 5：以封裝設計環境右上角的「變數」按鈕叫出「變數」視窗



透過「變數」視窗上方新增的移動變數按鈕，叫出「選取新的範圍」對話窗，可以重新設定變數所在的「範圍」，如圖 6 所示：


▲ 圖 6：透過「選取新的範圍」對話窗，重新設定變數所在「範圍」



‧改變在除錯階段各元件呈現進行中/成功/失敗等狀態的表示法，透過活潑的圖示讓你的畫面更賞心悅目，如圖 7 所示：


▲ 圖 7：在 SSDT 執行除錯時，透過元件右上的小圖示呈現該元件的執行狀態



‧資料流程的工具箱增加「來源小幫手」、「目的地小幫手」，隨時透過精靈讓新手較容易設定資料連接，而不似以往僅有在建立新專案時，可以選擇「Integration Services 連接專案精靈」，僅有一次透過精靈設定連接屬性。
‧資料流程(Data Flow)可以加入「群組(group) 」，在邏輯上歸類元件的用途。如圖 8 所示：


▲ 圖 8：透過設計環境的「群組」功能，替設計增加可讀性



圖 8 中「取得資料」、「樞紐所需的工作」是筆者自己透過 Ctrl 鍵搭配滑鼠左鍵選擇不同的元件後，再以滑鼠右鍵選擇快捷選單中的「群組」選項，便可以方形外框包住這些元件，點選方框右上角的箭頭便能展開/收合群組。以往僅有在控制流程可以圈起多個工作設成群組，展開/收合這些群組提供在設計與檢閱時，專注在重要的區塊。
‧若在資料轉換流程中，重新改變了某個元件環節的設計，造成其後流程中的元件其資料欄位有疑義，可透過對應欄位差異的編輯器來了解與修正，如圖 9 所示：


▲ 圖 9：在「解析參考」對話窗設定資料流程的元件間之欄位疑義



‧可以單步除錯「資料流程」的「指令碼元件」內之 .NET程式，不似以往僅有「控制流程」的「指令碼工作」能夠設定中斷點單步除錯。
‧簡化啟用與停用資料檢視器，可以滑鼠右鍵點選資料流程的路徑，直接從快捷選單啟用/停用。如圖 10 所示：


▲ 圖 10：在 SSDT 執行封裝以除錯資料流程時，簡化啟用與停用資料檢視器



當滑鼠雙擊兩個元件間的資料流程路徑，或是選擇圖 10 中的「編輯」快捷選項，叫出「資料流程路徑編輯器」對話窗，切換到「資料檢視器」項目時，可以看到如圖 11 右方的設定畫面：


▲ 圖 11：BIDS/SSDT 透過「資料流程路徑編輯器」對話窗設定資料檢視器所顯示的資料行



圖 11 內左方的對話窗為 SSIS 2008 R2 以前在 BIDS 設定資料檢視器的方式，右方為 SSIS 2012 經由 SSDT設定資料檢視器的方式，據微軟的開發團隊所言，除了「方格」以外的資料檢視器似乎都沒有人在用，所以在 SSDT 內直接移除，以簡化設定。
‧若連接管理員的內容是透過 SSIS Expression 定義，則代表連接的圖示旁會再出現 fx 圖示，如圖 12 所示：


▲ 圖 12：在「連接管理員」呈現透過 Expression 設定連接屬性的個別連接



同樣地，若封裝變數的值是透過 SSIS Expression 定義，則該變數的圖示也會出現 fx 圖示，如圖 13 所示：


▲ 圖 13：fx 圖示代表變數的值是透過 Expression 定義



‧檢視實際定義封裝的 XML 碼時，以較工整的格式呈現，如圖 14 所示：


▲ 圖 14：檢視程式碼時，以工整內縮的 XML 呈現 SSIS 封裝的實際內容



除了格式較為工整外，以往版本的封裝屬性多以 XML 元素表示，SSIS 2012 後則多改為屬性。

‧從專案部署檔案(副檔名為 ispac) [註2] 或 SQL Server 執行個體內的 Integration Services 目錄匯入封裝定義，以建立本機上的 SSIS 專案，讓你容易重新建立並維護立已經部署的 SSIS 專案。
在 SSDT 新增專案時，「Integration Services」項目下多了一個「Integration Services 匯入專案精靈」，如圖 15 所示：


▲ 圖 15：藉由「Integration Services 匯入專案精靈」重新建立並維護立已經部署的 SSIS 專案


-----[註2]-----
在 SSDT 針對 SSIS 專案執行建置，便會產生副檔名為 ispac 的單一檔案，其內放置 SSIS 專案內的各種資訊，例如各個封裝、共享的連接管理員、專案的參數…等等。其實它是一個 zip 檔，你可以將 .ispac 副檔名更名為 .zip，就可以檢視其內的各種定義檔案。
-----end-----

精靈的執行畫面如圖 16 所示：


▲ 圖 16：「Integration Services 匯入專案精靈」執行步驟



圖 16 中，「Integration Services 匯入專案精靈」從SQL Server 執行個體內的 Integration Services 目錄之 SSISDB/myDemo/myDemo 路徑下取得先前部署的專案定義，並依此在 SSDT 內於本機硬碟建立一個新的 SSIS 專案。
‧可以個別設定資料連接「離線工作」。在設計 SSIS 封裝時，其設計環境會透過連接管理員直接存取資料源，以確定使用該連接的元件所需之中繼資料，隨時確認其設定是否正確。但若在你設計時無法存取資料連接所指定的資料源，或是網路連接速度很慢，而你僅是想快速開啟封裝以檢視設計內容，可以透過主選單「SSIS」-「離線工作」選項，但這會中斷封裝內所有的連接管理員。在 SSIS 2012 的 SSDT 內，可以個別選擇不同的連接管理員，透過快捷選單設定其離線，如圖 17 所示：


▲ 圖 17：在資料管理員個別選擇不同的資料連接以設定其離線



被設定離線的連接其圖示會增加一個紅色圓點，而這僅會影響到使用該連接的元件。
由於 SSDT 改善了非常多處設計封裝的方式，在此無法一一詳列，就請你自行感受:)。而除了前述設計封裝的個別特性外，SSIS 2012 封裝設計最大的改變應屬加入了專案的共通屬性，以利一整組多個封裝的設定與執行，限於篇幅，我們將在下一期專欄中繼續討論此一部份。


-----參考資源-----
‧SSIS Team Blog：http://blogs.msdn.com/b/mattm/
‧SQL University– SSIS 2012: Deploying Projects –Lesson One：http://datachix.com/2011/11/29/sql-university-ssis-2012-deploying-projects/
-----end-----