从 JOB 执行 SSIS 包时出错 [英] Error while executing SSIS package from JOB
问题描述
留言
以用户身份执行:UKDBT91DB05V\SYSTEM.微软 (R) SQL Server为 64 位版权执行包实用程序版本 10.50.2500.0(C) Microsoft Corporation 2010.保留所有权利.开始:14:58:34 错误:2013-04-01 14:58:34.45 代码:0xC0016016
来源:描述:无法解密受保护的 XML 节点DTS:Property"，错误为 0x8009000B密钥不能用于指定状态.".您可能无权访问此信息.当存在加密错误时会发生此错误.验证正确的密钥是否可用.结束错误错误:2013-04-01 14:58:34.76 代码:0xC001000E 来源:收到DGD信息说明:连接stock"不是成立.当连接集合抛出此错误时未找到特定的连接元素.结束错误错误:2013-04-01 14:58:34.80 代码:0xC0202009 来源:接收DGD信息的连接管理器Stock"说明:SSIS错误代码 DTS_E_OLEDBERROR.发生 OLE DB 错误.错误代码:0x80040E4D.OLE DB 记录可用.来源:微软SQL Server Native Client 10.0" Hresult: 0x80040E4D 描述:用户‘DOTCOM\UKDBT91DB05V$’登录失败.".结束错误错误:2013-04-01 14:58:34.80 代码:0xC020801C 来源:PopulateStaging Table Populate DGD Staging table [34] 描述:SSIS错误代码 DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.这AcquireConnection 方法调用连接管理器Stock"失败错误代码为 0xC0202009.之前可能有报错信息这包含有关为什么 AcquireConnection 方法调用的更多信息失败的.结束错误错误:2013-04-01 14:58:34.80 代码:0xC0047017来源:Populate Staging Table SSIS.Pipeline 描述:组件填充 DGD 暂存表"(34) 验证失败并且返回错误代码 0xC020801C.结束错误错误:2013-04-0114:58:34.80 代码:0xC004700C 来源:填充临时表SSIS.Pipeline 描述:一个或多个组件失败验证.结束错误错误:2013-04-01 14:58:34.80 代码:0xC0024107 来源:Populate Staging Table 描述:有是任务验证期间的错误.结束错误 DTExec:包执行返回 DTSER_FAILURE (1).开始时间:14:58:34 结束时间:14:58:34 经过:0.624 秒.包执行失败.这步骤失败.
Executed as user: UKDBT91DB05V\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 14:58:34 Error: 2013-04-01 14:58:34.45 Code: 0xC0016016
Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". 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 Error: 2013-04-01 14:58:34.76 Code: 0xC001000E Source: ReceiveDGDinformation Description: The connection "stock" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC0202009 Source: ReceiveDGDinformation Connection manager "Stock" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DOTCOM\UKDBT91DB05V$'.". End Error Error: 2013-04-01 14:58:34.80 Code: 0xC020801C Source: Populate Staginng Table Populate DGD Staging table [34] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Stock" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC0047017 Source: Populate Staginng Table SSIS.Pipeline Description: component "Populate DGD Staging table" (34) failed validation and returned error code 0xC020801C. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC004700C Source: Populate Staginng Table SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC0024107 Source: Populate Staginng Table Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:58:34 Finished: 14:58:34 Elapsed: 0.624 seconds. The package execution failed. The step failed.
错误信息的有意义的部分
Meaningful bits of the error message
- 未能解密受保护的 XML 节点DTS:Property"，错误为 0x8009000B密钥在指定状态下无效.".您可能无权访问此信息.当存在加密错误时会发生此错误
- 用户DOTCOM\UKDBT91DB05V$"登录失败
推荐答案
这可能是您的 ProtectionLevel 设置以及您如何配置作业的问题.
This is probably a problem with your ProtectionLevel setting and how you are configuring the job.
如果 ProtectionLevel 处于默认的EncryptSensitiveWithUserKey"设置，那么开发环境会使用依赖于进行开发的用户的密钥来加密诸如连接字符串数据库密码之类的内容.将其部署到生产服务器并安排 SQL Server 代理作业后，它很可能会在不同的用户帐户下运行，然后将无法解密数据库密码.你会得到这个错误.
If ProtectionLevel is at the default "EncryptSensitiveWithUserKey" setting, then the development environment is encrypting things like your connection string database password using a key that depends on the user who is doing the development. After you deploy it to the production server and schedule a SQL Server Agent job, it will most likely run under a different user account, and then it will be unable to decrypt the database password. You will get this error.
一种可能性是确保开发用户和作业执行用户是同一个用户帐户，但这不是一个好主意.通常，您希望生产用户帐户与开发用户帐户分开.
One possibility would be to make sure the development user and the job execution user are the same user account, but this is not a good idea. Usually you want your production user accounts to be separate from your development user accounts.
相反，您应该将 ProtectionLevel 设置为DontSaveSensitive".然后密码将不会与 SSIS 包一起保存.您为连接字符串创建了一个配置文件，但密码也不会保存到配置文件中.如果您希望它包含密码，则必须手动编辑配置文件.但最好的方法是在安排执行 SSIS 包的作业时配置密码.这会将密码保存在一个安全的地方，并且它不会随 SSIS 包到处浮动.
Instead, you should set ProtectionLevel to "DontSaveSensitive". Then the password won't get saved with the SSIS package at all. You create a configuration file for the connection string, but the password won't get saved to the configuration file either. You will have to edit the configuration file manually if you want it to include a password. But the best way to do this is to configure the password when you schedule the job that executes the SSIS package. That keeps the password in a safe place, and it isn't floating around all over the place with the SSIS package.
Some useful links here and here.
祝你好运！
这篇关于从 JOB 执行 SSIS 包时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
