I have a package that uses a service account to connect to an Oracle server and the username/password are stored in the package. For this reason the package is set to ProtectionLevel=EncryptSensitiveWithPassword. I am attempting to push the package to the server with an Azure DevOps pipeline using the dtutil command line utility, including the /Decrypt argument with the package password. Here is the command line (I replaced sensitive info with "xxxxx"):
dtutil /FILE Proto_Fleet_Direct_Parts_WO_Vendor_Extract.dtsx /Decrypt xxxxx /COPY SQL;Proto_Fleet_Direct_Parts_WO_Vendor_Extract /Q /DestS xxxxx
When I execute this command I get this error:
The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
As a troubleshooting step I removed the package password and set the ProtectionLevel to EncryptSensitiveWithUserKey. Then the package loads to the server without error, but of course it errors on run since the server's account has a different user key than me.
A workaround would be to use a Linked Server that has the privileges I need to access the Oracle server. This would allow me to remove the service account from the package and set it to EncryptSensitiveWithUserKey. However, we don't currently have that linked server and I was instructed by our DBAs to try to get this other method to work first before we go down that route.
It turns out our issue was the password itself. The password had two special characters which are reserved characters in the Windows command line, "%" and "&". Since you cannot put the password in quotes, one (or both) of those special characters in the password was being interpreted by the command line. I changed the password to use a different special character which is not reserved, and then the dtutil command worked. I suppose you might also be able to escape those special characters instead of changing the password, but I didn't try that.
I wanted to share the solution in case it can help someone else.