I have a SSIS package that loads data from a Sharepoint List using s Sharepoint List Source component.
When i run the package from V.S NET it works well and the data destination is to a sql table.
But when i create a job for it and schedule it i get error:
Failed to open package file "Package.dtsx" due to error 0x80070005 "Access is denied."
I spent time looking at the history of the job and all i could see is that the job failed saying it was was invoked by me but i noticed its been executed by another USER :(
After pulling out my hair and making sure i granted the acct running Sql server agent admin access but still didn't work.
I later came across PROXIES you can find them under the Sql Server Agent Folder
There s also a a Proxy tutorial here.
After i configured the proxy and used it to run the job as specified in the articles above. The job worked like a charm:)
** Remember you have to create credentials first before you see them when creating a job.To do that just go to your root folder in SQL SERVER Management Studio and right click > Security > New > Credentials.
Hope that helps and keep integrating 