SQL Server 2008 R2 Errors running SSIS Package through the Job Agent

1

I have an SSIS Package that I created through the Import Export (32-bit) Tool. When I executed the package manually through the Execute Package Utility the package run successfully with no issues. However when I try to run the package through a Job Agent in SSMS I keep getting errors. The primary error I get seems to be:

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.

I'm using SQL Server 2008 R2.

I have researched this error to some degree and I think it has something to do with the package protection level. I feel like I've tried the configurations that make the most sense but none seem to be working for me. The Options are:

  • Encrypt sensitive data with user key
  • Do not save sensitive data
  • Encrypt sensitive data with password
  • Encrypt all data with user key
  • Encrypt all data with password
  • Rely on server storage and roles for access control

I feel like Ishould be using the last option here (Rely on server storage...) because I prefer to use SQL Server Authentication. I use SQL Server Authentication on the 'Choose Destination' window of the SQL Server Import and Export Wizard, and similarly I use this with the same username and password when I create the Job Agent in SSMS on the General Tab of the Job Step Properties. Is it possible that there is something that I need to add to the User I'm using in SSMS - even though it works outside of SSMS?

Something else I wondered that might have an impact is having the option "Drop and Re-create destination table" checked in the Column Mappings window of the Import and Export Tool. I was using a stored procedure to remove the tables before executing the Job Agent and I feel like ti was working at one point - could that have something to do with it?

Again the thing that baffles me most is that it runs no problem when I execute it manually through the 'SQL Server Execute Utility Package' tool.

I've included images of some of the windows I mentioned above if that helps.

enter image description here

enter image description here

enter image description here

sql-server
ssis
sql-server-2008-r2
sql-agent-job
ssis-2008
asked on Stack Overflow Nov 10, 2015 by JasonR • edited Nov 10, 2015 by AHiggins

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0