Integration Services 學習

概要

 

俗話說：“十年磨一劍”，Microsoft 通過5年時間的精心打造，於2005年濃重推出Sql Server 2005，這是自SQL Server 2000 以後的又一曠世之作。這套企業級的資料庫解決方案，主要包含了以下幾個方面：資料庫引擎服務、資料採礦、Analysis Services、Integration Services、Reporting Services 這幾個方面，其中Integration Services (即SSIS)，就是他們之間的中轉站、紐帶，將各種源頭的資料，經ETL到資料倉儲，建立Cube，然後進行分析、挖掘並將結果通過Reporting Services 送達給企業各級使用者，為企業的規劃決策、監督執行保駕護航。

 

SSIS 其全稱是Sql Server Integration Services ，是Microsoft BI 解決方案的一大利器，是Sql Server 2000中DTS 一個升級之作。 無論是功能上，效能上，還是可操作方面都有很大的改進。且看下面的操作介面就可見一斑。

 

 

SQL Server 2000 DTS

 

Sql Server 2008 SSIS

現在很多人都把SSIS 說成是一個ETL (Extract-Transform-Load)工具，我個人覺得不太準確，或許是大家基本上都把他做為ETL 使用，其實SSIS已經超越了ETL的功能，ETL 僅是其中之一，它在其它方面也有非常突出的表現：

(1)    系統維護：

a)         在資料庫維護方面：

                                                   i.              Database Backup；

                                                 ii.              統計資訊更新；

                                                iii.              資料庫完整性檢查；

                                               iv.              索引重建

                                                 v.              SSIS 包執行；

                                               vi.              SSAS 任務處理。

b)        業務處理：

                                                   i.              執行SQL 任務。

                                                 ii.              Web Service 任務。

c)        作業系統維護：

                                                   i.              WMI事件觀察器任務

                                                 ii.              檔案系統任務。

