C# 使用 DTS 導入 Excel 資料至 SQL Server 完整實作指南

概述

SQL Server 的資料轉換服務（DTS）提供圖形化介面與程式化控制，適合將 Excel 資料批次匯入資料庫。本文記錄建立 DTS 套件流程、C# 呼叫執行方式，以及實務中常見的三大問題與解決方案。

建立 DTS 套件步驟

1. 開啟 SQL 企業管理員 → 工具 → 資料轉換服務 → 匯入資料，依嚮導建立套件，可選擇儲存至資料庫。
2. 或在左側樹狀結構選擇「資料轉換服務」→「本地套件」→ 新建套件：拖曳 Excel 來源與 SQL 目的地，新增「資料轉換任務」，設定來源/目的欄位對應。注意：含主鍵的資料表需取消「啟用識別插入」選項。

C# 程式碼呼叫 DTS 套件

private void RunDTS()
{
    DTS.Package2Class package = new Package2Class();
    object p = null;
    // 從 SQL Server 載入套件（Windows 驗證）
    package.LoadFromSQLServer("ws-victor-03", null, null,
        DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
        null, null, null, "TestDTS", ref p);
    // 動態指定 Excel 檔案路徑
    package.Connections.Item(1).DataSource = @"dts.XLS";
    package.Execute();
}

實務問題與解決方案

問題 1：需插入 Excel 中不存在的欄位（如匯入時間、全域變數）

在「資料轉換任務」的屬性 → 「轉換」頁籤中撰寫 ActiveX Script (JScript/VBScript) 進行欄位映射與計算：

DTSDestination("ICC") = DTSSource("SIM Number");
DTSDestination("IMSI") = DTSSource("IMSI");
// ... 其他欄位對應 ...
// 使用全域變數
DTSDestination("MailFormID") = DTSGlobalVariables("MailFromID").Value;
// 產生目前日期字串
DTSDestination("ReceiveDate") = new Date().getYear() + "-" + 
    (new Date().getMonth()+1) + "-" + new Date().getDate();
return DTSTransformStat_OK;

C# 端設定全域變數：

foreach (GlobalVariable gv in package.GlobalVariables)
{
    if (gv.Name == "MailFromID") package.GlobalVariables.Remove(gv.Name);
}
package.GlobalVariables.AddGlobalVariable("MailFromID", MailFromID);

問題 2：數值型資料匯入後變成科學記號（如 1.23131E12）

根因為 Excel 欄位格式為「一般」或「文字」，DTS 推斷型別錯誤。解決方式：

• 在 Excel 來源連線屬性中，將該欄位資料型別強制設為 DT_STR 或 DT_WSTR。
• 或在轉換腳本中使用 CStr() / FormatNumber() 強制轉為字串再由 SQL 端轉型。

問題 3：匯入過程中需查詢 SQL Server 取得關聯資料

在 DTS 套件中新增「執行 SQL 任務」：

SELECT AddressList.* FROM AddressList WHERE (ID = ?)

設定「輸入參數」綁定來源欄位，「輸出參數」對應目的地欄位，即可在資料流中即時查詢並帶入。

總結

DTS 雖屬舊技術（已被 SSIS 取代），但在 SQL Server 2000/2005 環境或維護舊系統時仍具實用價值。掌握「動態連線字串」、「全域變數傳遞」、「ActiveX Script 欄位轉換」與「SQL 任務參數化」四大核心技巧，即能靈活處理多數匯入情境。