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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.