d)        其它：

                                                   i.              執行SQL 任務

                                                 ii.              執行進程任務

                                                iii.              ActiveX 指令碼任務

                                               iv.              指令碼任務(VB/C#).

                                                 v.              執行Web Service 服務

     尤其是上面的第四點，可以執行SQL 任務，可以執行Web Service 服務，可以執行系統進程，可以執行(VB/C#)指令碼任務，這給了我們多大想象的空間，還有什麼例外的。強啊。不得不佩服務一下。

       SSIS 的體繫結構主要由四部分組成：Integration Services 服務、Integration Services 物件模型、Integration Services 運行時和運行時可執行檔以及封裝資料流程引擎和資料流組件的資料流程工作(如圖)：

 

 

 

    這是我們初學者必須要瞭解的，只要明白了這個體系統結構，體會了各組成部分之間的關係，清楚了什麼是控制流程、什麼是資料流，SSIS學起來就不難了。

總之，SSIS 並不簡單的是DTS 的一個升級版，除了上面所說的幾個方面的改進外，在開發環境方面，Microsoft 還一如繼往地發揮著他的優勢，與Visual Studio 緊密整合，讓開發人員可以在一個更加熟悉，更加方便的平台上設計、開發，大大降低了入門的門檻，加速了學習、開發的進度。它的組成元素也更加對象化，每一個包、每一個任務、每個一控制流程、每一個資料流，都是一個獨立的對象，有其對應的屬性、對應的事件。VB/C# 的指令碼任務；變數、屬性的參數化，更是讓人震撼，幾乎是無所不能，無所不可似的(有些誇張了，我不是托，只是感覺比以前強大太多了)。使用起來也並不複雜，只要你安裝了SQL Server Integration Services 10.0 服務(SQL 2005 應該是Integration Services 9.0)，New project ，選擇Integration Services 項目，就可以一睹芳容，親密感受他的博大與精深了。

 

資料流程工作（上）

資料流程工作是SSIS中的一個核心任務，估計大多數ETL包中，都離不開資料流程工作。所以我們也從資料流程工作學起。

　　資料流程工作包括三種不同類型的資料流組件：源、轉換、目標。其中：

　　源：它是指一組資料存放區體，包括關聯式資料庫的表、視圖；檔案（一般檔案、Excel 檔案、Xml 檔案等）；系統記憶體中的資料集等。

　　轉換：這是資料流程工作的核心組件，如果說資料流程工作是ETL的核心，那麼資料流程工作中的轉換，則是ETL核心中的核心了。它包含非常豐富的資料轉換組件，比如資料更新、彙總、合并、分發、排序、尋找等。可以說SQL語句中有的功能，它都基本上運用起來了。

　　目標：與“源”相對應，也是一組資料存放區體。包含表、視圖；檔案；Cube、記憶體記錄集等。

 　　除以上三類組件外，還有一種組件，那就是”流(Flow)“，它形象地顯示了資料從”源“，經過”轉換“，最後到達”目的“地的一組路徑。我們可以利用”流“，來查看資料，添加備忘說明等。

　　下面一幅圖，就充分展示了源、轉換、目的、流的關係。

 

　　下面我們以將IIS Log 匯入資料庫為例，來介紹如何進行資料流程工作開發。

　　在開發之前，我們先來看看IISlog 的結構，如圖：

　　它基本上記錄了網頁瀏覽的所有資訊，如日期、時間、客戶IP、伺服器IP、頁面地址、頁面參數等很多資訊，我們再根據這些資訊，在關係型資料庫中，建立一張對應表，來記錄這些資訊。

 

代碼
CREATE TABLE [dbo].[IisLog](
    [c_Date] [datetime] NULL,
    [c_Time] [varchar](10) NULL,
    [c_Ip] [varchar](20) NULL,
    [cs_Username] [varchar](20) NULL,
    [s_Ip] [varchar](20) NULL,
    s_ComputerName varchar(30) null,
    [s_Port] [varchar](10) NULL,
    [cs_Method] [varchar](10) NULL,
    [cs_Uri_Stem] [varchar](500) NULL,
    [cs_Uri_Query] [varchar](500) NULL,
    [sc_Status] [varchar](20) NULL,
    sc_SubStatus varchar(20) null,
    sc_Win32_Status varchar(20) null,
    sc_Bytes int null,
    cs_Bytes int null,
    time_Taken varchar(10) null,
    cs_Version varchar(20) null,
    cs_Host varchar(20) null,
    [cs_User_Agent] [varchar](500) NULL,
    [cs_Refere] [varchar](500) NULL
) ON [PRIMARY]
 

　　萬事俱備，下面我們就可以開始ETL的開發之旅了，開啟Visual Studio 2008　工具，[檔案]-->[建立]-->[項目]，選擇“Integration Services 項目”，ETL　的開發介面就躍入眼帘，這是從事.Net 開發的朋友們非常熟悉的介面。開啟左邊“工具箱”，將“資料流程工作”拖到主視窗“控制流程面板”，如圖所示：

 

　　

　　然後雙擊“控制流程”面板上的“資料流程工作”，進入“資料流”面板，這兩部分UI沒有什麼差異，只是所實現的功能不同罷了。真正的資料流程工作開發，從現在才算開始。

 

　　開啟左邊“工具箱”，可以看到有三大部分：資料流源、資料流轉換、資料流目的地。我們從“資料流源”中，將“一般檔案源”拖到主視窗下，雙擊開啟“一般檔案源”編輯器，點擊“建立”，開啟一般檔案串連管理編輯器，如圖：
　　
 

　　輸入串連名稱，選擇IisLog 檔案，選擇行分隔字元、資料行分隔符號，就可以從預覽視窗看到資料的真面目了。

　　這裡有一點要注意，不同的一般檔案，其行分隔字元、資料行分隔符號都是不一樣的，如果選不正確，將達不到你想要的效果，所有的資料都可能擠到一列中去了。一般行分隔比較簡單，基本上都是以斷行符號換行（｛CR｝｛LF｝）來分隔；資料行分隔符號卻不一樣了，它既可以以任意文本字元來分隔，比如逗號（，）、分號（；）、冒號（：）tab符、豎線(|)，以及常用的文字字元、數字字元，也可以定義每一列的固定寬度來分隔。這就需要視檔案源不一樣，分別對待了。

　　在一般檔案連線管理員中，選擇“進階”，還可以定義每一列的列名、資料類型、字元長度等資訊。　等一切定義完成，點擊確定，返回到一般檔案編輯器介面，前面建立的串連將自動返回到“一般檔案連線管理員”的下拉式清單方塊中，下面就要以選擇需要輸出的列了，如圖：

　 　

    然後再選擇“錯誤輸出”，預設選項如下圖所示：

 

      這一選項非常重要，是要求我們配置當來源資料發生錯誤的時候該如何處理，一般來源資料發生錯誤有兩種情況：一是資料類型錯誤，比如日期格式錯誤、數字變字元了等；另一情況就是字元太長，超出列寬了。根據不同的情況，其處理方式也不一樣，系統提供了三種解決辦法：

　　忽略失敗：是指如果某一行資料錯誤，忽略此行，不影響程式執行，繼續匯入其它資料。

　　重新導向行：將錯誤的資料行，匯入到另外一個資料流目的地，供以後人工檢查後，再重新處理。

　　組件失敗：這是最嚴格的，只要遇到資料錯誤，組件立即失敗，停止運行。

　　就IISLOg 這樣的資料來源檔案來說，有錯誤資料行，那是是經常發生，但是這些少量資料錯誤，也不會影響最終的結果，我們就要以考慮容錯性為主了，放寬對資料品質的要求，一般選擇“忽略錯誤”，以方便程式繼續運行。

     一切都定義完後，我們看到“一般檔案源”控制項上，還有一個紅色的叉(X),那是指沒有為此資料來源定義目標，那就是下一步要定義的。另外下面還有兩個長線箭頭，一個綠色，一外紅色，其中綠色：表示正確資料流通路，紅色表示錯誤資料流通路，如果前面定義錯誤“重新導向行”，那麼錯誤資料將沿著紅色路徑，流向錯誤資料存放地。

　　定義資料來源目標，這可能要簡單一些了，同理從左邊"工具箱"中，看到有很多種類型的資料來源目標，我們選擇“OLE　DB　目標”，將“一般檔案源”控制項下的綠色箭頭串連到“OLE　DB　目標”，然後雙擊，開啟“OLE　DB　目標編輯器”視窗，“建立”資料庫連接，如圖：

 

返回到“OLE　DB　目標編輯器”視窗，在資料訪問模式下，選擇“表或者視圖--快速載入”一項，然後再選擇對應的表,如圖：

 

下面配置列映射，如圖：

 

如果沒有的列，直接忽略即可（前提是表中該列允許為空白），後面仍然是配置錯誤處理方式，參照一般檔案源錯誤處理方式即可。

　　到此為止，一個簡單的資料流程工作就基本上完成了，點擊運行，我們期待已久的結果出現了。

 

 當然，在實際開發過程中，可能並沒有這麼順利，會遇到很多各種各樣的問題，在這篇文章中我們很少提及，主要是因為這僅是個開始，沒有涉及到這麼深入，在以後的專題中，會逐漸講解。

　　　一個簡單的資料來源任務就算完成了，其實這隻是一個Demo ，讓大家瞭解了一個概況，可以說萬裡長城只是走出了第一步，真正的ETL不會這麼簡單。下後面我們將介紹ETL最精彩的部分“資料流轉換”，敬請期待。

 

資料流程工作（下）

 資料流程工作（上），介紹了如何建立一個簡單的ETL包，如何通過一個簡單的資料流程工作，將一個文字檔的資料匯入到資料庫中去。這些資料都保持了它原有的本色，一個字元不多，一個字元地少匯入，但是在實際應用過程中，可能很少有這種情況，就拿IisLog檔案來說吧，其中包含有：請求成功的記錄（sc-Status=200），也有請求失敗的記錄；有網頁（比如：*.aspx、*.htm、*.asp、*.php等）、有圖片、有樣式表檔案（*.CSS)、有指令檔（*.js)等，可謂是鮮花與毒草並存，精華與糟鉑同居啊，我們如何根據不同的需求，把其中的鮮花與精華提煉出來呢，這就是我們今天要講的重點：資料流轉換。

　　在進行資料流轉換之前，我們先介紹一下使用情境：以IISLOG為依據，進行網網站擊率分析（IP & PV 分析），具體需求如下：

　　（1）分析一段時間內，網網站擊率的變化趨勢。同時還需要知道各個周未、各個節假日網站的流量情況。

　　（2）分析一天內，各時段（以小時為單位）網站的壓力情況。

　　（3）瞭解網站客戶群分別來自哪些國家，哪些地區。

　　為了實現這些需求，我們建立了如下的資料模型，請看：

 

   

 
代碼
USE [IisLog]
GO
--建立事實表
CREATE TABLE [dbo].[IISLog](
    [lngID] [bigint] NOT NULL,
    [lngShopID] [int] NULL,
    [lngDateID] [int] NULL,
    [lngTimeID] [int] NULL,
    [csDateTime] [datetime] NULL,
    [lngIpID] [int] NULL,
    [cIP] [varchar](30) NULL,
    [csUriStem] [varchar](1000) NULL,
    [csUriQuery] [varchar](1000) NULL,
    [scStatus] [varchar](30) NULL,
    [UserAgent] [varchar](255) NULL,
    [lngReferer] [int] NULL,
    [csReferer] [varchar](1000) NULL,
    [csRefererKPI] [varchar](1000) NULL,
    [lngFlag] [int] NULL
) ON [PRIMARY]

