SQL Server 2005 SSIS/DTS 疑難雜症完整 FAQ：從封裝設計到部署執行的 30+ 核心問題解析

前言

SQL Server 2005 將 DTS 重寫為 **SSIS (SQL Server Integration Services)**，架構全面改為 Managed Code、專案部署模型、SSISDB 目錄。遷移與新開發過程中，開發者常遇到設計介面限制、資料流元件行為、運算式語言、遷移相容性、部署模型等疑難雜症。本文彙整 30+ 核心問題，提供官方解釋與實戰對策。

一、封裝設計與開發環境

Q1: 為何匯入/匯出精靈處理數千張表會失敗？

A：精靈建立單一資料流任務，包含所有來源/目的地。SSIS 資料流架構對元件數量有擴展性限制。對策：拆分為多個封裝、使用動態封裝生成 (BIML/腳本任務)、或直接開發 SSIS 專案而非依賴精靈。

Q2: 為何精靈不支援複製資料庫物件 (資料表、索引、SP)？

A：精靈定位為**資料移動**工具 (ETL)，非**管理操作**工具。物件複製屬結構描述部署範疇。對策：使用「複製資料庫精靈」 (SMO/腳本產生)、SSDT 專案部署、Redgate SQL Compare 等專用工具。

Q3: 為何不能在 SSMS 中設計封裝？

A：SSMS 定位為**管理部署執行環境** (封裝儲存、執行、記錄、列舉遠端封裝)。設計環境為 **BI Development Studio (BIDS) / SSDT (SQL Server Data Tools)**，支援方案/專案組織、偵錯、版控整合。

Q4: 什麼是資料來源與資料來源檢視？它們與連線管理員有何關係？

A：

• 資料來源：專案層級共用物件，定義 OLE DB/ADO.NET 連線字串、友好名稱、描述
• 資料來源檢視 (DSV)：資料來源的關聯式結構描述子集，支援命名查詢、計算欄、虛擬關聯、友好名稱
• 連線管理員：封裝層級物件，**設計時**可參考資料來源/DSV，**執行時**獨立序列化所有中繼資料，不依賴專案物件

**優勢**：多個封裝共用單一 DSV 定義 (如星狀結構維度)，確保一致性；部署後封裝獨立執行，無外部相依。

Q5: 為何沒有 Excel/Access 專用資料來源？

A：使用標準 OLE DB 連線管理員 (Provider: Microsoft.ACE.OLEDB.12.0 / Microsoft.Jet.OLEDB.4.0) 即可。精靈會自動建立對應連線管理員。

Q6: WMI 資料工作 vs WMI 事件工作 差異？

特性	WMI 資料工作	WMI 事件工作
用途	查詢系統管理資料 (CPU、磁碟、服務狀態)	訂閱系統事件 (檔案建立、服務啟動、閾值觸發)
控制流角色	前置條件檢查 (如磁碟空間足夠才執行 ETL)	事件驅動觸發 (如新檔案到達啟動資料流)
輸出	WQL 查詢結果集 → 變數/記錄	事件觸發 → 工作流程分支

二、運算式語言與腳本元件

Q7: 為何運算式語言不是 T-SQL 或 VB？

A：資料流引擎需**高效能、強型別、緩衝區導向**運算。T-SQL 為集合導向、VB 為解釯執行，皆不符。SSIS 運算式語言：

• 支援位元運算子 (位遮罩處理)
• 嚴格型別系統 (對應資料流緩衝區型別)
• 無副作用、可並行化

