前一篇文章Integration Services：數據流任務（上），介紹了如何創建一個簡單的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 的查找就完成了。
其它兩個，有興趣的朋友可以自動手試試，看能否成功。
這樣，數據轉換就算完成了，最後接著上課的數據流目標，將源列與目標映射起來，如圖：
點擊“運行”，夢想中的綠色境界，就出現了。
源碼下載：http://files.cnblogs.com/invinboy/IisLog.rar
