Post reply

Sceduled DTS package failure

  • April 15, 2004 at 1:40 am

    #59826

    I have a bit of a weird one here.  I have a DTS package that pulls in data from several tables in an Access database and loads them in to the relevant tables in my SQL Server database, no problem. 

    The Access database does not reside on the same box as SQL Server but this does not seem to be a problem when running the package manually, however, as soon as i schedule the package to run it fails saying that the path to the Access DB is invalid.

    I've mapped a path on the SQL Server to the Access DB but still no joy.  I thought it could be because i was setting it all up from my PC and not on the server so i tried doing it directly on the server but had the same results.

    Does anyone know of any reason why running the package manually works but not when its scheduled?  My work around at the moment is a scheduled task that copies the Access db to the server just before the package is scheduled to run.


    Growing old is mandatory, growing up is optional

  • April 15, 2004 at 2:50 am

    #502862

    I come across two common problems that may be relevant. 

    1. When you run a package using EM from a client then the connection used is taken from the client (i.e. path names / ODBC datasource ) but when the package is scheduled or run from a job then the connection details are taken from the server.  For example if you want to use a DSN called MyAccess then you would need a the MyAccess DSN on the client for when you run via EM and an identical MyAccess DSN when you run from a job.

    2.  When the package is run from a job the process is run by the SQL Agent, this is different to the user that the dts is manually run as.  Often the Windows system account will need permission to access the file for the link to access to be allowed.  Take a look at the SQL Server Agent properties to see what the job will be run as.

  • April 16, 2004 at 9:31 am

    #503155

    1.You can't use mapp drivers in dts pkgs.

    2.Use UNC path like \\serverName\folder\subfolder\

    JFB

  • April 19, 2004 at 1:30 am

    #503265

    Many thanks to both of you, the issue is now resolved and my users are actually happy, for the moment!


    Growing old is mandatory, growing up is optional

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply