DTS Error (Export to Access2003)

  • Hi all, I have a problem with a DTS package I have just created.

    basically it exports a number of tables from SQL2000 to an Access 2003 database, the package runs ok when executed manually, but when I schedule it and run I get the following error.

    ========================================================

    Executed as user: BIGBROTHER\SYSTEM. ...art: 

    Delete from Table `K:\db1`.`Location` Step  

    DTSRun OnStart: 

    Delete from Table `K:\db1`.`ProgArea` Step  

    DTSRun OnStart: 

    Delete from Table `K:\db1`.`SubSection` Step  

    DTSRun OnStart: 

    Delete from Table `K:\db1`.`ServiceUnit` Step  

    DTSRun OnError: 

    Delete from Table `K:\db1`.`Location` Step,

    Error = -2147467259 (80004005)     

    Error string:  [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. 

    Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.     

    Error source:  Microsoft OLE DB Provider for ODBC Drivers     

    Help file:       

    Help context:  0     

    Error Detail Records:     

    Error:  -2147467259 (80004005);

    Provider Error:  1023 (3FF)     

    Error string:       

    Error source:       

    Help file:       

    Help context:  0     

    DTSRun OnFinish: 

    Delete from Table `K:\db1`.`Location` Step  

    DTSRun OnStart: 

    Delete from Table `K:\db1`.`S... 

    Process Exit Code 7. 

    The step failed.

    ========================================================

    Does anyone know the solution?

    Cheers

  • Sounds like the logon the Agent is running under doesn't know about / have access to that drive.  Have you tried using the UNC path?  Is the server/agent on a different machine?

     

  • Sounds like your ODBC driver is not accessed through a system DSN. If you have access to your ODBC Data Source Administrator (Admin Tools), set the DSN as a System DSN and not as it is now (either User of File DSN).

  • Hi, and thanks for the replies, it turns out that it was due to the login I used to access the server.

    Once I used the local admin, it seemed to work.

    Cheers for the help.

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

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