Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Unhappy Unanswered: need to re-set path to access file each time I import it in DTS task

    I have struggled with this one for a while.

    I have a DTS task that imports an Access file into a table in SQL Server. The Access file has security setup since it is the backend for a HR application.

    I defined my Access file connection in DTS by setting the path the the mdb file, supplying user and password and under advanced properties I add the workgroup file to JetOLEdbystem Database. Once set it works fine.

    This is the scenario:

    Access file is copied from another server nightly to the server running DTS
    DTS task is scheduled and runs each night opening the file and importing some of its data into SQL Server

    Problem:
    as soon as a new copy of the Access file is put on the SQL Server, the DTS task fails, saying the file mdb file is already in use or the user set in the connection properties is not valid. The only way I can get it back to work is to open the connection properties, re-set the path (it does show the right path, but when you 'Browse' to it, it ends up showing the My Documents folder. Then I have to go into advanced properties, remove the workgroup file, click OK, go back into advanced properties and add the workgroup file, click OK and then it works again.

    It is not a network/share permission etc. issue as outlined in MS Knowledge Base Article: How to Run a DTS Package as a Scheduled Job

    I think it is either related to the fact that the file is copied each night (new update date?) or something with the workgroup file.

    Anyone seen this before? I clueless and can't run this job automatically...

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Are you executing the COPY and ACCESS the MDB file in same job, if so try to re-define them seperately and execute in seperate intervals.

    Check from system TASK MANAGER and see what process is holding MS ACCESS database while this process is in progress.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jun 2003
    Posts
    2
    Hi,

    I run a simpe NT Task to copy the file from one server to another. This happens after all users left the application that uses this MDB file. Also the DTS job doesn't start until 2 hours after the copy of the file finished.

    The odd thing is that if I manually unset/set the path to the file and the workgroup file in the connection properties it works fine...

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try to use PROFILER and see activity on SQL Server during this process.

    ALso check whether any issues on network.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •