SQL Server Agent 執行 SSIS 套件失敗排查：xp_cmdshell + dtexec 完整實戰

現象

Agent 作業執行 SSIS 套件失敗，歷史記錄僅顯示「以 xxx 帳戶身份執行失敗」，無詳細錯誤資訊。

核心解法：啟用 xp_cmdshell + dtexec 呼叫

步驟 1：啟用 xp_cmdshell (需 sysadmin)

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

步驟 2：兩種 dtexec 執行模式

模式 A：直接執行 .dtsx 檔案 (檔案系統部署)

EXEC xp_cmdshell 'dtexec /f "C:\SSIS\Packages\UpsertData.dtsx"';

• 優點：部署簡單，檔案即套件。
• 缺點：路徑硬編碼，參數傳遞較繁 (`/SET \Package.Variables[User::Var].Value;...`)。

模式 B：部署至 SSISDB (專案部署模型, SQL 2012+) → 推薦

1. SSMS → Integration Services 目錄 → 右鍵「專案」→「部署專案」→ 選擇 `.ispac` 檔。
2. 建立 Agent 作業步驟：
   • 類型：**SQL Server Integration Services 套件**
   • 套件來源：**SSIS 目錄**
   • 伺服器：實例名
   • 套件路徑：`\SSISDB\Folder\Project\Package.dtsx`
   • 參數/連線管理員：在「設定」頁直接覆寫。

**此模式無需 `xp_cmdshell`，Agent 直接呼叫 SSIS 目錄執行引擎，權限隔離、日誌完整、參數化原生支援。**

舊版 (套件部署模型) 必讀：匯入 MSDB + dtexec /DTS

1. SSMS → 連線 **Integration Services** → 「已儲存的套件」→「MSDB」→ 右鍵「匯入套件」→ 選檔案系統 `.dtsx`。
2. **保護層級**務必選：「依賴伺服器儲存和角色存取控制」(`Server Storage`)，否則 `dtexec` 解密失敗報錯 `0x8009000B`。

EXEC xp_cmdshell 'dtexec /DTS "\MSDB\MyPackage" /SERVER "MyInstance" /MAXCONCURRENT "-1" /CHECKPOINTING OFF /REPORTING V';

常見失敗原因與對策

最佳實踐棧 (2024+)

1. 專案部署模型 + SSISDB：版本控管、環境參數、執行日誌、重試/檢查點、T-SQL/REST API 觸發。
2. Agent Proxy 帳號：最小權限執行套件，隔離安全。
3. 日誌集中化：SSISDB 內建報表 + 自訂 `event_log` 寫入 ELK/Splunk。
4. CI/CD 整合：Azure DevOps / GitHub Actions → `sqlpackage` / `isdeploymentwizard` 自動部署 `.ispac`。

總結

Agent 執行 SSIS 失敗**先看保護層級、再看執行身分權限、最後看 32/64 位元**。新專案**直接上 SSISDB 專案部署模型**，棄用 `xp_cmdshell` + `dtexec` + MSDB 的舊鏈路，獲得原生參數化、日誌、安全、可觀測性。