Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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?
Posted

1 solution

There is an option to in SSIS to save passwords(to access DB or anyother stuff), the default setting is "EncryptSensitiveWithUserKey"... You need to change this.

Package Proprties Window >
ProtectionLevel -- Change that to EncryptSensitiveWithPassword
PackagePassword -- enter password-> somepassword

Read: http://msdn.microsoft.com/en-us/library/ms141747.aspx[^]

http://support.microsoft.com/kb/918760[^]

Now you can specify the paassword in SQL Agent Job.

On SQL Agent Job Step, go the Command Line Tab, manually edit command line, then add the following

/De somepassword
After modification you would see(example):
/FILE "\"C:\Users\xxxx\Documents\SQL Server Management Studio\SSIS\Package.dtsx\"" /DECRYPT somepassword /CHECKPOINTING OFF /REPORTING E


Thanks,

Kuthuparakkal
 
Share this answer
 
v2
Comments
The Doer 25-Jul-13 1:42am    
Hi Kuthuparakkal,
I am also facing the same problem i changed from EncryptSensitiveWithUserKey to EncryptSensitiveWithPassword . and done the modification as you writtten above, but ended up with the following error.

Missing argument for option "decrypt".
The command line parameters are invalid. The step failed.
Kuthuparakkal 25-Jul-13 2:42am    
Modified, please try now!
The Doer 25-Jul-13 4:44am    
I changed now its displaying-
/FILE "H:\BOOGLE\BOOGLE\bin\Deployment\Package.dtsx" /DECRYPT /CHECKPOINTING OFF /REPORTING E


but it cause error as-

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 'sa'.". End Error Error: 2013-07-25 16:39:27.10 Code: 0xC00291EC Source: Sales from MADPOS to B2Lingerie Execute SQL Task Description: Failed to acquire connection "172.16.3.10.AIMB_POS.sa". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:39:24 PM Finished: 4:39:27 PM Elapsed: 3.031 seconds. The package execution failed. The step failed.,00:00:03,0,0,,,,0
The Doer 25-Jul-13 4:46am    
and then now as soon i change from EncryptSensitiveWithUserKey to EncryptSensitiveWithPassword , my package is not running- it shows error as-
Failed to acquire connection, Or you may not have the right permission on this connection.

Note:- Am deploying it in another Server. Does it cause any effect??
Kuthuparakkal 25-Jul-13 9:15am    
Did you give proper password once you change to "EncryptSensitiveWithUserKey" ?
Also, be sure if you are using 32bit execution mode when Excel(.xls or .xlsx) connection involved.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900