Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: DTS job fails when scheduled from SQL Agent

    Folks,
    I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
    The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..

    Executed as user: mydomain\mylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text 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: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.

    How come it loses the path to the file when I dont run it directly?
    Cheers
    Mick

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: DTS job fails when scheduled from SQL Agent

    DTS runs in the context of the client machine when you run it directly. That means that if you run it from Enterprise Manager on your local PC then it uses the settings, drive mappings and ODBC drivers of your workstation. When a DTS package is run by SQL Agent, it uses the settings from the Server. You have to ensure that the server has all the settings that your local machine does.

    Be sure not to use mapped drives to specify file locations -- use UNC instead. This is because a mapped drive only exists in the context of a logged in user. SQL Agent is a service and thus is not logged in.

    I hope this makes some sense; I still find this a difficult topic to explain clearly even after dealing with it for five years.

    Regards,

    hmscott

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Thanks for that,
    Thing is I have done every step from package creation to scheduling ON the server itself through terminal services. I thought SQL Agent would be aware of these server-based system DSN's. Ill have a go at UNC then.
    Cheers
    ML

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://support.microsoft.com/default...;EN-US;Q269074 - KBA to schedule DTS as a scheduled job and troubleshoot any issues.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Thanks folks,
    I used UNC text file sources instead of odbc text connections. Worked great.
    Cheers
    Mick

Posting Permissions

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