共用方式為

從 SQL Server Agent 作業步驟呼叫 SSIS 套件時不會執行

  • 發行項

本文可協助您解決從 SQL Server Agent 作業步驟呼叫 SSIS 套件時所發生的問題。

原始產品版本： SQL S
原始 KB 編號： 918760

徵狀

當您從 SQL Server Agent 作業步驟呼叫 Microsoft SQL Server Integration Services (SSIS) 套件時，SSIS 套件不會執行。 不過，如果您未修改 SSIS 套件，它會在 SQL Server Agent 外成功執行。

解決方案

若要解決這個問題，請使用下列其中一個方法。 最適當的方法取決於環境和套件失敗的原因。 套件可能失敗的原因如下：

  • 用來在 SQL Server Agent下執行套件的用戶帳戶與原始套件作者不同。
  • 用戶帳戶沒有建立連線或存取 SSIS 套件外部資源的必要許可權。

在下列案例中，封裝可能不會執行：

  • 目前的用戶無法解密套件中的秘密。 如果目前的帳戶或執行帳戶與原始套件作者不同，而且套件的 ProtectionLevel 屬性設定無法讓目前的使用者解密套件中的秘密，就會發生此案例。
  • 使用整合式安全性的 SQL Server 聯機會失敗，因為目前的用戶沒有必要的許可權。
  • 檔案存取失敗，因為目前的用戶沒有寫入連接管理員存取之檔案共用的必要許可權。 例如，此案例可能會發生在未使用登入和密碼的文字記錄提供者。 此案例也可能發生於任何相依於檔案連接管理員的工作，例如 SSIS 文件系統工作。
  • 以登錄為基礎的 SSIS 套件組態會使用 HKEY_CURRENT_USER 登錄機碼。 登錄 HKEY_CURRENT_USER 機碼是使用者特定的。
  • 工作或連接管理員需要目前的用戶帳戶具有正確的許可權。

若要解決此問題，請使用下列方法：

  • 方法 1：使用 SQL Server Agent Proxy 帳戶。 建立 SQL Server Agent Proxy 帳戶。 此 Proxy 帳戶必須使用認證，讓 SQL Server Agent 以建立套件的帳戶或具有必要許可權的帳戶身分執行作業。

    此方法可解密秘密，並滿足使用者的主要需求。 不過，此方法的成功可能有限，因為 SSIS 套件使用者密鑰牽涉到目前的使用者和目前的電腦。 因此，如果您將套件移至另一部計算機，即使作業步驟使用正確的 Proxy 帳戶，這個方法仍可能失敗。

  • 方法 2：將 SSIS 套件 ProtectionLevel 屬性設定為 ServerStorage。 將 SSIS Package ProtectionLevel 屬性變更為 ServerStorage。 此設定會將套件儲存在 SQL Server 資料庫中，並允許透過 SQL Server 資料庫角色進行訪問控制。

  • 方法 3：將 SSIS 套件 ProtectionLevel 屬性設定為 EncryptSensitiveWithPassword。 將 SSIS 套件 ProtectionLevel 屬性變更為 EncryptSensitiveWithPassword。 此設定會使用密碼進行加密。 然後，您可以修改 SQL Server Agent 作業步驟命令行以包含此密碼。

  • 方法 4：使用 SSIS 套件組態檔。 使用 SSIS 套件組態檔來儲存敏感性資訊，然後將這些組態檔儲存在安全的資料夾中。 然後，您可以將 屬性變更 ProtectionLevel 為 DontSaveSensitive ，讓封裝不會加密，而且不會嘗試將秘密儲存至封裝。 當您執行 SSIS 套件時，會從組態檔載入必要的資訊。 如果組態檔包含敏感性資訊，請確定組態檔已受到適當保護。

  • 方法 5：建立套件範本。 針對長期解析，請建立使用與預設設定不同之保護層級的套件範本。 未來套件不會發生此問題。

重現問題的步驟

  1. 以不屬於 SQLServerSQLAgentUser 群組的使用者身分登入。 例如，您可以建立本機使用者。
  2. 建立 SSIS 套件，然後新增 ExecuteSQL 工作。 使用下列字串，將 OLE DB 連接管理員用於本機 msdb 檔案： 'Windows Authentication' -SQLSourceType: "Direct Input" -SQLStatement: "sp_who"。
  3. 執行封裝，以確定其執行成功。
  4. 屬性 ProtectionLevel 設定為 EncryptSensitiveWithPassword。
  5. 建立 SQL Server Agent 作業和作業步驟。 在 [執行身分] 列表中，按兩下 [SQL Server Agent 服務] 以執行作業步驟。 SQL Server Agent 作業歷程記錄中的文字會顯示類似下列的資訊：

解密套件秘密

