Need help with VS_NEEDSNEWMETADATA error

Hello:

I need some help figuring out the true source of the following error:

"

Executed as user: EPSILON\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:09 PM Error: 2007-09-14 12:53:09.59 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" 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: 2007-09-14 12:53:10.50 Code: 0xC020837F Source: Data Flow Task Source - icsp [1] Description: The data type of "output column "user2" (138)" does not match the data type "System.String" of the source column "user2". End Error Error: 2007-09-14 12:53:10.50 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - icsp" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". The package execution fa... The step failed."

Reading the error it appears that the issue is with a datatype mismatch with field "user2". It is coming in as a unicode string and being sent into a varchar field. But so are a whole bunch of other userX fields as well. So why is my package having an issue with this specific field. Moreoever the package was running fine until a few days ago and runs successfully in BIDS!

I first figured that the source has changed, as there was some work being performed on the source ERP system. The package had failed a month ago and when I updated the metadata I thought it fixed the problem.

I appreciate your assistance in helping me resolve this issue!

Don't ignore the password issue. Maybe because it can't use the password, it can't determine that the metadata has changed, or something like that. In BIDS, maybe it doesn't have the password problem?

|||Did you move the package from one computer to another by any chance? What is the ProtectionLevel of the package set to? I recommend setting the ProtectionLevel to "DontSaveSensitive" and seeing if it resolves the problem.

I have also had problems in the past where somehow the metadata gets messed up and the easiest thing to do is recreate the package.|||

Thank you for your response gentlemen!

I have checked the connection string for teh SQL Agent and it has the correct ID and password. I have not changed anything in the job. In BIDS, when I enter the password and run the job it runs fine.

|||As Danny asked, what's the ProtectionLevel of the package?|||EncryptSensitiveWithUserKey|||

Using EncryptSensitiveWithUserKey is the cause of the error. This is a link to an article that explains the problem and more importantly, the solution:

http://support.microsoft.com/kb/918760

|||

You mention SQL Agent, so does this error happen when you schedule the package, but works for you on your desktop?

If so the problem is the ProtectionLevel. The EncryptSensitiveWithUserKey value means just that, it uses the user key, your key as you built the package. If your SQL Server Agent service was set to run under you account, then the error would go away. that would also be a stupid thing to do, so change to using DontSaveSensitive is my advice. If you have passwords, then supply them through Configurations.

Some links with more information -

http://support.microsoft.com/kb/904800

http://support.microsoft.com/kb/918760

http://technet.microsoft.com/en-us/library/ms141682.aspx

|||

I have to admit, I have really looked at the Protection Level closely. The issue is that the package for running fine in an Agent, as well are other packages with the same type of Protection Level. Why did it work all this time and is not working now - is the question that puzzles me?

I will review the links that you have provided and figure out how to build Configurations.

PS: The package does not run outside of an agent in Mgmt. Studio but runs in BIDS after I supply the password in the DataReaderSrc.

Thanks again!

|||

Many of us have had to struggle with the ProtectionLevel setting during deployment. I do not think Microsoft documented it very well. It finally clicks after beating your head against the wall for awhile and visiting forums.