SSIS 批次處理設計模式：以 Foreach 迴圈容器實現可重啟的資料倉庫增量載入

業務場景

每日載入真實交易記錄至資料倉庫，需支援：

• 將來源資料分批次處理（避免長交易鎖定）。
• 批次層級交易控制（成功提交、失敗回滾）。
• 失敗後可從失敗批次重啟（冪等性）。
• 記錄處理狀態（未處理/處理中/已完成/失敗）。

SSIS 套件架構

六大核心元件

1. Get Batch List (執行 SQL 任務)：
   SELECT BatchID, StartDate, EndDate FROM Staging.BatchControl WHERE Status = 'Pending' ORDER BY BatchID
   將結果集存入 Object 型別變數 User::BatchList。
2. Process Batch Loop (Foreach 迴圈容器)：
   列舉 User::BatchList，每筆記錄對應 User::CurrentBatchID、User::BatchStart、User::BatchEnd 變數。
3. Transaction Container (序列容器)：
   設定 TransactionOption = Required，內含任務同享一個交易，任一失敗整批回滾。
4. Append Batch to Sales History (執行 SQL 任務)：
   INSERT INTO DW.SalesHistory ... SELECT ... FROM Staging.SalesRaw WHERE TxnDate BETWEEN ? AND ? AND BatchID = ?
   參數對應迴圈變數。
5. Compute Aggregation (執行 SQL 任務)：
   針對該批次重新計算彙總表（月銷售額、商品排名等），MERGE 更新 DW 聚合表。
6. Mark Batch as Processed (執行 SQL 任務)：
   UPDATE Staging.BatchControl SET Status = 'Completed', ProcessedAt = GETDATE() WHERE BatchID = ?

錯誤處理與重啟機制

• Foreach 容器屬性 FailPackageOnFailure = True。
• 事件處理器 OnError 更新 BatchControl.Status = 'Failed'、記錄錯誤訊息。
• 重啟時僅撈取 Status IN ('Pending','Failed') 的批次，天然冪等。

效能調優要點

• 批次大小建議 10,000~100,000 列，平衡交易日誌與平行度。
• 來源表建立 (BatchID, TxnDate) 複合索引支援篩選。
• 目的地表啟用 TABLOCK、最小化日誌（簡單復原模式/大量記錄模式）。