需複雜邏輯時，使用 **腳本元件 (C#/VB.NET)** 完整存取 .NET Framework。

Q8: 為何部分 T-SQL 函數不在運算式語言中？

A：資源限制，優先實作高頻函數。可透過腳本元件呼叫 .NET 對應方法，或回饋給產品團隊納入後續版本。

Q9: 為何排序順序需在來源配接器設定，而非下游轉換？

A：資料流引擎依賴**中繼資料傳播** 最佳化下游元件 (合併聯結、合併、查閱需排序輸入)。來源配接器最清楚資料實際順序，下游元件**不改變順序** (排序轉換除外)。

Q10: 為何必須鎖定變數？

A：SSIS 執行階段使用**多執行緒平行化** 控制流/資料流。未鎖定變數會導致競態條件：結果非確定性、資料損壞、封裝失敗。VariableDispenser.LockForRead/Write 確保執行緒安全。

三、元件開發與擴展

Q11: 為何分離輸出路徑時不通知元件？

A：設計器支援「刪除路徑 → 重新連接」不破壞封裝行為。元件作者可實作 OnOutputPathDetached 或設定 DeleteOutputOnPathDetached = true 自動清理。

四、管理與監控

Q12: 為何 SSMS 中 DTS 服務下看不到執行中封裝？

A：DTS 服務預設**停用**。需在「服務」中啟動 SQL Server Integration Services 10.0 (或對應版本)，並設為自動啟動。

Q13: 為何封裝記錄不再預設寫入 msdb？

A：SSIS 2005+ 提供**多種記錄提供者**：文字檔、SQL Server (任意資料庫)、Windows 事件記錄、SQL Server Profiler、XML 檔案。每個封裝可配置多個提供者，靈活度遠超舊版單一 msdb 表。

五、遷移相容性 (SQL 2000 DTS → 2005 SSIS)

Q14: 為何 XML 來源配接器不自動偵測結構描述變更？

A：結構描述變更會大幅影響資料流中繼資料，需開發者顯式更新，避免隱性破壞下游轉換。

Q15: 遷移引用 DTSGlobalVariables 的 ActiveX 腳本失效？

A：SSIS 物件模型完全重寫。舊腳本需重寫為 **腳本任務/元件 (C#/VB.NET)**，使用 Dts.Variables 集合存取變數。

Q16: 遷移資料泵任務 (Data Pump Task) 失效？

A：資料流任務 取代資料泵，架構差異巨大無法直接對應。遷移精靈將舊任務封裝在 **SQL 2000 封裝** 中，透過 Execute DTS 2000 Package Task 執行。**建議重寫為原生資料流任務**。

Q17: 遷移動態屬性任務 失效？

A：直接依賴 DTS 2000 物件模型。遷移精靈替換為腳本任務並附註釋，**需手動重寫邏輯**。

Q18: ActiveX 腳本任務中無法存取全域變數？

A：Beta 2 版本限制。正式版支援透過 Dts.Variables 存取。

Q19: 為何移除資料轉換任務與資料驅動查詢任務？

A：資料流任務整合兩者功能並移除限制 (單一來源/目的地/轉換)。新物件模型無法容納舊任務架構。

六、部署與授權

Q20: 為何 SSIS 不再是用戶端可重新散發？

A：SSIS 從工具升級為**平台/應用程式**，包含執行引擎、管理服務、目錄資料庫。授權模型與 SQL Server 引擎一致，確保修補程式統一管理、版本一致性。

Q21: 為何不支援 MSI 部署？

A：MSI 需重新散發二進位檔，安全性修補無法集中推送。目前部署模型要求目標機器預裝 SSIS，由管理員統一管理更新。

七、實戰避坑清單 (開發者必讀)

1. **資料流緩衝區調優**：DefaultBufferMaxRows (預設 10000)、DefaultBufferSize (預設 10 MB) — 依資料列寬度調整
2. **查閱轉換快取模式**：全快取 (小維度)、部分快取 (大維度)、無快取 (極大/即時)
3. **錯誤流配置**：每個轉換/目的地皆配置錯誤流導向統一錯誤表，含錯誤碼、錯誤欄、錯誤描述
4. **檢查點重啟**：控制流層級啟用 CheckpointFile、SaveCheckpoints，關鍵任務設定 FailPackageOnFailure
5. **參數化部署**：專案參數 + 環境變數 (SSISDB) 取代舊版 XML 組態檔
6. **記錄提供者選型**：生產環境建議 **SQL Server 記錄提供者** 寫入集中記錄資料庫，支援 SSISDB 內建報表
7. **版本控管**：.dtsx 為 XML，Git 可追蹤，但合併衝突複雜 — 建議小步提交、專案層級鎖定

總結

SSIS 2005 是**架構級重寫**，帶來企業級 ETL 平台能力，也帶來遷移陣痛。核心心法：**拥抱專案部署模型、資料流緩衝區思維、參數化配置、集中記錄監控**。將本 FAQ 納入團隊知識庫，新成員上手、遷移專案評估、疑難排查將事半功倍。