Error in SSIS package after deployment

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Error in SSIS package after deployment

Error in SSIS package after deployment

Rate Topic

Display Mode

Topic Options

Author

Message

RamSteve

Posted Tuesday, August 23, 2011 10:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 9:53 AM
Points: 144, Visits: 479

Hi I have deployed the package and when i was running this package from other machine i was getting the following error .Can some one help me in ignoring this failure ... Thanks in advance

Error Message

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: 2011-08-23 12:25:04.79 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: 2011-08-23 12:25:06.43 Code: 0xC0202009 Source: OracleDB_INFO Connection manager "Target" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied ". End Error Error: 2011-08-23 12:25:06.43 Code: 0xC020801C Source: DB_INFO OLE DB Source [40] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Target" 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: 2011-08-23 12:25:06.43 Code: 0xC0047017 Source: DB_INFO SSIS.Pipeline Description: component "OLE DB Source" (40) failed validation and returned error code 0xC020801C. End Error Error: 2011-08-23 12:25:06.43 Code: 0xC004700C Source: DB_INFO SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-08-23 12:25:06.43 Code: 0xC0024107 Source: DB_INFO Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:25:04 PM Finished: 12:25:06 PM Elapsed: 1.828 seconds. The package execution failed. The step failed.

Post #1164166

RamSteve

Posted Tuesday, August 23, 2011 2:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 9:53 AM
Points: 144, Visits: 479

I have the Package deployed by following steps
-->Right Click on Project and Deployment utility is set to true
-->Build the Solution
-->Go to bin\Deployment and run the deployment tool and selected file system type.
-->Scheduled the package in SQL Server Jobs .

But i was getting the following error ..My Question is where the packages encrypted?? The package level protection is as "EncryptSensitiveWithUserKey"

Message
Executed as user: XX\ACXXXX. ...Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:22:33 PM Error: 2011-08-23 16:22:33.84 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: 2011-08-23 16:22:33.86 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error

Post #1164313

RamSteve

Posted Tuesday, August 23, 2011 3:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 9:53 AM
Points: 144, Visits: 479

Now My issue was resolved once i changed the protection level of the package to encrypt with password and i gave the credentials at Set values in SQL Agent job properties and now the job runs fine with out any issues .The issue was raised as i developed on one machine and i was running the package on other machine .

Post #1164325

kramaswamy

Posted Tuesday, August 23, 2011 6:43 PM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 785, Visits: 1,534

The problem is one that I've faced several times, and it's because the default encryption level is EncryptSensitiveWithUserKey.

The problem is that the User Key is something specific to a machine. That means once you copy the package to another machine, it will fail.

Changing it to EncryptSensitiveWithPassword, or EncryptAllWithPassword, allows you to avoid that problem.

Post #1164377

qaiser2001pk

Posted Wednesday, October 03, 2012 12:12 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 5:31 PM
Points: 69, Visits: 125

http://msdn.microsoft.com/en-us/library/dd440760.aspx
Play Video on this page, this will resolve all issue regarding running SSIS packages through SQL Server Agent Job.
Thanks
Muhammad Qaiser

Post #1367411

Tee Time

Posted Wednesday, October 03, 2012 2:57 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:27 AM
Points: 321, Visits: 236

Been there myself, and on occasion still forget to change the encryption.

Post #1368050

« Prev Topic | Next Topic »

Permissions