I am developing an SSIS package that I am currently just running from BIDS that takes a file from one server and bulk inserts it into another server. When I run the package I get the following error:
Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\servername\Data Files\Narrative_2006.txt" could not be opened. Operating system error code 5(Access is denied.).
I am an admin on the both servers and I can browse to the file and open the file without issue. I also created a test step that copies the file to the destination server with a file system copy task. That it is able to do successfully. I can also point my bulk insert task connection mananger to that copied to destination server location and it runs successfully, but since the files are large I don't want to add the step to copy the files to the destination server first. I just want to be able to bulk insert from the source server.
I also tried (without the test step of copying the file) to deploy the package to my SQL Server and have it run as a scheduled job since my SQL Agent is a domain account with domain admin rights in case there is something with my account and it fails with the same error message.
If anyone can offer any guidance I would greatly appreciate it!
Thanks
Sherri