摘要:[SQL] SQL Server Integration Service - SSIS 簡單應用
本篇不是要說高深的SSIS 技巧,而是用實例的方式,說明如何應用 Visual Studio 的「Business Intelligence Projects」來建立「Integration Service Project」,並且加入(deployment)到SQL中,最後再以 SQL Agent 來排程做定期執行的工作。
如果你的環境是只有安裝 SQL2008 或是先安裝了SQL ,你會發現怎麼系統上也安裝了 Visual Studio 呢?沒錯~當你安裝了 SQL 之後,會連帶的幫你安裝 Business Intelligence (BI) 的開發環境。
要完成 SSIS 的簡單應用,需要下列的步驟
- 開啟 Visual Studio ,並「New Project」
- 選擇「Business Intelligence Projects」在點選「Integration Service Project」
- 修改 dtsx 檔名:Data Transformation Service 這個名稱(Package Object)最後是在 SQL 裡引用的名稱,所以建議改成有符合目的的名稱來使用。其 x 代表這是一個 XML 格式的檔案。
- 接著在 Control Flow tag 中拉入一個「Data Flow Task」,並切換到 Data Flow tag.
- 設定資料來源(Data Source 與 Connection manager)
- 拉入「來源」、「處理」、「目的」的物件
- 編輯「Edit」各個物件,定義要做的事
- 拉動綠箭頭決定資料流(Data Flow)順序
- 對 Package 做 Deployment
- 建立專案 (Build Solution)
- 建立 SQL Agent 工作順序及執行時間點
-*-*-*-*開始動手做吧~-*-*-*-*-**-*-*-*-*--*-*-*-*-*-*-*-*
↓ 開啟 Visual Studio ,並「New Project」
↓ 專案類型 Project Type 選「Business Intelligence Projects」,樣版Templates 選「Integration Service Project」
↓ 預設開啟版面的樣式
↓ 這個名稱最後會出現在 SSIS 的 Stored Package 裡的 Package 名稱,建議改成符合功能的名稱
↓ 跟你確定是不是要改 Package Object 的名稱
↓ 接著在 Control Flow tag 中拉入「Data Flow Task」物件
↓ 到 Solution Explorer 去增加資料來源 Data Sources
增加資料來源的方式有兩種,一種是剛剛說的『到 Solution Explorer 去增加 Data Sources』,另一種是下方『Connection Managers』去建立連線
↓ 建立連線
上述的兩種有何差別呢?
↓ 在 Solution Explorer 建立 Data Source 是建立整個專案都可以引用的資料來源(如同 VS 將 connectionstring 放在 web.config 一樣)
而下方的 Connection Manager 就在某一個 aspx.vb 或 aspx.cs 定義的 connectionstring ,只能在該程式裡使用,屬於 Local 性的定義。
↓ 設定資料來源,以 New 建立
↓ 設定相關資料連結參數
↓ 設定好了就按 Finish 吧
↓ 在左邊的工具列(ToolBox)裡,其物件分為 Source / Destination,有些名稱很類似,大家要看清楚
↓ 接著依序拉入你想要的處理步驟,搭配資料流當然由上到下就是,來源~處理動作(s)~目的輸出
↓ 每一個物件要編輯,就按滑鼠右鍵選「Edit」
↓ 指定來源資料庫,選擇資料取出範圍
↓ 資料來源設定好後,就可以拉動資料流(綠色箭頭)到下一個動作去做處理囉
↓ 建立資料流
↓ Lookup 可以讓你對來源資料流在跟其他資料庫物件做連結的動作。我的例子是去 Join Employee 資料表,然後要取出姓名跟職稱。
↓ 跟 Join 一樣,你要決定配對的機制
↓ 選擇對應的欄位,在勾選你想多看到的內容欄位
↓ 連結配對過程中,可能會產生鍵值 Match 或 No Match,在你將處理完的資料流拉到下一關時,他會問你是要輸出哪一種情況。
當然你也可以將兩種情況分別導到不同的目的去。
↓ Excel Destination 可以將你資料流的內容轉匯到 Excel 檔案去
↓ 決定 Excel 的版本跟存放的位置跟檔名。
↓ 資料存取模式
↓ 從 mapping 中可以看到欄位位置調換的關連
↓ 選擇輸出的工作頁(Sheet)
↓ 從下圖中,你有無注意到跟之前圖片不同的地方?在這提醒一下,當你從 Lookup 匯出資料流到下一的關卡時,其左邊應該會說明,你是採取 Match 或 No Match 的輸出。少了這個你將會發現資料輸出是空的...
↓ 輸出、輸入的選擇畫面
↓ 在這邊我應用的方式事先建立 Excel 檔案,並且定義好欄位名稱與順序,這麼一來 Excel Destination 就會以此來做匯出。
↓按下「向右箭頭」執行看看,若是正常就會全出現綠色底,並且會有資料的匯出。
-=-=-=-=-=-=-=-=-=-=-=-=-===-=---------------
上面的步驟是在講怎樣建立 SSIS 的 Package
----
下面則在說明怎麼將此 Package 安裝到 SQL 裡,甚至是其他台機器呢?
↓看到這張圖,表示過了一晚,因為 Solution Explorer 變成了「方案總管」,這是因為在不同機器上面做的關係。
首先,點選 Project 按滑鼠右鍵,選擇屬性(property)
↓ 然後把屬性「Deployment Utility」的「CreateDeploymentUtility」設定為 True。
↓ 到 SSIS 選單選「Package Configurations」去設定 Package 的參數
↓ 咦?怎麼我的畫面是向下圖而非上圖那樣有「Package Configurations」可以選呢?別擔心,只要把滑鼠點回 Data Flow tag 就會出現了,在 Solution Explorer 點 Project 是看不到選項的。
↓ 開啟選項後,就「Enable package Configurations」然後「Add...」新增囉!
↓ Configuration type:這邊選 XML 格式
↓ ★☆★ 這項很重要值得給三顆星~呵呵....因為我們建立好的 Package 可能會運用在許多地方,不單單是本機的SQL Server,所以需要把一些參數設成可以依不同 Server 做調整。
所以左邊框就是讓你勾選,當將此 Package 安裝在 SQL 裡,哪些項目還能做更改
↓ OK了~按下 Finish 吧
↓ 回到這一頁按下 Close 結束吧!
↓ Package 設定檔都設定好之後,就是要將他建立起來(建立安裝檔),選擇 Build 選單來建置。
↓ 接著在你專案的目錄下\ bin \ Deployment\ 就會有相關的檔案
------ 開始安裝 Package 在你的 SQL 吧 -------------------------
↓ 只需要執行上圖中「安裝檔」那個檔案,就會開始安裝
↓Package 可以以檔案形式,也可以直接 deployment 到 SQL,這個例子我選用檔案方式。
↓設定檔案存放的位置 (特別再說明一次,因為我前半段跟後半段在不同電腦執行,又忘記取相同的專案名稱,所以下圖中的專案目錄是 ISP1 ,如果您是跟著我前半段使用 SSIS_01 那麼這邊的目錄就會是 SSIS_01。
↓ ☆★☆ 剛剛保留可以更改的變數,在此安裝步驟就可以依每台 Server 不同的環境,設定不同的值。
↓ 安裝之後,你要用 Object Explorer 的「Integration Service」去連結他
↓ 安裝好的 Package 會出現在 \Stored Package\File System\Project Name。
↓ 在這個 Package 裡有一些項目還可以再修改(例如輸出的檔案名稱)
↓ 執行就會出現下圖的執行結果
↓ 噹噹噹~在 Excel 裡也能看到資料匯出的成果~搞定。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
如果你沒打算將此 Package 放入排程來定期處理,上述的步驟就完成了。
----------
下面是說明怎麼利用 SQL Agent 來排定工作
↓ 新增工作
↓ 設定新工作的名稱
↓ 建立工作內容(Steps),Type 要選「SQL Server Integrated Service Package」 來呼叫剛剛建立的 Package。
↓在 General 頁籤中 Package Source 選「File System」(因為我是用檔案的方式嘛!)
↓ 檔案會放在 ~SQL\100\DTS\Package\專案
↓ 然後到左邊「進階 Advanced」去設定當此工作完成後,該怎麼辦~當然就是給他結束囉,除非你還有下一個 Step
↓ 接著在 Schedule 設定執行的時間
↓ 呼*999。你就可以在 SQL Agent 的 Job 中查看,剛剛建立的新JOB是否產生了
最後你只需在排程預定的執行時間後,觀察執行的狀況,如果正常 SQL Agent 就會幫你繼續執行下去了!
謎之音:這一篇是繼水塔,寫完馬上覺得不會有人認真看完的一篇,又不是美女圖幹嘛圖片爆多,還無償的哩!
上次去某家大公司面試,主管(面試官)說知道我有在寫BLOG,也知道我喜歡跟人家討論技術、協同處理問題,但是......還是很可惜的,沒能有那個機緣能去那家好公司上班。
-------
後記:使用 SSIS 真的還蠻容易在設定時出錯的,如果參數設定不完善,也會導致執行時錯誤。
例如:我上面使用 Excel 匯出,並指定到 Sheet1$ (因為這樣可以讓資料的輸出順序跟著我在 Sheet1 定義好的欄位順序走),但是萬一該 Excel 檔被刪除了時候,就會導致資料匯出的錯誤。
~ End