--IP庫
CREATE TABLE [dbo].[dimIP](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ipSegment] [nvarchar](20) NULL,
    [strCountry] [varchar](20) NULL,
    [strProvince] [varchar](20) NULL,
    [strCity] [varchar](50) NULL,
    [strMemo] [varchar](100) NULL,
 CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--日期
CREATE TABLE [dbo].[dimDate](
    [lngDateID] [int] NOT NULL,
    [lngYear] [int] NULL,
    [strMonth] [varchar](10) NULL,
    [dtDateTime] [datetime] NULL,
    [strQuarter] [varchar](10) NULL,
    [strDateAttr] [varchar](10) NULL,
    [strMemo] [varchar](50) NULL,
 CONSTRAINT [PK_dimDate] PRIMARY KEY CLUSTERED
(
    [lngDateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--時間
CREATE TABLE [dbo].[dimTime](
    [lngTimeID] [int] NOT NULL,
    [lngHour] [int] NULL,
    [strHour] [varchar](10) NULL,
    [strTimeAttr] [varchar](10) NULL,
    [strMemo] [varchar](50) NULL,
 CONSTRAINT [PK_dimTime] PRIMARY KEY CLUSTERED
(
    [lngTimeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 

 下面，我們就一步一步地介紹，如何進行資料流轉換，以達到上面的需求。

　　（一）、"條件性拆分（Conditional Split )"。相當於Sql 語句的Where 條件。這或許是所有資料流轉換任務的第一步，為了減少後續處理的資料量，為了提高系統效能，先過濾掉不需要的記錄。前面講過，IisLog 檔案包括有各式各樣的記錄，而對本例需求來說，為了準確計算IP、PV資料，我們將如何過濾呢。

　　（1）、篩選出純網頁瀏覽記錄。即*.aspx、*.htm（本網站只有這兩種類型的網頁檔案）檔案記錄。

　　（2）、篩選出請求成功的記錄（sc-Status=200)。

　　開啟上一篇檔案的SSIS Solution，切換到資料流Tab，從左邊工具箱中，開啟“資料流轉換”，找到“條件性拆分（Conditional Split）”組件，拖到資料流面板上，然後將“一般檔案源”組件下的綠色箭頭拖到“條件性拆分”組件上，雙擊“條件性拆分”組件，開啟“條件性拆分轉換編輯器”，如圖：

　　在這個視窗，有系統變數、資料來源列、系統函數這些資源可供使用。我們為了篩選出純網頁瀏覽記錄，需要從列cs_uri_stem中找到以.aspx、.htm、“/” 結尾的頁面連結。請分別在上圖列表的“輸出名稱”欄位，輸入“Form Records”，在條件運算式欄位輸入：

RIGHT(cs_uri_stem,5) == ".aspx" || RIGHT(cs_uri_stem,4) == ".htm" || RIGHT(cs_uri_stem,1) == "/"
 然後篩選請求成功的記錄，其表過式為：

sc_status == "200"

 最後將兩個運算式組合起來，即為：

(RIGHT(cs_uri_stem,5) == ".aspx" || RIGHT(cs_uri_stem,4) == ".htm" || RIGHT(cs_uri_stem,1) == "/") && sc_status == "200"
 如圖所示:

 

點擊確定.資料過濾就算大功告成了。

　　（二）、衍生的資料行（Derived Column)，相當於SQL語句中的計算資料行，即根據其它列，按照一定的計算公式，派生出一個新列。在此例中，有三種情況需要用到衍生的資料行：

　　（1）日期列，從log檔案匯入的日期、時間，為兩個獨立的字串（varchar)，而資料庫中的對應欄位為Datetime 型，如果要想建立一種映射，則需要根據log 檔案的Date 、time 欄位，派生出一個Datetime 型的欄位。

　　（2）時間段，同理log 檔案中的Time 為一字串，需要取出其中的“小數（hour),才能與dimTime 中的lngHour 相匹配。

　　（3）IP，我們想根據客戶IP，確定他所在國家、省市、地區。要達到這一需求，我想並不需要IP完全符合，只要IP的前三段匹配，就可以確定了（沒有考證過，個人感覺而已，如不妥，請指正），所以需要派生出一個ipSegment =IP的前三段，以此映射他所在的地區。

　　同理，從工具箱中，將“衍生的資料行”組件拖到“條件拆分”組件的下方，再將“條件拆分”組件下方的綠色箭頭拖到“衍生的資料行”組件上，系統會彈出一視窗，要求選擇條件拆分的的輸出名稱，如圖：

 

　　從下拉式清單方塊中選擇“Form Records”，點擊確定。

　　然後再雙擊“衍生的資料行”組件，開啟“衍生的資料行轉換編輯器”，如圖：

　　這個視窗太眼熟了吧，那不是前面講的“條件性拆分編輯視窗”嗎。是的，非常類似，我就不羅嗦了，按圖上要求，輸入衍生的資料行名稱，選擇衍生類別型，輸入運算式，後面的資料類型、資料長度、精度等屬性，將根據派生運算式自動產生，一般是不允許修改的。

　　（三）、資料類型轉換。在Integration Services 中，資料類型匹配要求是相當嚴格的，尤其是後面要講的尋找（Lookup)組件，資料類型必須絕對匹配，才能Join ，否則將不成功。

　　Integration Services 中的資料類型，它為了相容多種資料來源（比如一般檔案、MssQL、ORACLE、DB2、MYSQL等），在形式上它不同於前面說的任何一種資料來源的資料類型，一旦資料進入Integration Services 包中的資料流中時，資料流程引擎就會將這些列的資料轉換為Integration Services 的資料類型，前面介紹的“條件性拆分”、“衍生的資料行”中的運算式，都是對這種Integration Services類型的資料進行操作。所以如果後面要應用到尋找（Lookup)組件，就必須要對這種資料類型進行轉換，才可以與尋找源（關係型資料庫中的表或視圖）的列匹配。具體操作為：

