Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004

    Unhappy Unanswered: DTS job fails when Scheduled?

    I have a DTS job that moves a text file from a workstation to the SQL server, transforms the data, pumps into a table and then archives the file. It runs fine within EM on the SQL Server, but once scheduled fails immediately on Step 1. I have logged the error as follows:

    ************************************************** **********
    The execution of the following DTS Package succeeded:

    Package Name: Load LaserGauge Data Daily
    Package Description: (null)
    Package ID: {6E4E8DDC-3864-43E9-B82D-300FCBCCAE63}
    Package Version: {4DFF3F73-1653-4896-8DA3-A4246D41EE51}
    Package Execution Lineage: {E17CD8A1-9754-413B-B129-093711326C27}
    Executed On: SEQUEL
    Executed By: SQLSERVICE
    Execution Started: 3/26/2004 1:12:17 PM
    Execution Completed: 3/26/2004 1:12:17 PM
    Total Execution Time: 0.031 seconds

    Package Steps execution information:

    Step 'DTSStep_DTSDataPumpTask_1' was not executed

    Step 'DTSStep_DTSActiveScriptTask_1' failed

    Step Error Source: Microsoft Data Transformation Services (DTS) Package
    Step Error Description:The task reported failure on execution.
    Step Error code: 8004043B
    Step Error Help File:sqldts80.hlp
    Step Error Help Context ID:700

    Step Execution Started: 3/26/2004 1:12:17 PM
    Step Execution Completed: 3/26/2004 1:12:17 PM
    Total Step Execution Time: 0.031 seconds
    Progress count in Step: 0

    Step 'DTSStep_DTSActiveScriptTask_3' was not executed
    ************************************************** **********

    I have taken the following actions to resolve:
    1. Tested DTS job on local SQL Server - executes without error
    2. Checked security: The DTS job is owned by me, and I am a member sysadmins, etc... The SQL Job I have tried several differing accounts (Mine, Domain Administrator, and most recently a proxy account I setup called SQLDTSuser) All have failed with the same error.
    3. I have checked the SQL Agent account to ensure that it has necessary rights, stopped and started SQL Agent service, same results.
    4. As mentioned I setup a Proxy Account which is in Domain Admins, and Sysadmins on SQL and changed SQL Agent Properties to use this account. result was same error.
    5. Set Workflow Properties on the DTS job ActiveXtask to "Execute on Main PAckage Thread" - result was same error.

    I'm at a loss I have to be overlooking something obvious, but need someone to hopefully point me in the right direction. Thank you in advance for any help I can get!

    The platform is SQL 2000 and I've included the first activex Task code below:

    ************************************************** **********
    ' Visual Basic ActiveX Script
    '************************************************* **********

    Function Main()
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim filePath
    filePath = DTSGlobalVariables("gv_SourceDir").Value & "\" & SourceFileName
    If fso.FileExists(filePath) Then
    fso.MoveFile filePath, DTSGlobalVariables("gv_WorkDir").Value & "\" & DTSGlobalVariables("gv_WorkFileName").Value
    Main = DTSTaskExecResult_Success
    Main = DTSTaskExecResult_Failure
    End If
    End Function

    Function SourceFileName
    Dim fileDate
    fileDate = DateAdd("d", -1, Date)
    SourceFileName = "LASERDATA" & PadLeft(DatePart("d", fileDate), 2) & PadLeft(DatePart("m", fileDate), 2) & Right(DatePart("yyyy", fileDate), 2) & ".CSV"
    End Function

    Function PadLeft(Value, Length)
    Dim retval
    retval = Value
    Do While Len(retval) < Length
    retval = "0" & retval
    PadLeft = retval
    End Function

  2. #2
    Join Date
    Jan 2004
    Ottawa, Canada

    Re: DTS job fails when Scheduled?

    My initial thought is the line

    filePath = DTSGlobalVariables("gv_SourceDir").Value & "\" & SourceFileName


    A scheduled task runs on the server not the workstation and it is unlikely that your server actually has the same path defined as your workstation.

  3. #3
    Join Date
    Mar 2004
    Thanks for the reply Simsoph, I thought I wasn't going to get any help there for awhile.

    We are able to manually run the DTS job on the SQL Server console and it completes successfully. That tells me that the paths used, which are UNC and stored in a global variable for the DTS job, are working. I've followed all the TIDs mentioned for security and the like but this one has me stumped?


  4. #4
    Join Date
    Mar 2004
    I'll give the credit to Simsoph as I have been able to resolve my problem. Simsoph mentioned the file paths, which although they weren't the problem the global variables were! For some reason when scheduling via the DTS menu the GV weren't getting saved as part of the job. I ran the GUI DTS util and set the GV's within the advanced section and scheduled from it instead and presto the scheduled job now runs. Very strange I hope this helps someone else in the future.


  5. #5
    Join Date
    Aug 2002
    Its always better to use UNC file paths than specifying drive letters and names.
    --Satya SKJ
    Microsoft SQL Server MVP

  6. #6
    Join Date
    Apr 2004
    have you noticed that UNC paths suffer some strange performance loss --- better to map a drive for the performance and add the mapping of the drive into the application.

    I have been working on a VB6.0 project which uses the DTS objects to crate a package and add steps for a package for each file in a folder. There can be up to 1000 files in this folder. I was getting ;

    The execution of the following DTS Package failed:

    Error Source: Microsoft Data Transformation Services (DTS) Package
    Error Description:Package failed because Step 'X:\EventLog\Events_130404004649_130404010050_0000 .tsv' failed.
    Error code: 80040428
    \Error Help File:sqldts80.hlp
    Error Help Context ID:700

    Now I have added "Execute on Main PAckage Thread = true" to each step in the package and have had none of these errors since.. I have done 3 full runs of 2000 files now. So I am hoping that has fixored it.
    Last edited by shuntor; 04-15-04 at 05:10.

Posting Permissions

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