包含SSIS包的SQL Server作业-无法解密受保护的XML节点"DTS:Password“，错误为0x8009000B
我有一个运行SSIS包的SQL服务器作业。该作业有9个步骤，每个步骤都从不同的数据库中提取数据。连接字符串被定义为每个步骤中的参数。
当我运行作业时，我得到了以下错误。
Executed as user: USER\MYSERVER$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 5:50:55 PM Error: 2013-06-21 17:50:55.44
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" 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-06-21 17:50:55.45
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" 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-06-21 17:50:55.45
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" 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-06-21 17:51:06.30
Code: 0xC020901C
Source: Data Flow Task Daily Attendance View 1 [34]
Description: There was an error with output column "ShiftCode" (54) on output "OLE DB Source Output" (45). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2013-06-21 17:51:06.30
Code: 0xC020902A
Source: Data Flow Task Daily Attendance View 1 [34]
Description: The "output column "ShiftCode" (54)" failed because truncation occurred, and the truncation row disposition on "output column "ShiftCode" (54)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2013-06-21 17:51:06.30
Code: 0xC0047038
Source: Data Flow Task SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Daily Attendance View 1" (34) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:50:55 PM Finished: 5:51:06 PM Elapsed: 10.983 seconds. The package execution failed. The step failed.有人能告诉我为什么会这样吗？
回答 5
Stack Overflow用户
发布于 2014-05-08 23:56:19
除了Kiran's answer的建议之外，还要确保设置正确：
在SSIS中有一个选项来保存密码(访问数据库或任何其他东西)，默认设置是“EncryptSensitiveWithUserKey”……你需要改变这一点。
Package属性窗口> ProtectionLevel --将其更改为EncryptSensitiveWithPassword PackagePassword --输入密码->某些密码
Stack Overflow用户
发布于 2013-10-17 16:32:53
这是因为SSIS包的创建者是其他人，而其他人正在执行这些包。
假设A人已经创建了SSIS包，B人正在尝试执行，那么上述错误就会出现。
您可以通过将包属性中的创建者名称从A更改为B来解决此错误。
谢谢，Kiran Sagar
Stack Overflow用户
发布于 2018-02-14 06:21:27
这个游戏有点晚了，但我找到了一种我在其他地方都没有见过的解决方法。从连接管理器中选择您的连接。在右侧，您应该会看到属性。如果没有添加表达式，请检查是否有任何表达式。在包资源管理器中，添加一个名为connection to sql的变量。将变量设置为字符串，将值设置为连接字符串，并包括用户Id和密码。回到连接管理器属性和表达式。从下拉列表中选择ConnectionString，并将第二个框设置为变量的名称。它应该看起来像这样
我无论如何也找不到另一个解决方案，但这个办法奏效了！
https://stackoverflow.com/questions/17248560
复制相似问题