　　從工具箱中，將“資料轉換”組件拖到視窗上，將上一組件（衍生的資料行）組件下面的綠色箭頭拖此組件上，雙擊開啟“資料轉換組件”，如圖：

 

　　勾選要進行資料類型轉換的列：Date,strDatetime，將它們轉換MSSQL的Datetime 類型。

　　特別說明一下，Integration Services資料類型與其它關係型資料庫的資料類型之間的關係是比較複雜，如果憑空猜想，很難找到它們之間的對應關係，請參考Microsoft 說明文檔，那裡面有非常詳細的說明。Integration Services 資料類型

　　(四)、尋找（Lookup)，類似於Sql 中的Left Join 、Right Join ，一般可以實現兩方面的功能：（1）輸出匹配的項；（2）、輸出無匹配項，這個功能在ETL中應用是相當頻泛的，如果善加利用，可以實現很多功能。前面兩種資料流轉換（衍生的資料行、資料類型轉換）都是為Lookup 鋪路搭橋的。在這個例子，有三個列需要尋找，IP、Date、Time。只要一切準備工作就緒，Lookup 就容易多了。

　　將“尋找(Lookup)”組件拖到視窗中，串連上一組件的綠色箭頭，雙擊開啟“尋找轉換編輯器”，如圖：

　　這可比以前的編輯器，複雜一些了吧，其實也並沒有那麼可怕，如果一般用用，很多地方都按Default 設定，那也是很容易的。但是ETL的效能，在這一步是蠻關鍵的。首先看緩衝模式：

