SQL Server 2005 Integration Services：建立最簡單模糊比對封裝

前言

SQL Server 2005 SSIS 引入 模糊查找 轉換，能以近似比對方式整合髒亂資料（如地址拼音差異、縮寫、拼字錯誤）。本文示範從零建立一個最小可行性模糊比對封裝。

前置需求

• SQL Server 2005 + Business Intelligence Development Studio (BIDS)
• 來源資料：含字串欄位的資料表（如客戶地址）
• 參考資料：標準化、乾淨的主資料表

逐步建立封裝

步驟 1：建立專案與資料流程

1. 開啟 BIDS → 新增專案 → Integration Services 專案
2. 切換 Data Flow 索引標籤 → 設計面會提示「加入資料流程任務」→ 點擊建立

步驟 2：配置來源與目的地

• 來源： 從工具箱拖曳 OLE DB 來源 → 設定連線管理員 → 選取含「髒資料」的輸入資料表
• 目的地： 拖曳 OLE DB 目的地 → 設定連線 → 稍後將輸出導向新資料表

步驟 3：加入模糊查找轉換

1. 從工具箱拖曳 模糊查找 到資料流程設計面，介於來源與目的地之間
2. 連接綠色箭頭：來源 → 模糊查找 → 目的地

步驟 4：設定模糊查找參考資料

1. 雙擊模糊查找元件開啟 UI
2. 參考資料表： 選擇連線管理員與標準化主資料表（如 dbo.CleanAddresses）
3. 欄位對應： 切換到 Columns 索引標籤 → 將「可用輸入欄位」（來源）拖曳至「可用查找欄位」（參考表）
   例如：Input.StreetAddress → Lookup.Address
4. 勾選所有查找欄位的核取方塊 → 確定

步驟 5：設定目的地資料表

1. 雙擊 OLE DB 目的地 → 選擇連線管理員
2. 資料存取模式： 選「資料表或檢視」→ 點擊 新增 自動建立目的地資料表
3. 對應： 確認來源欄位正確對應至目的地欄位 → 確定

步驟 6：執行與驗證

1. 方案總管中右鍵封裝 → 執行
2. 設計面顯示綠色勾號與資料列計數，代表成功
3. 可在來源與模糊查找間的連接線右鍵加入 資料檢視器，即時觀察比對結果

核心運作機制

錯誤容忍索引 (Error-Tolerant Index, ETI)

• 首次執行時，模糊查找會在 tempdb 建立 ETI，這是比對的核心資料結構
• 參考資料量大時，ETI 建立會有明顯延遲（僅首次）
• 後續執行若參考資料未變更，ETI 會快取重用

輸出欄位說明

調整比對敏感度

在模糊查找編輯器的 進階 索引標籤可調整：

• 相似度閾值： 預設 0.8，提高可減少誤比對但增加遺漏
• 最大比對數： 每筆輸入最多回傳幾筆候選（預設 1）
• Token 分隔符： 自訂字串切詞規則（如中文需特殊處理）

常見問題與解決

結語

SSIS 模糊查找是資料整合 ETL 中處理髒亂資料的利器。掌握 ETI 機制、參數調整與中文資料前處理，能顯著提升主資料管理（MDM）與資料品質專案的成功率。