Running an SSIS package as a job in SSMS

Question

Post reply

Running an SSIS package as a job in SSMS

bajanpoet

Mr or Mrs. 500

Points: 591
More actions
November 25, 2010 at 11:47 am

#241742

The error I get whenever I try to run an SSIS package I created from SSMS is, in part "Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.""
In my research I see that some people have advocated changing the ProtectionLevel to "Don'tSaveSensitive." Is this recommended? What are the ramifications of this? Do I have to put in a password every time this is to run now (not practical to manually enter passwords because the job is scheduled for early morning before work as well as in the middle of the day.)
How do I get my packages to run successfully????

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

DontSaveSensitive is OK if you use Windows Authentication on your connections.

How is your package constructed? What sort of connections do you use? Are there passwords needed?

edit: oh yeah, what is your current ProtectionLevel?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

bajanpoet Mr or Mrs. 500 Points: 591 More actions · Answer 2

da-zero (11/26/2010)
DontSaveSensitive is OK if you use Windows Authentication on your connections.
How is your package constructed? What sort of connections do you use? Are there passwords needed?
edit: oh yeah, what is your current ProtectionLevel?

My connections? The ADO.NET connection is used in both Data Flow Tasks that pull data into two Flat File Connections. They feed an FTP Task that has an FTP Connection. The ADO.NET and FTP connections have passwords.

The current protection level is EncryptSensitiveWithUserKey.

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 3

bajanpoet (11/26/2010)
da-zero (11/26/2010)
DontSaveSensitive is OK if you use Windows Authentication on your connections.
How is your package constructed? What sort of connections do you use? Are there passwords needed?
edit: oh yeah, what is your current ProtectionLevel?
My connections? The ADO.NET connection is used in both Data Flow Tasks that pull data into two Flat File Connections. They feed an FTP Task that has an FTP Connection. The ADO.NET and FTP connections have passwords.
The current protection level is EncryptSensitiveWithUserKey.

EncryptSensitiveWithUserKey:

Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails.

as da-zero said,if your connections are using windows authentication. then you can change the Protection level to DontSaveSensitive and then run the package.

refer url for more details:

http://msdn.microsoft.com/en-us/library/ms141747.aspx

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Sharath has explained the most important things.

If you wish to keep your connections with a password, you should try using the "Rely on Server Storage for Encryption" when your package is deployed to the server (to the MSDB database, not the file system).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP