萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Agent 作業執行 SSIS 套件失敗排查:xp_cmdshell 與 dtexec 完整指南

現象

SQL Agent 作業步驟類型選 **SSIS 套件** 或 **命令執行 `dtexec`** 時,失敗訊息僅顯示「以使用者 xxx 身分執行失敗」,無詳細錯誤,排查困難。

核心工具:dtexec.exe

命令列執行 SSIS 套件 (`.dtsx` 或 SSISDB 目錄) 的標準工具,回傳詳細錯誤碼與輸出,適合 Agent 作業步驟 (CmdExec 類型) 或手工測試。

步驟一:啟用 xp_cmdshell (若 Agent 使用 T-SQL 步驟呼叫)

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

安全警示:`xp_cmdshell` 極高風險,**生產環境建議關閉**,改用 **Agent CmdExec 步驟直接呼叫 `dtexec`** (由 Agent 服務帳號執行,不經 `xp_cmdshell`)。

步驟二:兩種部署/執行模式

模式 A:檔案系統部署 (File System / .dtsx)

-- Agent 步驟:類型 = Operating System (CmdExec)
dtexec /f "C:\SSIS\Packages\UpsertData.dtsx" /DECRYPT "YourPassword" /REPORTING EWCDI
  • `/f`:檔案路徑。
  • `/DECRYPT`:若套件加密 (ProtectionLevel = EncryptSensitiveWithPassword),需提供密碼。
  • `/REPORTING EWCDI`:輸出 Error/Warning/Custom/Diagnostic/Information 到標準輸出 (Agent 記錄可見)。

模式 B:SSISDB 目錄部署 (Project Deployment Model, SQL 2012+)

  1. SSMS → **Integration Services Catalogs** → `SSISDB` → 右鍵 **部署專案** → 選 `.ispac` 檔。
  2. 部署時 **Protection Level** 選 **`Rely on server storage and roles for access control`** (依賴伺服器儲存與角色存取控制)。
  3. Agent 步驟:類型 = **SQL Server Integration Services Package** → 選擇 SSISDB 目錄下的專案/套件。**無需密碼、無需 xp_cmdshell**。

常見失敗原因與對策

錯誤現象根因解決
無法解密受保護節點 (0x8009000B)ProtectionLevel = EncryptSensitiveWithUserKey,執行身分異於開發者改用 `EncryptSensitiveWithPassword` + `/DECRYPT`,或部署至 SSISDB 選 Server Storage
登入失敗 / 連線失敗Agent 服務帳號/Proxy 帳號無目標資料庫權限建立 **Proxy 帳號** (Credentials + Proxy) 具備最低權限,步驟指定 Run As Proxy
找不到檔案/路徑路徑含空格未加引號、網路路徑無權限、32/64 位元不匹配路徑加雙引號;用 UNC 路徑並給 Proxy 讀取權;確認呼叫正確版本 `dtexec` (x64: `C:\Program Files\Microsoft SQL Server\130\DTS\Binn\`)
元件/連線管理員驗證失敗執行時參數/環境變數未傳遞使用 **環境** (Environment) 在 SSISDB 設定參數值,或 `/SET \Package.Variables[User::VarName];Value` 覆寫
記憶體不足 / 緩衝區錯誤資料流緩衝區設定過大、並行度過高調整 `DefaultBufferMaxSize`、`DefaultBufferMaxRows`、`EngineThreads`;啟用 64 位元執行

最佳實務:Agent 作業標準化範本

  1. **步驟 1 (CmdExec)**:`dtexec /ISSERVER "\SSISDB\Folder\Project\Package.dtsx" /SERVER "." /ENVREFERENCE 1 /REPORTING EWCDI` (SSISDB 部署首選)。
  2. **步驟 2 (T-SQL)**:記錄執行結果至自訂日誌表,失敗發送郵件 (`sp_send_dbmail`)。
  3. **排程**:依業務窗口設定 (夜間/每小時)。
  4. **通知**:完成/失敗郵件給 DBA 與擁有者。

進階:取得詳細錯誤而不依賴 Agent 記錄

-- 在 SSIS 套件內加入「事件處理常式」OnError
-- 使用 Script Task 寫入自訂 Log 表或呼叫 sp_send_dbmail
-- 變數 System::ErrorCode, System::ErrorDescription 可取得完整錯誤
copyright © 萬盛學電腦網 all rights reserved