ETL 學習筆記之三：MS SQL DTS 深度解析與實戰評價

SQL Server 2000 的 DTS (Data Transformation Services) 是微軟早期 ETL 解決方案的集大成者，雖已被 SSIS (SQL Server 2005+) 取代，但其核心架構思想、組件模型、可視化設計 仍具備極高教學與遷移參考價值。本文從概念、特徵、核心對象 (包/任務/連接/轉換/工作流/元數據/數據驅動查詢/查找查詢/事務) 九大維度深度拆解 DTS，並給出生產級評價與遷移建議。

一、核心概念：DTS 即 ETL

「從完全不同源的數據抽取、轉換並合併到單個或多個目的地。主要應用於企業數據倉庫數據抽取過程，完成從源數據庫/文件到數據倉庫的抽取和轉換。」

這正是 ETL (Extract-Transform-Load) 的標準定義。DTS 以圖形化設計器 + COM 組件模型 + 緊密集成 SQL Server 實現了這一流程。

二、四大核心特徵

三、九大核心對象深度拆解

3.1 包 — 最小調度單元

• 由任務通過工作流連接構成的有向無環圖 (DAG)。
• 可嵌套： 包中的任務可執行另一個包，實現模塊化復用 (如公共清洗邏輯封裝為子包)。
• 調度對象直接是包，嵌套包保證任務級復用。

3.2 任務 — 最小工作流單元

3.3 數據連接 — 定義源與目標

• 標準數據庫： SQL Server, Access, Oracle, DB2, ODBC/OLE DB 通用。
• 文件類： Excel, CSV, 文本 (固定寬度/分隔符)，HTML。
• 特殊： 文件連接 (需定義列分隔、編碼、行尾)。
• 限制： 無原生 Java/JDBC 支援 (純 Windows 技術棧)。

3.4 轉換 — 列級數據加工

轉換作用於列，四種模式遞增能力：

1. 直接複製： 源列 → 目標列，類型隱式轉換。
2. 簡單轉換： 內建函數 (格式化、子串、類型轉換、NULL 處理、大小寫)。
3. ActiveX 腳本： VBScript/JScript 實現複雜邏輯 (正則、調用 COM、多列運算)。
4. 自定義 COM： C++/VB6 編寫高性能組件，註冊後在設計器中可選。

選型建議： 80% 場景用 1+2 解決；集成應用用 3；核心高性能邏輯用 4。

3.5 工作流 — 任務編排與錯誤處理

• 三種連接線：成功 (綠)、失敗 (紅)、完成 (藍)。
• 支援腳本控制工作流決策 (如動態判斷下一步驟)。
• 純數據抽取場景較少用，但含郵件/文件傳輸/外部程序調用時必不可少。

3.6 元數據 — 預定義與擴展

• 基礎用途：預定義目標表結構 (列名、類型、約束)，實現「先建模後生成」。
• 擴展用途：存儲業務規則、轉換映射文檔、數據血緣信息。

3.7 數據驅動查詢任務 — 高級轉換模式 (難點/亮點)

根據源數據特徵動態決定目標操作 (INSERT/UPDATE/DELETE/查詢)，支援參數綁定、腳本計算參數、批量處理、多階段代碼 (行轉換/轉換失敗/插入成功/插入失敗/批次完成/抽取完成)。

典型場景： 多表分區存儲 — 根據源數據時間戳/業務類型，動態決定寫入哪張分區表、是新增還是更新。

-- 概念性邏輯
FOR EACH source_row:
    IF EXISTS (SELECT 1 FROM Target_Table WHERE Key = source.Key)
        UPDATE Target_Table SET ... WHERE Key = source.Key;  -- 參數綁定
    ELSE
        INSERT INTO Target_Table ...;                        -- 參數綁定
    END IF
    -- 批次提交、錯誤處理、階段代碼觸發

3.8 查找查詢 — 跨連接引用

• 在轉換/數據驅動查詢中，對非源非目標的第三方連接執行查詢/存儲過程。
• 如：轉換時實時查詢匯率表、代碼表、主數據服務，豐富目標行。
• 作為擴充功能實現復雜查詢操作。

3.9 多任務事務支撐

• 支援順序/並行執行步驟。
• 支援檢查點重啟 (Checkpoint Restart) — 失敗處恢復而非全量重跑。
• 事務最終依賴底層數據源是否支援 (SQL Server/Oracle 支援，文本/Excel 不支援)。

四、生產級評價與遷移建議

遷移路徑：DTS → SSIS (SQL Server 2005+)

1. 遷移向導： SSIS 包含 DTS 遷移向導，可自動轉換 80%+ 基礎包 (連接、任務、簡單轉換)。
2. 手動重寫： ActiveX 腳本 → C#/VB.NET Script Task；COM 任務 → 自定義組件/Script Component；數據驅動查詢 → 合併/條件分割/查找轉換組合。
3. 架構升級： 單包 → 專案部署模型 (Project Deployment) + SSISDB 目錄 + 參數化/環境變量 + 內建日誌/報表/REST API。
4. 並行驗證： 新舊並行運行一段時間，核對行數、檢查和、業務指標一致後再切流。

總結

DTS 以 OLE DB 統一訪問、包/任務/轉換 分層架構、可視化設計 + COM 擴展、檢查點事務、Agent 調度 構建了經典的 Windows 平台 ETL 基因。雖已退出歷史舞台，但其核心抽象 (Source → Transform → Destination)、工作流編排思想、數據驅動模式、元數據驅動設計 直接繼承至 SSIS、Azure Data Factory、甚至現代 Python Airflow/dbt 架構中。學習 DTS，不是為了維護舊系統，而是為了理解 ETL 設計模式的源頭邏輯，在新技術棧中做出更本質的架構決策。