i have a SQL server job that runs a SSIS package. This job has 9 steps and in each step it extracts data from a different database. the connections strings are defined as parameters in each step.
im getting the following error when i run the job.
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.can someone please tell me why this happens?
6条答案
按热度按时间cotxawn71#
除了Kiran's answer建议的内容外，请确保正确设置以下内容：
在SSIS中有一个保存密码的选项（用于访问数据库或任何其他东西），默认设置是“EncryptSensitiveWithUserKey”...您需要更改此设置。
包属性窗口〉保护级别--将其更改为EncryptSensitiveWithPassword包密码--输入密码-〉某个密码
ki1q1bka2#
将项目和包属性ProtectionLevel都更改为“DontSaveSensitive”
rggaifut3#
It is because the creator of the SSIS package is someone other than the person who is executing the packages.
If person A created the SSIS package and person B is trying to execute the package, than the above error will be given.
You can resolve the error by changing the creator name in the package properties from person A to person B.
cu6pst1q4#
Little late to the game but i found a way to fix this for me that i had not seen anywhere else. Select your connection from Connection Managers. On the right you should see properties. Check to see if there are any expressions there if not add one. In your package explorer add a variable called connection to sql or whatever. Set the variable as a string and set the value as your connection string and include the User Id and password. Back to the connection manager properties and expression. From the drop down select ConnectionString and set the second box as the name of your variable. It should look like this
I could not for the life of me find another solution but this worked!
lsmepo6l5#
For me the issue had to do with the parameters assigned to the package.
Make sure you right click on your "Project Name" and then validate that 32-bit runtime is set correctly and that the parameters that are used by default are instantiated properly. Check parameter NAMES and initial values. For my package, I was using values that were not correct and so I had to repopulate the parameter defaults prior to executing my package. Check the values you are using against the defaults you have set for your parameters you have set up in your SSIS package. Once these match the issue should be resolved (for some)
6gpjuf906#
在我例子中，这是因为我在第一次打开解决方案时还没有连接到数据库单击.连接管理器.选项卡，建立到该选项卡中每个数据源连接，运行项目