適用環境
- SQL Server 2000 (Enterprise Manager / DTS Designer)
- 來源：Excel 97-2003 (.xls)
- 目標：SQL Server 2000 資料表
逐步操作流程 (Enterprise Manager 圖形介面)
- 開啟 **Enterprise Manager**，展開伺服器群組 → 目標資料庫 → 右鍵 **資料表** → **所有工作** → **導入資料...** 啟動 **DTS 導入/導出精靈**。
- 選擇資料來源： **Microsoft Excel 97-2000** → 瀏覽選擇 `.xls` 檔案 → **下一步**。
- 選擇目的地： **Microsoft OLE DB Provider for SQL Server** → 伺服器：`(local)` 或 IP → 驗證：SQL Server 驗證 (`sa`/密碼) 或 Windows 驗證 → 選擇目標資料庫 → **下一步**。
- 指定資料複製方式： 選擇 **「使用查詢指定要傳輸的資料」** → **下一步** (靈活控制欄位對應、過濾、排序)。
- 建立查詢： 點擊 **「查詢產生器...」** → 來源表清單中選擇 Excel 工作表 (如 `Sheet1$`) → 將所需欄位 **按目標資料表欄位順序** 依序加入右側「選取的欄位」清單 → **下一步**。
關鍵： 欄位順序 **必須與目標資料表定義順序完全一致**，否則資料會寫入錯誤欄位或型別轉換失敗。
- 排序順序 (可選)： 指定 `ORDER BY` 欄位 → **下一步** (通常匯入不需排序，略過)。
- 篩選列 (可選)： 選擇 **「所有列」** → **下一步** (或指定 `WHERE` 條件)。
- 檢視生成的 SQL 查詢： 確認 `INSERT INTO ... SELECT ... FROM [Sheet1$] ...` 語法正確 → **下一步**。
- 選擇目的地資料表： 「來源」顯示 Excel 工作表，「目的地」下拉選擇目標 SQL 資料表 → **下一步**。
- 排程/執行選項： 選擇 **「立即執行」** → **下一步** (或儲存為 DTS 套件排程執行)。
- 完成精靈： 檢視摘要 → **完成**。DTS 套件執行，進度對話框顯示傳輸列數/錯誤。
常見問題與解決
| 問題 | 原因 | 解決 |
|---|
| 資料截斷/型別不符錯誤 | Excel 欄位資料長度超過目標欄位定義、或文字寫入數值欄位 | 修正目標表結構 (擴大長度)、或查詢中用 `CAST/SUBSTRING` 預處理、或 Excel 預先清洗 |
| 首行非標題導致欄位名錯誤 | Excel 第一行為資料非標題 | 精靈「資料來源」步驟取消勾選「第一行包含欄位名稱」，欄位將自動命名 F1, F2... 需在查詢產生器手動對應 |
| 混合型態欄位讀取為 NULL | Jet OLEDB 掃描前 8 行猜測型態，後續不同型態資料被忽略 | 連接字串加 `IMEX=1` (精靈無法直接設定) → 改用「查詢指定」模式手寫 `SELECT * FROM [Sheet1$]` 並確保登錄 `TypeGuessRows=0` |
| 中文欄位名/資料亂碼 | 代碼頁/排序規則不匹配 | 確認 SQL Server 排序規則支援中文 (Chinese_PRC_CI_AS)，Excel 儲存為 Unicode (另存新檔 → 工具 → Web 選項 → 編碼 UTF-8) 或連接字串指定代碼頁 |
| 大量資料匯入極慢 | DTS 預設逐列 Commit、無批次、無鎖定優化 | 改用 `bcp` / `BULK INSERT` / SSIS (SQL 2005+) / `SqlBulkCopy` (程式碼) 獲得數量級效能提升 |
進階：儲存為 DTS 套件以便重複使用
精靈最後一步可選 **「儲存 DTS 套件」** → 存入 **SQL Server (msdb..sysdtspackages)** 或 **結構化儲存檔 (.dts)** → 之後可在 **Enterprise Manager → 資料轉換服務 → 區域套件** 右鍵 **執行** 或排程作業 (`sp_start_job`)。
現代化替代方案 (SQL Server 2005+ / 當前版本)
- SQL Server Import and Export Wizard (SSIS 引擎)： `開始 → SQL Server → 匯入及匯出資料`，功能更強、支援 .xlsx、資料流轉換、錯誤輸出、資料分析。
- OPENROWSET / OPENDATASOURCE (Ad-hoc 分散式查詢)：
INSERT INTO dbo.TargetTable SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\data.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
需 `sp_configure 'Ad Hoc Distributed Queries', 1`、安裝 ACE Provider。
- SSIS 套件專案 (Visual Studio / SSDT)： 企業級 ETL，參數化、部署模型 (Project/Package)、Logging、Event Handling、平行執行。
- C# / Python 腳本 + SqlBulkCopy / pandas + pyodbc： 彈性最高、易版控、CI/CD 整合。
結語： DTS 精靈是 SQL 2000 時代的經典工具，理解其「來源 → 目的地 → 查詢/對應 → 執行」核心邏輯，有助於掌握後續 SSIS/現代 ETL 工具的同源設計思維。生產環境大量資料匯入請務必使用批次/大量載入機制。
