Error message when an SSIS package runs that is scheduled to run as a SQL Server Agent job: "An OLE DB error has occurred. Error code: 0x80004005"

I am receiving the following error in SSIS "An OLE DB error has occurred. Error code: 0x80004005" for each of my data flow tasks.

When I set 'delay validation' to 'True' for all data flow tasks and execute my packages the integration works okay.

However the SQL agent job doesn't run.

As far as I can tell, the reason for this is due to the 'to_update' temporary tables I have set up to act as a middle man. The Microsoft article below seems to back this up.

As the article says my next step would be to 'change the permissions for the Temp directory of the SQL Server Agent Service startup account. Grant the Read permission and the Write permission to the SQL Server 2005 Agent proxy account for this directory.' however I honestly have no idea where I would do this (I'm new to the world of SSIS!)

If someone could point me in the right direction that would be much appreciated.

2 Answers

There are a few things to take into account, the delay validation might be a red herring and nothing to do with the error or it might be that the Agent is calling an older version of the package that doesn't delay validation. So first of all make sure the package with the delay validation is deployed correctly and it is the one being run by SQL Agent.

Then, if the problem continues it could be permission or that the Temp directory is running out of space, worth checking it.

Finally, if it comes to permission problems then whoever is in charge of maintaining the DB, I'm assuming there is a DBA, should check what account is running the package from SQL Agent and make sure it has the right permissions.

another thing to check:

make sure the Run as is correct and if not running as 32 bit try it or viceversa.

After spending some more time it looked like the issue was actually related to disk space, the memory on the server I was using was running at 100% and the SQL agent jobs were running but very slowly and never actually ran successfully. I ended several processes and staggered when each of the SQL jobs run so they do not all trigger at once, and these are running successfully again.