SSIS 封裝 ProtectionLevel 屬性的預設設定為 EncryptSensitiveWithUserKey。 儲存封裝時，SSIS 只會加密封裝中包含標示 sensitive之屬性的部分，例如密碼、使用者名稱和連接字串。 因此，重載套件時，目前的用戶必須滿足要解密之屬性的加密 sensitive 需求。 不過，目前的使用者不需要滿足載入封裝的加密需求。 當您透過 SQL Server Agent 作業步驟執行套件時，預設帳戶是 SQL Server Agent 服務帳戶。 此預設帳戶很可能是與套件作者不同的使用者。 因此，SQL Server Agent 作業步驟可以載入並開始執行作業步驟，但封裝會因為無法完成連線而失敗。 例如，封裝無法完成 OLE DB 連線或 FTP 連線。 套件失敗，因為它無法解密必須連接的認證。

重要事項

請考慮開發程式和環境，以判斷每部計算機上需要和使用的帳戶。 屬性的 EncryptSensitiveWithUserKey 設定 ProtectionLevel 是功能強大的設定。 此設定一開始會導致部署複雜性，因此不應折扣。 當您登入適當的帳戶時，可以加密套件。 您也可以使用 Dtutil.exe SSIS 命令提示字元公用程式，使用.cmd檔案和 SQL Server Agent 命令子系統來變更保護層級。 例如，請遵循下列步驟。 因為您可以在批處理檔和迴圈中使用 Dtutil.exe 公用程式，所以您可以同時針對數個套件遵循這些步驟。

  1. 使用密碼修改您想要加密的套件。

  2. 透過作業系統 (cmd Exec) SQL Server Agent 作業步驟使用 Dtutil.exe 公用程式，將 屬性變更ProtectionLevel為 EncryptSensitiveWithUserKey。 此程式牽涉到使用密碼來解密封裝，然後重新加密套件。 用來加密套件的使用者金鑰是執行身分清單中 SQL Server Agent 作業步驟設定。

    注意事項

    因為金鑰包含使用者名稱和計算機名稱，所以將套件移至另一部計算機的效果可能會受到限制。

請確定您有有關 SSIS 套件失敗的詳細錯誤資訊

您可以使用 SSIS 記錄來確定您有有關 SSIS 套件失敗的錯誤資訊，而不是依賴 SQL Server Agent 作業歷程記錄中的有限詳細數據。 您也可以使用 exec 子系統命令，而不是 SSIS 子系統命令來執行封裝。

關於 SSIS 記錄

SSIS 記錄和記錄提供者可讓您擷取有關封裝執行和失敗的詳細數據。 根據預設，封裝不會記錄資訊。 您必須設定封裝來記錄資訊。 當您將封裝設定為記錄資訊時，會顯示類似下列的詳細資訊。 在此情況下，您會知道這是許可權問題：

OnError,DOMAINNAME,DOMAINNAME\USERNAME,FTP Task,{C73DE41C-D0A6-450A-BB94-DF6D913797A1},{2F0AF5AF-2FFD-4928-88EE-1B58EB431D74},4/28/2006 1:51:59 PM,4/28/2006 1:51:59 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

OnError,DOMAINNAME,DOMAINNAME\USERNAME,Execute SQL Task,{C6C7286D-57D4-4490-B12D-AC9867AE5762},{F5761A49-F2F9-4575-9E2B-B3D381D6E1F3},4/28/2006 4:07:00 PM,4/28/2006 4:07:00 PM,-1073573396,0x,Failed to acquire connection "user01.msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.

關於 exec 子系統命令和輸出資訊

藉由使用 exec 子系統命令方法，您可以將詳細資訊主控台記錄參數新增至 SSIS 命令行，以呼叫 Dtexec.exe SSIS 命令行可執行檔。 此外，您也會使用輸出檔案的進階作業功能。 您也可以使用 [在歷程記錄中包含步驟輸出] 選項，將記錄資訊重新導向至檔案或 SQL Server Agent 作業記錄。

以下是命令列的範例：

 dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT

主控台記錄會傳回類似下列的詳細資料：

Error: 2006-04-27 18:13:34.76 Code: 0xC0202009 Source: AgentTesting Connection manager "(local).msdb" Description: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'DOMAINNAME\username'.". End Error

Error: 2006-04-28 13:51:59.19 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error

Log: Name: OnError Computer: COMPUTERNAME Operator: DOMAINNAME\username Source Name: Execute SQL Task Source GUID: {C6C7286D-57D4-4490-B12D-AC9867AE5762} Execution GUID: {7AFE3D9E-5F73-42F0-86FE-5EFE264119C8} Message: Failed to acquire connection "(local).msdb". Connection may not be configured correctly or you may not have the right permissions on this connection. Start Time: 2006-04-27 18:13:34 End Time: 2006-04-27 18:13:34 End Log

參考資料

可惜的是，使用者並不知道預設的代理程式作業步驟設定會讓他們處於此狀態。 如需 SQL Server Agent Proxy 和 SSIS 的詳細資訊，請參閱《SQL Server 2005 在線叢書》中的下列主題：

  • 在 SQL Server Agent 中排程封裝執行
  • 建立 SQL Server Agent Proxy