从代理作业步骤调用 SSIS SQL Server不运行

本文帮助您解决从代理作业步骤调用 SSIS 包时SQL Server的问题。

原始产品版本:  SQL Server
原始 KB 编号:   918760

症状

当你从 Microsoft SQL Server 代理 (步骤) SSIS SQL Server 集成服务包时,SSIS 包不会运行。 但是,如果不修改 SSIS 包,它将在代理外部SQL Server运行。

解决方案

若要解决此问题,请使用下列方法之一。 最合适的方法取决于环境和包失败的原因。 程序包可能失败的原因如下:

  • 用于在代理下运行程序包的用户帐户SQL Server包作者不同。
  • 用户帐户没有建立连接或访问 SSIS 程序包外部资源所需的权限。

包可能不会在下列情况下运行:

  • 当前用户无法解密程序包中的密钥。 如果当前帐户或执行帐户与原始包作者不同,并且包的 ProtectionLevel 属性设置不允许当前用户解密程序包中的密码,则可能发生此情况。
  • 使用SQL Server安全性的连接失败,因为当前用户没有所需的权限。
  • 文件访问失败,因为当前用户没有写入连接管理器访问的文件共享所需的权限。 例如,不使用登录名和密码的文本日志提供程序可能会出现此情况。 对于依赖于文件连接管理器的任何任务(如 SSIS 文件系统任务)也会发生此方案。
  • 基于注册表的 SSIS 包配置使用 HKEY_CURRENT_USER 注册表项。 注册表 HKEY_CURRENT_USER 项特定于用户。
  • 任务或连接管理器要求当前用户帐户具有正确的权限。

若要解决此问题,请使用以下方法:

  • 方法 1:使用SQL Server代理帐户。 创建SQL Server代理帐户。 此代理帐户必须使用一个凭据,SQL Server代理以创建程序包的帐户或具有所需权限的帐户身份运行作业。

    此方法适用于解密密钥,并满足用户的关键要求。 但是,此方法的成功可能有限,因为 SSIS 包用户密钥涉及当前用户和当前计算机。 因此,如果将程序包移动到另一台计算机,即使作业步骤使用了正确的代理帐户,此方法也可能失败。

  • 方法 2:将 SSIS Package ProtectionLevel 属性设置为 ServerStorage。 将 SSIS Package ProtectionLevel 属性更改为 ServerStorage。 此设置将包存储在一个SQL Server数据库中,并允许通过数据库角色SQL Server访问控制。

  • 方法 3:将 SSIS Package ProtectionLevel 属性设置为 EncryptSensitiveWithPassword 。 将 SSIS Package ProtectionLevel 属性更改为 EncryptSensitiveWithPassword 。 此设置使用密码进行加密。 然后,您可以修改SQL Server代理作业步骤命令行以包含此密码。

  • 方法 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代理作业和作业步骤。 在"运行代理"列表中,SQL Server 代理服务" 以运行作业步骤。 "代理作业SQL Server中的文本显示的信息类似于以下内容:

解密程序包密码

SSIS 包属性的默认设置 ProtectionLevelEncryptSensitiveWithUserKey 。 保存包时,SSIS 仅对包含标记为 的属性(如密码、用户名和连接字符串)的包 sensitive 部分进行加密。 因此,在重新加载包时,当前用户必须满足要解密的属性的 sensitive 加密要求。 但是,当前用户无需满足加密要求来加载包。 通过代理作业步骤SQL Server包时,默认帐户是 SQL Server 代理服务帐户。 此默认帐户很可能与程序包作者不同。 因此,SQL Server代理作业步骤可以加载并开始运行作业步骤,但包会失败,因为它无法完成连接。 例如,该包无法完成 OLE DB 连接或 FTP 连接。 程序包失败,因为它无法解密必须连接的凭据。

重要

考虑开发过程和环境以确定每台计算机需要和使用的帐户。 该属性的 EncryptSensitiveWithUserKey ProtectionLevel 设置是一个强大的设置。 此设置不应折扣,因为它首先会导致部署复杂性。 登录相应帐户后,可以加密程序包。 您还可以使用 Dtutil.exe SSIS 命令提示符实用程序,通过 .cmd 文件和 SQL Server Agent 命令子系统更改保护级别。 例如,请按照以下步骤操作。 由于可以在批处理Dtutil.exe和循环中使用应用程序实用程序,因此可以同时对多个包执行这些步骤。

  1. 使用密码修改要加密的程序包。

  2. 使用Dtutil.exe通过 操作系统 (cmd Exec) SQL Server 代理作业步骤将 ProtectionLevel 属性更改为 EncryptSensitiveWithUserKey 。 此过程涉及使用密码解密程序包,然后重新加密程序包。 用于加密程序包的用户密钥是"代理SQL Server"列表中的"代理作业 "步骤 设置。

    备注

    由于键包括用户名和计算机名称,因此将程序包移动到另一台计算机的效果可能会受到限制。

确保您具有有关 SSIS 包失败的详细错误信息

您可以使用 SSIS 日志记录确保具有有关 SSIS 包失败的错误信息,而不是依赖 SQL Server 代理作业历史记录中的有限详细信息。 您还可以使用 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作业历史记录。

下面是命令行的一个示例:

 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

参考

遗憾的是,用户不知道默认代理作业步骤设置会将其置于此状态。 有关代理代理和 SSIS SQL Server,请参阅 SQL Server 2005 联机书籍中的下列主题:

  • 在代理中计划SQL Server执行
  • 创建SQL Server代理代理