SSIS入門與進階

標籤：

SSIS是從MS SQL 2005開始引入的，實際上是DTS的馬甲，是SQL Server Integration Service的縮寫。DTS也好，SSIS也罷，都是ETL（Extract Transform Load）工具，一般用來匯入資料到資料庫。SSIS比普通的ETL更進一步，它是可視化的，用Visual Studio來開發，包檔案(*.dtsx)採用的是XML格式。跟Office2007+系列文檔副檔名一樣，都是???x，代表xml格式，但他沒有壓縮成zip檔案罷了。

 

首先使用SSIS建立一個簡單的Integration Services Project: File->New->Project->Business Intellegence Projects->Integration Services Project

（建立項目1）

（建立項目2）

添加一個Foreach Loop Container

 

, 雙擊這個Foreach Loop Container，點擊左邊的Collection，選擇來源檔案目錄(Folder)，輸入檔案類型(Files)。

 

點擊Variable Mappings，點擊New Variable，在Name輸入變數名稱。這個變數是目錄迴圈中每個檔案的值，稍後會用在每個檔案的處理中。

再在裡面添加一個Data Flow Task

 

 

，雙擊這個Data Flow Task，進入了Data Flow標籤（Control Flow右邊）。因為原始檔案是從測量表讀取出來然後寫到CSV檔案的，所以我們使用Flat File Source(就是文字檔，CSV檔案也屬於這類)。

 

 

雙擊Flat File Source，Flat File connection manager，點右邊的New建立一個文字檔串連，給串連一個名字，選擇一個例子檔案，按照你的情況輸入header rows to skip(跳過頭列)，如果第一列是列名稱，你應該輸入1。

 

點擊Advanced(進階)，給每列選擇正確的資料類型，確定。

 

點擊Columns（列），給每列起個名字。

在我們的資料中，都是基本的裝置、日期、溫度、濕度、氣壓等等。但某些記錄是錯誤的（譬如沒有日期），這些記錄就要忽略，日期是細分為：年、月、日、小時、分鐘的，必須合并，還有溫度、濕度某些情況是沒有的，你就要處理為NULL。

 

對於錯誤的記錄（日期為空白），我們要添加一個Conditional Split，在Condition(條件)中輸入 “LEN(TRIM(Year)) > 0”(只需要判斷年是否為空白)。

 

關於運算式，需要特別說明一下：你可以選作左邊的變數或者列，或者右邊的各種內建函數。你只需要直接拖放變數、列或者函數到輸入框便可。

 

對於完整的日期，我們需要添加一個Derived Column(衍生的資料行)，從Conditional Split拖放關係，要求你選擇跳轉到指定流程的條件。

 

 

雙擊該衍生的資料行，在Expression(運算式)中輸入“Year + "-" + Month + "- " + Day + " " + Hour + ":" + Minute + ":00"”（構造標準的日期格式，如：2010-06-29 16:11），日期類型選database timestamp（根據你自己的實際情況吧）。

 

對於NULL數實值型別，你也需要添加一個衍生的資料行，然後在運算式中輸入LEN(TRIM(Temperature)) > 0 ? (DT_R8)Temperature : NULL(DT_R8)。這裡用DT_R8是因為溫度是double。

 

 

 

然後添加一個匯入目標，我們的目的是把資料匯入到MS SQL Server，所以添加一個SQL Server Destination，雙擊進入編輯

 

點擊New建立一個資料庫連接。

點擊Mapping（映射），把CSV檔案的列對應到資料庫表

 

 

接下來就是運行了，如果你正確設定，資料自然會匯入到資料庫。

 

這裡需要注意，SSIS的設計有頗多的bug，譬如下面提及的指令碼編譯問題，還有在設計器中，不能通過編輯目標資料來直接編輯串連，你要雙擊下方的控制項列表。另外，當你編輯串連，你會發現之前選擇的例子檔案會丟失，每次你都必須重新選擇。

 

如果要發布到SQL Server，你要修改SSIS的項目屬性：Project->(your project name) Properties->Deployment Utility->CreateDeploymentUtility->True。然後雙擊bin\Deployment下面的Deployment檔案，按照提示一步步Next便可（建議安裝到MS SQL Server，而不是檔案系統）。如果要查看已經安裝的，開啟SQl Server Management Studio，選擇Integration Services，開啟。

在開發SSIS的過程中，你可能會遇到一些詭異的情況。譬如： 1.類型轉換，你在Prorgress（進度）標籤中可以查看詳細的結果，但往往結果都比較含糊。一般都是類型沒有選對，譬如SSIS會自動選回字串類型，你又要手工改回database timestamp 2.目標資料庫中的資料表欄位變化了，或者來源檔案格式變了，你運行，肯定錯誤，但在Progress(進度)中，你難以看出個門道。一般看見設計圖中的出現警告或者錯誤小表徵圖，你滑鼠挪上去停一下，就會給個模糊的提示，雙擊，會提示你修正。

 

一些特別情況： 1.內建的控制項無法滿足需要，怎麼辦？自己寫控制項：陳希章同學寫的為SSIS編寫自訂任務項(Task)之入門篇 。這裡還有另外一個例子：執行SQL Job並等待其結束。

2.需要複雜的處理：在Control Flow中用Script Task，在Data Flow中用Script Component；

關於指令碼：添加一個Script Task，雙擊，點擊Script(指令碼)，如果你是SQL 2005 SP1或以下，你就必須選PrecompileScriptIntoBinaryCode，否則你將無法正確運行。或者你給2005安裝SP2；

3.你需要讓SSIS執行並盡量等待你去做其他事情（矛盾啊。。。），你可以添加一個Script Task，輸入System.Threading.Thread.Sleep(想多長就多長)；

 

關於變數：你需要注意變數的範圍。一般的範圍是Package(包)。在指令碼中，如果你想訪問變數，你就必須在ReadOnlyVariables（唯讀變數）和ReadWriteVariables（可讀寫變數）中輸入變數名稱（多個變數用逗號分隔）。然後在編輯指令碼代碼中，使用Dts.Variables("YourVariableName")來訪問變數。

 

關於郵件發送，如果使用內建的Send Mail Task，你會發現無法指定登陸使用者名稱和密碼。我改用指令碼調用sourceforge上opensmtp，或者，你可以使用MS SQL Server內建的郵件功能，然後用SQL Excute Task執行，譬如：

exec msdb.dbo.sp_send_dbmail @profile_name = ‘Foo‘,     @recipients = ‘FooReceiver‘,     @subject = ‘FooSubject‘,     @body = ‘FooBody‘     @body_format = ‘Text‘

 

SSIS入門與進階