SSIS Lookup 元件完整教學：快取模式選擇與 Upsert 實戰

Lookup (查閱) 是 SSIS 資料流程中實現「查詢參考表、決定插入/更新」的核心元件。本文以「舊系統客戶資料同步至新系統」為例，完整示範封裝結構、快取模式設定與效能調優。

案例情境

• 來源：DEMO_LK_LegacyCustomer (舊系統)，含 5 筆資料。
• 目標：DEMO_LK_Customer (新系統)，初始 3 筆。
• 規則：依 CustomerID 比對，存在則更新 (公司名、姓名、地址)，不存在則插入。

-- 建表腳本 (略，含 5 筆測試資料，ID 1、3 需更新，4、5 需插入)

封裝結構概覽

1. Data Flow Task: DF_Lookup
2. 來源：OLE DB Source 讀取 LegacyCustomer。
3. 核心：Lookup 元件 (命名 LKP_Customer)，連接目標資料庫 Customer 表，比對鍵 CustomerID。
4. 分流：
    - Match Output → OLE DB Command 執行 UPDATE。
    - No Match Output → OLE DB Destination 執行 INSERT。

Lookup 快取模式詳細比較

Full Cache 設定要點 (本案例採用)

1. Connection Manager：指向目標資料庫。
2. General 頁：Cache Mode = Full Cache，Connection Type = OLE DB。
3. Columns 頁：勾選需輸出的參考欄位 (公司名、姓名、地址)，別名加前綴 Ref_ 避免衝突。
4. Advanced 頁：
    - MaxMemoryUsage：預設 0 (自動)，可設定上限 MB。
    - CacheConnectionManager：可改用 Cache Connection Manager 將快取持久化至 .caw 檔，跨封裝/跨執行重用，極大提升重複執行效能。

效能調優實戰建議

• 參考表 CustomerID **必須有索引** (最好是叢集索引)，否則 Partial/No Cache 模式每列查詢會掃表。
• 來源資料預先依 CustomerID 排序，可提升 Partial Cache 命中率 (連續相同鍵命中快取)。
• 大量資料 (千萬列) 建議：
  ① 來源分批 (如每批 10 萬列) 執行封裝。
  ② 使用 Cache Connection Manager 預先產生 .caw 檔 (可另一個封裝專門產生)。
  ③ Lookup 後改用 OLE DB Destination (Fast Load) 批次寫入，而非 OLE DB Command 逐列更新。

常見錯誤排查

• 資料型別不符：來源與參考欄位型別、長度、精度必須完全一致，否則比對失敗導致誤判為 No Match。
• 空值比對：Lookup 預設 NULL != NULL，若鍵值含 NULL 需在來源端用 ISNULL 轉換或啟用 TreatNullsAsEqual (需 SQL 2012+)。
• 記憶體不足：Full Cache 參考集過大導致 OutOfMemoryException，改用 Partial Cache 或升級記憶體/啟用 .caw 快取檔。