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.

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.

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.

Need help with Update query to populate new field

Hi folks:

I have added a new field to an established table, and am having trouble figuring out how to populate its values:

Two tables are involved: Jobs and Parts

There is a one-to-one relationship between each JobID and its PartID

Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.

The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:

UPDATE Jobs

SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))

WHERE (JobID = [the job in question])

My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,

Matt

which is the parent table?

|||assuming that the jobs table has a foreign key that links to the parts table, the update query should look like this

UPDATE J
set j.JobPrice = p.PartPrice
from Jobs J
join Parts p on (p.PartID = j.PartID)|||

Thanks for your replies. I don't have any knowledge about foreign keys, etc. I attempted to set one up between the Jobs and Parts tables, but SQL Server Mgt Studio is still throwing errors when I attempt your query, carlop.

Thank you both anyway for throwing a pearl or two before this swine.

m