SSIS 實戰教學 (三)：Foreach 循環容器批量匯入 Excel 檔案完整指南

• Foreach File Enumerator：遍歷資料夾檔案 (支援萬用字元 *.xls、正則)。
• Foreach ADO Enumerator：遍歷 ADO Recordset 列。
• Foreach Variable Enumerator：遍歷陣列/清單變數。
• Foreach SMO Enumerator：遍歷 SMO 物件 (資料庫、資料表等)。

圖 1：Foreach 循環容器編輯器 - 集合頁籤

完整實作步驟

1. 準備範例檔案

• 重新命名既有 Product.xls → Product1.xls。
• 建立 Product2.xls，複製 Product1.xls 中 ProductID < 800 的列，**工作表名稱必須一致** (如 Sheet1$)。
• 放入目標資料夾 F:\ExcelImports\。

2. 建立 SSIS 專案與封裝

• BIDS/SSDT → Integration Services 專案 → 新增封裝 ForeachInput.dtsx。

3. 佈置控制流：Foreach 循環容器

1. 工具箱拖入 **Foreach Loop Container** 至設計面。
2. 雙擊編輯 → **集合** 頁籤：
   • 列舉器：**Foreach File Enumerator**
   • 資料夾：F:\ExcelImports\
   • 檔案：*.xls
   • 擷取檔案名稱：**完整路徑** (Fully qualified)
3. **變數對應** 頁籤 → 新增使用者變數 User::CurrentFilePath (String) → 索引 0。

   

   圖 2：變數對應設定

4. 重用既有資料流 (複製貼上法)

1. 開啟既有封裝 OutputAndInput.dtsx。
2. 選取 **資料流程工作** (含 Excel 來源、衍生欄位、OLE DB 目的地) → Ctrl+C。
3. 回到 ForeachInput.dtsx，在 Foreach 容器**內部**貼上 (Ctrl+V)。
4. 連接管理器 (Excel、OLE DB) 亦會自動複製。

5. 關鍵：Excel 連接管理器動態繫結變數

1. 點選方案總管/連接管理器區的 **Excel 連接管理器**。
2. 屬性視窗 → Expressions → 點選省略號。

   

   圖 3：屬性運算式編輯器入口

3. 屬性選 ExcelFilePath → 運算式輸入 @[User::CurrentFilePath]。

   

   圖 4：動態繫結檔案路徑運算式

4. 將 Excel 連接管理器 **DelayValidation = True** (延遲驗證)，避免設計階段因變數為空報紅錯誤。

6. 調整衍生欄位轉換邏輯 (區分批次)

• 編輯「衍生欄位」元件，將 ProductNumber、Name 衍生邏輯由 +1 改為 +2，以區別匯入批次。

  

  圖 5：衍生欄位調整

7. 執行與驗證

• 右鍵封裝 → **執行封裝**。
• 輸出視窗顯示 Foreach 容器迴圈執行 2 次 (綠色勾勾 ×2)。
• 查詢目標表 dbo.Product，確認匯入 2 批資料，衍生欄位值正確 (+1 批次、+2 批次)。

圖 6：成功執行結果

8. 進階除錯：資料檢視器

1. 右鍵資料流路徑 (綠色箭頭) → **啟用資料檢視器**。
2. 加入 → 選擇欄位 → 確定。
3. 再次執行，封裝暫停於資料檢視器視窗，即時檢視每列資料、衍生欄位結果。

   

   圖 7：資料檢視器介面

4. 點擊綠色播放鈕繼續執行。

圖 8：即時資料預覽

關鍵技術點總結

擴充應用場景

• 檔案歸檔：Foreach 容器內加入「檔案系統工作」 → 移動已處理檔案至 Archive\ 並重命名加時間戳記。
• 錯誤隔離：資料流錯誤輸出導向錯誤表/檔案，Foreach 迴圈繼續處理下一檔。
• 動態工作表名：若各檔工作表名不同，需用 Script Task 讀取 Excel Schema 取得第一個工作表名，存入變數，Excel 來源設定 OpenRowset 運算式繫結該變數。
• 參數化資料夾路徑：使用專案/封裝參數 (Project/Package Parameters) 取代硬編碼路徑，支援多環境部署 (Dev/Test/Prod)。

結語

Foreach 循環容器 + 變數對應 + 連接管理器運算式 = SSIS 批量處理同結構檔案的**黃金組合**。掌握「動態繫結」與「延遲驗證」兩大核心技巧，即可構建彈性、可維護、可擴展的自動化 ETL 流程。搭配資料檢視器除錯、檔案歸檔、錯誤隔離、參數化部署，打造企業級資料整合管線。