I'm setting up an ETL process using SQL Server 2014 and SSIS. My Bulk Insert Task is utilizing a source file and format file on ShareA targeting a DB on InstanceA. The package is run via SSA job on InstanceB. Both InstanceA and InstanceB are running the same service account for the Agent. The service account has
bulkadmin and read/write privs on the target table. It also has appropriate permissions on both the share and folder via NTFS.
When the SSA job runs on InstanceB, the job fails on the step with code 5 for Access Denied attempting to access the source file. However, if I run the
BULK INSERT T-SQL generated by the task in a job on InstanceA it succeeds, confirming share/NTFS permissions are OK.
Am I forced to run the SSIS package on my target server InstanceA to allow
BULK INSERT to function or are additional permissions needed in order to run on an alternate server?