0

I have spent the past two days trying to find a solution for this error but I am still struggling to understand the failure.

I am fairly sure the issue is to do with the SQL Agent accessing a Network Drive as I have created identical SSIS packages with the only difference being the location of a flat file, when it runs the version on a Network folder it fails with very little indication of the error.

Its worth pointing out both of the packages run in BIDS, its the SQL Agent that results in the error

I have followed the below link and created a Proxy account and I can confirm the SQL Agent runs under this logon (it says in the Job History)

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

Question is - What is the failure and why? It clearly seems to be something with the network file but I cannot understand what step I am missing?

Thanks

Community
  • 1
  • 1
Chris
  • 1,766
  • 1
  • 21
  • 36
  • 1
    How are you accessing the file on the network drive? If you are accessing a mapped drive (f.ex. M:\folder\file.ext ), keep in mind that mapped drives are user specific, not machine specific. Better to access with UNC notation "\\server\sharename\..." – larsts Sep 14 '15 at 10:52
  • I did try that how SSIS doesnt like it in that format? it says 'a valid file name must be selected'...but as I am using the same logon details for Agent should that matter? – Chris Sep 14 '15 at 11:10
  • Does the proxy account have read/write access on that share? – Eric Hauenstein Sep 14 '15 at 12:41
  • Apologies for the stupid question but how would I check that? – Chris Sep 14 '15 at 12:46
  • Just to add, the proxy account is my logon, the same one that works within SSIS – Chris Sep 14 '15 at 12:46
  • As far as access; if your SSIS package runs with your credentials, it should be sufficient to test that you can open the network file using the path specified, f.ex. in windows explorer. – larsts Sep 14 '15 at 16:01
  • To confuse things further, I just wrote a script that opens Excel and downloads the network file to the C drive using VBA, it works fine on SQL Agent. For now I have this step grabbing the file and SSIS just looking in the C drive, its stupid but its working =| – Chris Sep 14 '15 at 16:03

0 Answers0