需求
將目錄 (含子目錄) 下結構相同的多個 Excel 檔批次匯入 SQL Server 2005。
環境
- SQL Server 2005 + Business Intelligence Development Studio (BIDS)
- Excel 檔放置
F:\excel\，欄位 a,b,c,d 相同。
步驟總覽 (20 步)
- 準備測試 Excel 檔 (含標題列)，複製多份至目標資料夾。
- 開啟 BIDS → 新增「商業智慧專案」→「Integration Services 專案」。
- 工具箱拖入 Foreach 迴圈容器 至控制流程。
- 編輯容器 → 「集合」頁籤：
列舉器：**Foreach File Enumerator**
資料夾：F:\excel\
檔案：***.xls**
勾選「完整檔名」
「變數對應」頁籤 → 新增變數 User::xlspath (String)，索引 0。
- 容器內拖入 資料流程工作。
- 切換「資料流程」頁籤 → 拖入 Excel 來源。
- 編輯 Excel 來源 → 「新增」連線管理員 → 選取任一測試 Excel → 選擇工作表。
- 拖入 OLE DB 目的地 → 連線至目標資料庫/資料表 (預先建立同結構表
tt)。
- 將 Excel 來源綠色箭頭連接到 OLE DB 目的地 → 欄位對應 (同名自動對應)。
- 關鍵步驟：回到「連線管理員」區 → 右鍵 Excel 連線管理員 → 「屬性」。
- 「運算式」屬性 → 「...」 → 新增：
屬性：**ExcelFilePath**
運算式：@[User::xlspath]
- 此時會出現驗證錯誤 (設計階段找不到檔案) → 回到控制流程 → 右鍵 Foreach 容器 → 「屬性」→ DelayValidation = True。
- 按 F5 執行，所有 Excel 即批次匯入資料庫。
- (選擇性) 建立 SQL Server Agent 作業排程執行 .dtsx 套件，實現自動化。
關鍵技術點
- Foreach File Enumerator 遍歷檔案系統，將完整路徑存入變數。
- 連線管理員運算式 動態覆寫
ExcelFilePath，實現單一資料流程處理多檔。
- DelayValidation 關閉設計階段驗證，避免變數未賦值時的錯誤。
常見問題
- Excel 64 位元驅動缺失 → 安裝
AccessDatabaseEngine_X64.exe。
- 欄位型別不符 → 進階編輯器手動調整輸出資料行型別。
- 首列非標題 → 連線管理員「第一列有資料行名稱」取消勾選。
現代替代方案
- SQL Server 2016+：
OPENROWSET + OPENJSON / POLYBASE。
- Azure Data Factory / SSIS 專案部署模型 (Project Deployment Model)。
- Python/pandas + SQLAlchemy 腳本自動化，彈性更高。
