[SQL] SQL Server Integration Service - SSIS 簡單應用

摘要:[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 的簡單應用,需要下列的步驟

  1. 開啟 Visual Studio ,並「New Project」
  2. 選擇「Business Intelligence Projects」在點選「Integration Service Project
  3. 修改 dtsx 檔名:Data Transformation Service 這個名稱(Package Object)最後是在 SQL 裡引用的名稱,所以建議改成有符合目的的名稱來使用。其 x 代表這是一個 XML 格式的檔案。
  4. 接著在 Control Flow tag 中拉入一個「Data Flow Task」,並切換到 Data Flow tag.
  5. 設定資料來源(Data Source 與 Connection manager)
  6. 拉入「來源」、「處理」、「目的」的物件
  7. 編輯「Edit」各個物件,定義要做的事 
  8. 拉動綠箭頭決定資料流(Data Flow)順序
  9. 對 Package 做 Deployment
  10. 建立專案 (Build Solution)
  11. 建立 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