　　完全緩衝：是指在尋找轉換前，先把引用資料集，完全緩衝在記憶體中，供以後尋找時用。

　　部分緩衝：在執行“尋找轉換”時產生引用資料集，並將有匹配的資料行載入到緩衝中，沒有匹配的資料行則丟棄。

　　無緩衝：在執行“尋找轉換”的過程中產生引用資料集，但不載入入緩衝。

　　通過上面的解釋，利弊已經很明顯了，不同的情況，可能需要不同的處理策略，自已權衡吧。

　　連線類型，實際上也很清楚了，就不多說了。

　　指定如何處理無匹配的行：這一選項非常重要，共有四個選項：

　　忽略失敗：就是說遇到無匹配的項，忽略，程式繼續執行。

　　將行定位到錯誤輸出：無匹配的記錄，通過錯誤資料流路徑（紅色箭頭）輸出，供以後人手分析處理。

　　組件失敗：如果遇到無匹配的項，組件立即失敗，程式停止執行。

　　將行定位到無匹配輸出：輸出無匹配的記錄集。此選項通常用於尋找是否有新的記錄產生，如果有新記錄出現，則匯入，已有匹配的記錄集忽略。本例中，IP尋找將會用這一選項，如果遇到一個新IP，則插入到資料倉儲中，否則，就則忽略此記錄，不再重複插入了。

 　　選擇“串連”，如圖：

　　選擇連線管理員IisLog，在表或者視圖拉列框中選擇“dimDate“。

　　切換到“列”，將[可用輸入列]中的“dtDate”拖到[可用尋找列]的“dtDatetime”，兩個欄位間w會連一條直線，表示相互建立串連關係，前面說過，如果這兩列的資料類型不一致，這種關係將無法建立。最後在“可用尋找列”中勾選“lngDateID”，作為輸出。點擊確定，lngDateID 的尋找就完成了。

　　其它兩個，有興趣的朋友可以自動手試試，看能否成功。

　　這樣，資料轉換就算完成了，最後接著上課的資料流目的地，將源列與目標映射起來，如圖：

　　點擊“運行”，夢想中的綠色境界，就出現了。

 

 

 

  

源碼下載：IisLog 源碼下載

 

本 文地 址 ： http://www.fengfly.com/plus/view-168582-1.html