DTS fails when trying to access network drive from SQL Server Agent job

• Tom Hegh

  SSC Enthusiast

  Points: 135

  More actions

  March 8, 2006 at 1:51 am

  #70295

  Hi,

  I have a DTS running daily to collect data from sql server tables, transform them to excel files and save these on the server.

  Now, the customer wants these files to be copied to a network drive, so everybody can freely access these excel files.

  I've mapped a network drive with correct permissions

  When I add some small code to copy the file to this directory on the network, it works perfectly. BUT only when the DTS package is executed manually from the Enterprise manager.

  When I schedule this job to be run from the SQL Server Agent, it fails.

  My guess is that the service isn't seeing the network drive, how do I overcome this problem ?

  Please advice,

  I've changed the user policies, all users can have full access to the network, they are members of the administrators group and are also setup in sqlserver security.

  Any ideas and/or suggestions would be welcome,

  Regards,

  Tom

• Tom Hegh

  SSC Enthusiast

  Points: 135

  More actions

  March 8, 2006 at 2:09 am

  #625335

  OK, people, SOLUTION found !!

  When I include the full network path, instead of the mapped network drive, it works perfect.

  I used the syntax sDestFileName = "K:\FURNACE_DATA_WEEK_10.XLS"

  It should have been sDestFileName = \\FILE_SERVER\DATA\FURNACE_DATA_WEEK_10.XLS

  Since the Sql Server Agent job isn't capable of connecting to drive K as a service, adding the full network path solved my problem.

   

  FYI,

  Regards,

  Tom