Search code examples
sql-serverssissql-agent

Moving Files with SSIS package, scheduled by a SQL Server Agent Job

I developed quite a complex SSIS package. Part of it Moves Files from the "c:\Flags\" folder to the "c:\Flags\Busy\" folder.

I deploy the package under the SSIS Catalog.

When i right click and start the task manually, under my own credentials, it works fine. (i am administrator)

When i schedule the task (to run say every 30 mins) using the SQL Server Agent, it it fails in the part where the files gets moved. The SSIS Package runs under the "SQL SERVER AGENT ACCOUNT", by default.

Any suggestions or pointers how i can resolve the issue? (i did google the issue)

Maybe change the account it runs under (no options here from the GUI) or somehow give the "SQL SERVER AGENT ACCOUNT" rights to move files?

Solution

  • The agent account will almost always have insufficient rights to this type of task depending on where exactly the files are located. You could give more rights to the account but this is not recommended. I suggest a proxy.

    http://sqlmag.com/blog/sql-server-dba-tip-credentials-and-proxies