Re: SSIS package won't run within a Stored procedure

Hello John D.,

To quote from the KB - "...when a different user account or a different computer is used to run the SSIS package, the EncryptSensitiveWithUserKey value of the ProtectionLevel property is engaged, and the Password property of the SSIS package remains encrypted. When this occurs, an error message is generated."

"To resolve this behavior, change the value of the ProtectionLevel property in the SSIS package." - So change it from the default of EncryptSensitiveWithUserKey to something which is not tied to a specific user.

You do not develop under the same user account as would be used by the stored proc from SQL, so the decryption fails.


I have a SSIS package that was migrated from a DTS package. If I
rightclick
on the package & click run package within management studio, it
executes
correctly. If I run the package in a stored procedure using DTEXEC
utility I
get the following error:
Description: Failed to decrypt protected XML node "PackagePassword"
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 t
hat the correct key is available.
I did some searching & came across a KBB article that said it had to
do with the protectionlevel property. I have it set to
EncryptSEnsitiveWithUserKey which is supposed to be the default.
http://support.microsoft.com/default.aspx?scid=kb;en-us;904800

Anyone come across this error before & have a resolution?

Thanks for the help!
John


.

Relevant Pages

  • Re: Connection Manager Passwords
    ... EncryptSensitiveWithUserKey - means that the user that creates the package ... I ended up going into the SQL Job Agent, ... password into the Connection string under the Job Properties/DataSources ...
    (microsoft.public.sqlserver.dts)
  • RE: Calling a package within a package...
    ... If you use "EncryptSensitiveWithUserKey", the package is encrypted with your ... The sql agent job might be running under a different user ... The other options are to store the package using ServerStorage. ... execute the package in the designer, ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL 2005 SSIS Issue
    ... Ok so this error is telling you that something sensitive in the package has been encrypted. ... It does this because of the ProtectionLevel property of the package. ... The default for the ProtectionLevel is EncryptSensitiveWithUserKey which means that unless the same user fires the package as created it then the sensitive info will not be decrypted. ...
    (microsoft.public.sqlserver.dts)
  • RE: Run a ssis package on SQL Server 2005 Error
    ... Failure saving package. ... This is most likely a known issue in SQL Server 2005, ... Set the SSIS Package ProtectionLevel property to ServerStorage ...
    (microsoft.public.sqlserver.dts)
  • RE: Run and Edit DTS in SQL Server 2005
    ... Charles Kangai, MCT, MCDBA ... we've the schedule to migrate all DTS to SSIS but the run DTS in ... Packages folder in Solution Explorer and choose New SSIS Package). ... The SSIS package you just created will then go and execute your DTS ...
    (microsoft.public.sqlserver.dts)