使用 DTS 批量導入多個 CSV 文件到 SQL Server 完整教程

概述

在實際開發中，經常需要將儲存在多個文本文件（如 CSV）中的數據批量導入 SQL Server 資料表。本文詳細介紹如何結合批處理文件與 DTS（Data Transformation Services），從指定目錄自動讀取多個文件並將數據匯入資料庫。

準備測試環境

1. 建立來源文件

在 C:\MyImport 目錄下建立三個 CSV 文件：

C:\MyImport\a.csv
1, MAK, A9411792711, 3400.25
2, Claire, A9411452711, 24000.33
3, Sam, A5611792711, 1200.34
4, Wright, A5611792711, 1200.34
5, Richard, G561d792755, 1223.34
6, ValarIE, B5611792788, 1240.32

C:\MyImport\b.csv
11, Rubon, 9671792711, 400.14
22, Mike, 9418952711, 4000.56
39, Hsu, 75611792511, 1230.00

C:\MyImport\c.csv
69, Lucy, 8411992710, 305.11
45, Grace, 3413452713, 246.52
33, Saint, 5461795716, 1278.70

2. 建立目標資料庫與資料表

CREATE DATABASE Bank;
GO
USE Bank;
GO
CREATE TABLE Account (
    [ID] INT,
    Name VARCHAR(100),
    AccountNo VARCHAR(100),
    Balance MONEY
);
GO
CREATE TABLE LogTable (
    ID INT IDENTITY(1,1),
    Status VARCHAR(500),
    ImportedDate DATETIME DEFAULT GETDATE()
);
GO
-- 建立匯入專用登入
USE master;
GO
EXEC sp_addlogin 'importuser', 'import', 'Bank';
GO
USE Bank;
GO
EXEC sp_adduser 'importuser';
GO
EXEC sp_addrolemember 'db_datareader', 'importuser';
GO
EXEC sp_addrolemember 'db_datawriter', 'importuser';
GO

建立 DTS 套件

1. 建立全域變數：在 DTS 設計器中新增三個全域變數：FileName、ServerName、DatabaseName。
2. 建立連線：分別建立「Text File (Source)」連線（指向 CSV 文件）與「SQL Server」連線（指向目標資料庫）。
3. 建立資料轉換任務：將來源文字文件的欄位對應至 Account 資料表的對應欄位（ID、Name、AccountNo、Balance）。
4. 設定動態屬性任務：
   • 針對 InputFile 連線：將 Catalog 設為全域變數 DatabaseName，DataSource 設為 FileName。
   • 針對 SQLServer 連線：將 DataSource 設為全域變數 ServerName。
5. 建立工作流程：在「動態屬性任務」與「InputFile 連線」之間加入「成功時」工作流程。
6. 記錄匯入日誌：新增「執行 SQL 任務」，SQL 陳述式為 INSERT INTO LogTable (Status) VALUES (?)，參數 1 綁定全域變數 FileName。
7. 連接任務流程：在「SQLServer 連線」與「執行 SQL 任務」之間加入「成功時」工作流程。
8. 儲存套件：將 DTS 套件儲存為結構化儲存檔案（*.dts），以便後續透過批處理或排程呼叫。

自動化批量匯入

撰寫批處理檔（*.bat）遍歷目錄下所有 CSV 文件，依序設定全域變數 FileName 並呼叫 dtsrun 執行套件，即可實現無人值守的多文件匯入。

重點提示

• 確保 DTS 套件中的文字文件連線「第一列包含欄名」選項與實際文件一致。
• 全域變數 ServerName、DatabaseName 可在批處理中透過 /A 參數傳入，提高複用性。
• 若文件編碼非預設 ANSI，需在文字文件連線屬性中指定正確的代碼頁。