SSIS package execution failed inside SQL Agent

▲
0
▼

I have created one SSIS package which is I have imported into Integration services MSDB which is executed correctly from Integration services MSDB

But when I tried to create a JOB in SQL Server Agent and try to execute that job it is giving me following error

I'm unable to get it what is wrong from following error please help me.

(I have tried by adding config file and deleting config file when I add packge in SQL Server Agent)

Message Executed as user: cam\Package.Runner. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:05:01 AM Error: 2013-07-02 05:05:01.85 

Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Property" 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.  End Error  Error: 2013-07-02 05:05:01.85   
  Code: 0xC0016016     Source:       Description: 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.  End Error 
 Error: 2013-07-02 05:05:02.02   
  Code: 0xC0202009     Source: BloombergFTP Connection manager "WOPR\Fireball_PROD.sa"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'package.runner'.".  End Error  Error: 2013-07-02 05:05:02.02     Code: 0xC00291EC     Source: Execute SQL Task Goodhart Execute SQL Task     Description: Failed to acquire connection "WOPR\Fireball_PROD.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:05:01 AM  Finished: 5:05:02 AM  Elapsed:  0.266 seconds.  The package execution failed.  The step failed.
sql
ssis
asked on Stack Overflow Jul 2, 2013 by Neo

2 Answers

▲
0
▼

Ensure that credential used by "WOPR\Fireball_PROD.sa" BloombergFTP Connection manager is valid before trying the following.

Either

  1. Change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword and Set the Packagepassword if you want to encrpt the package.Specify this Password at command line in SQLSERVER Agent Job SSIS configuration.
  2. Change the SSIS Package ProtectionLevel property to DontSaveSensitive if you dont want to encrpt the package.Specify the credential in the SQLSERVER Agent Job SSIS configuration itself.
answered on Stack Overflow Jul 2, 2013 by S.M • edited Jul 2, 2013 by S.M
▲
0
▼

I ran into a similar problem. My SSIS package was running within SQL Data Tools without errors, but failed within SQL Server Agent, using a admin proxy. Looking into the logs of the job, the error message was something like "Failed to decrypt protected XML node "DTS:Property""

One of the many possible reasons (next to the one given by S.M.) for such an error is that the proxy doesn't have access to tables/databases you're referring to, while you yourself do have access to these tables.

In my case: I was writing stuff to a personal database which couldn't be accessed by the credentials underlying the proxy. Hence, when running the SSIS package within SQL Data Tools, I could access the tables, but when running the job within SQL Server Man. Studio, I couldn't. I changed the destination database of my package and the job could be runned.

This might be helpful for more people running into similar problems.

answered on Stack Overflow Jan 14, 2016 by Dendrobates

User contributions licensed under CC BY-SA 3.0