Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error in SSIS package after deployment


Error in SSIS package after deployment

Author
Message
RamSteve
RamSteve
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 635
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 "DTSTongueassword" 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 "DTSTongueassword" 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.
RamSteve
RamSteve
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 635
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 "DTSTongueassword" 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 "DTSTongueassword" with error
RamSteve
RamSteve
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 635
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 .
kramaswamy
kramaswamy
SSC Eights!
SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)

Group: General Forum Members
Points: 859 Visits: 1788
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.
qaiser2001pk
qaiser2001pk
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 166
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
Tee Time
Tee Time
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 465
Been there myself, and on occasion still forget to change the encryption.
stfnt
stfnt
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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:
<DTSTongueroperty DTS:Name="ConnectionString">Data Source=Oracledb;User ID=userDB;Provider=MSDAORA.1;Persist Security Info=True;</DTSTongueroperty></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!
kramaswamy
kramaswamy
SSC Eights!
SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)

Group: General Forum Members
Points: 859 Visits: 1788
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search