Why wouldn't SSIS package running in SQL Server Agent job transfer files to a network folder?

5
votes

I've run into this twice now where I can run my SSIS package in BIDS and Integration Services but it fails when it's run through SQL Server Agent. Both of these packages transfer a file as a final step to a folder on our network.

The error I receive is Could not find a part of the path then the path and file name. When I schedule them in Windows Task Scheduler they execute fine but I'd rather have them run through sql server agent.

Has anyone run into this issue and found a work around? Is there a setting that I'm missing in sql server?

Any help would be appreciated.

1
ssis

1 Answers

8
votes

You need to run the SQL Server job using a proxy account that is configured to run jobs of type SQL Server Integration Services Packages. The jobs usually run under SQL Server Agent Service account, which does not have access to network folders. To access network folders, you need to set up a proxy with a domain account credentials (preferably) so you can access network path.

Below answer in SO question has the detailed steps on how to set up a proxy account

How do I create a step in my SQL Server Agent Job which will run my SSIS package?