Integration Services：資料流程任務（下）—— 進階轉換元件實戰

承上篇〈資料流程任務（上）〉建立基本 ETL 匯入，本文進入核心——資料流程轉換。實際資料（如 IIS Log）包含雜訊（圖片、CSS、JS、失敗請求），需透過轉換元件過濾、衍生、查找，萃取高價值資訊（IP/PV 分析、時段流量、地理分佈）。

實作場景：IIS Log 點擊率分析

需求：

1. 分析一段時間內網站點擊趨勢、週末/假日流量
2. 分析一天內各時段（小時）壓力
3. 了解客戶來源國家/地區

建立星狀結構資料模型：

-- 事實表 IISLog、IP 維度表 dimIP、日期維度 dimDate、時間維度 dimTime
CREATE TABLE [dbo].[IISLog] (...);
CREATE TABLE [dbo].[dimIP] (...);
CREATE TABLE [dbo].[dimDate] (...);
CREATE TABLE [dbo].[dimTime] (...);

步驟一：條件分割 —— 過濾雜訊

拖曳 條件分割 元件，連接平面檔案來源。編輯器中設定輸出名稱「Form Records」，條件運算式：

(RIGHT(cs_uri_stem,5) == ".aspx" || RIGHT(cs_uri_stem,4) == ".htm" || RIGHT(cs_uri_stem,1) == "/")
&& sc_status == "200"

僅保留網頁請求（.aspx/.htm/根目錄）且狀態碼 200 的記錄。

步驟二：衍生欄位 —— 計算新欄位

拖曳 衍生欄位，連接條件分割輸出「Form Records」。新增三個衍生欄位：

步驟三：資料類型轉換 —— 嚴格匹配

SSIS 資料類型嚴格，查找 元件要求來源與參考欄位類型完全一致。拖曳 資料轉換，將 Date、strDatetime 轉為 DT_DBTIMESTAMP（對應 SQL Server datetime）。

步驟四：查找 —— 關聯維度表

拖曳三個 查找 元件分別處理 IP、日期、時間。以日期查找為例：

1. 緩存模式選 完全快取（維度表小、全量比對）
2. 連線類型：OLE DB 連線管理員
3. 無符合列處理：忽略失敗（或導向錯誤輸出）
4. 選擇參考表 dimDate，將輸入欄位 dtDate 對應 dtDatetime，輸出 lngDateID

IP 查找建議選「將列重新導向至無符合輸出」，新 IP 可另存入維度表。

完成：對應目的地欄位

連接 OLE DB 目的地，對應來源與目標欄位：

執行，全綠燈通過：

原始碼下載：IisLog.rar