Misleading error message
Hi
Following a dumb error on my part, I had a problem this morning where a package was failing with an error message that bore no relation to the actual cause - this post is just a comment so that, if possible, the SSIS team can make the error message more accurate and anyone else encountering the problem has a reference.
I have a master package that is executed from a SQL Agent job. The master package triggers a series of packages through Execute Package Tasks. The packages are held in the file system and have the ProtectionLevel set to EncryptSensitiveWithPassword.
Yesterday, I deleted an Execute Package Task and the associated Connection Manager. I uploaded the amended master package to the server and let SQL Agent run it last night. This morning my logging showed that a series of error messages stating "Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information." had been logged. Also the Execute Package Tasks in the master package were being reported as having failed, although the sub-packages themselves actually ran succesfully.
I tracked the problem down to the fact that after uploading the amended master package I had not opened the SQL Agent job, visited the Data Sources tab on the step that runs the package and resaved it. I therefore assume that the SQL Agent job had a reference to a Data Source that no longer existed in the package. After I had fixed this the job ran succesfully.
My comment on this is that the error message " .. the pasword was not specified or not correct" has nothing to do with the real cause of the problem.
I'd be interested to hear anyone's comments on this.
Ed
[1820 byte] By [
EdAllan] at [2007-12-24]
I got the same error but in a different way. It has nothing to do with the job system or (I think) a user error. I'd like to see whether someone from MS can reproduce and comment on this.
Basically it looks as though if you have a data source that uses a password, and you choose to encrypt sensitive information with a password, you can't successfully use master/child packages. Here are steps to reproduce the error in my environment, boiled down as much as possible.
On workstation with client tools only installed:
New solution: MasterPkgTest
New data source: dsn_with_pwd (any data source; test it to make sure the password is good. I've tested with the .net ODBC provider and with the SQL Native Client.)
Rename Package.dtsx: Child.dtsx
In child package:
New connection from data source: dsn_with_pwd
protection level: EncryptSensitiveWithPassword (set password)
execute child, check for errors
Result: no errors, no warnings
New package: Master.dtsx
In master:
protection level: EncryptSensitiveWithPassword (set password to same as child)
Add execute package task: child.dtsx (new connection to existing file)
Execute master, check for errors
Result: child succeeds, but master thinks the child failed. No error message is shown, but you do get this warning:
Warning (code 0x80019002) The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
build and deploy child and master to a server with SSIS installed;
Execute Child on SSIS server:
dtexec /FILE "[yourpath]\Child.dtsx" /DECRYPT [your password] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
Result: child succeeds
Execute Master on SSIS server:
dtexec /FILE "[yourpath]\master.dtsx" /DECRYPT [your password] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
Result:
Error (code 0xC001405F) Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.
Warning (code 0x80019002) The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
In all cases the child succeeds. In my simplified example I gave it no tasks to complete, but you could add something that requires a connection to dsn_with_password, and it will work. But the master reports failure nevertheless.
If you change the protection level to EncryptSensitiveWithUserKey, or if you change your Data Source to use a trusted connection, you get no error. But of course you can't deploy your package with that protection level, and for some data sources you can't use a trusted connection. Is there a solution to this situation?
This also occurred with the following scenario:
A package was created that was password encrypted. A second package that executed the first package was created with UserKey encryption. Despite the original user changing the encryption on the second package to Password, the first package can not be executed as a SQL job without throwing this error.
I have a feeling that I'm going to have to completely re-create the second package with the appropriate encryption.
