I am writing a stored proc that calls a SSIS package, that works on its own, but gives me the following error when I run it from a stored proc: Failed to decrypt protected XML node "DTS:Password" 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.
The SSIS package runs properly if I run it from the commandline as: dtexec /f "L:\Data\LPS\_EZPikins\CLAIMSIII__LPS_Extract_DO_NOT_Execute.dtsx"
This is the proc:declare @ssisstr varchar(8000), @packagename varchar(200)set @packagename = '"L:\Data\LPS\_EZPikins\CLAIMSIII__LPS_Extract_DO_NOT_Execute.dtsx"'set @ssisstr = 'dtexec /f ' + @packagenameDECLARE @returncode intEXEC @returncode = xp_cmdshell @ssisstrselect @returncode
In you package, what is your Protection Level? I believe you have "Encrypt all with password".
Take a look: http://msdn.microsoft.com/en-us/library/ms141747.aspx
I think it has to do with the account I am running on the server, it has admin sql permissions and therefore trusts me to run this. When I run it from a stored proc, sql server tells me "bad login for user". I can force the issue by editing the .dtsx file to include the password in the connection strings, but this is cumbersome as I have to edit the file everytime I save it from the GUI. I have tried all the security combinations, to no avail. My question is: How do I get the sql password through the stored proc call? Would it be a /set on the dtexec command line through the sql xp_cmdshell? And if so, what is the correct syntax of the /set?