Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error in SSIS package after deployment Expand / Collapse
Author
Message
Posted Tuesday, August 23, 2011 10:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 AM
Points: 163, Visits: 574
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
Posted Tuesday, August 23, 2011 2:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 AM
Points: 163, Visits: 574
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
Posted Tuesday, August 23, 2011 3:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 AM
Points: 163, Visits: 574
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
Posted Tuesday, August 23, 2011 6:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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
Posted Wednesday, October 3, 2012 12:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:33 PM
Points: 69, Visits: 142
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
Posted Wednesday, October 3, 2012 2:57 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
Been there myself, and on occasion still forget to change the encryption.
Post #1368050
Posted Friday, July 19, 2013 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:40 AM
Points: 1, Visits: 1
What worked for me:
- create the package with the wizard using "Do not save sensitive data";
- import the package to Visual Studio Management;
- right click on the package and "View Code";
- find the connection string, in my case Oracle:
<DTS:Property DTS:Name="ConnectionString">Data Source=Oracledb;User ID=userDB;Provider=MSDAORA.1;Persist Security Info=True;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
- modify the string by adding "Password=userPassword"
- save the package;

In my case this eliminated the null password problem, not to mention that, the password in not visible in the connection string.

Hope it works for you!
Post #1475360
Posted Friday, July 19, 2013 9:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
Problem with using that approach is that, if you choose to not save sensitive data, then you will have to specify all parameters that are sensitive each time you run - which is quite inconvenient. Much simpler to go with EncyrptSensitiveWithPassword, which allows you to save connection strings and such, and still keeps your package protected.
Post #1475560
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse