asked
SQL Server Agent 2005 and SSIS
Hi, I have a SSIS 2005 job that imports data from a text file that is on a network share (from a Linux server). Processing the job in SSIS is fine. However when I create a scheduled job in SQL it fails. I have tried using a mapped network drive, the full UNC, running the SQL Agent as a domain user but nothing works. Error message when using the mapped network drive is "cannot open the datafile...". Using the UNC the error msg is "the file name specified in the connection is not valid". The Linux share is open as read only to all users. Any ideas??
SQL Jobs authenticate using the account that SQL SERVER AGENT is running under. The fact that you are trying to access the file on a Linux server is what I am unsure of. I would suggest creating the user on your linux server that your SQL SERVER AGENT account is running under and give that user rights to the share.
@andypee Mapped network drive will not work because it is user specific. When you use the **map network drive** feature, it is mapped for you (the box logged in user). The only way to make it available to SQL Server Agent domain account is to login to the box using that account and then map network drive (which is a pretty crazy idea). Does SSIS work with either mapped drive or UNC? If it does work with UNC then you have to examine the network sharewise differences between your and SQL Server Agent accounts. You can also temporarily set the agent to work under your credentials just to isolate the problem to network share permission issue. If it starts working then permission is definitely the cause.
Hi Oleg...you are correct, the mapped network drive doesn't work when processing the job as SQL Server Agent. However both the UNC and mapped network drive will work in SSIS. The issue was on the Linux share side and the job will now execute successfully when run as the SQL Server Agent as Local System (not a domain user). Cheers.
