SQL Server Performance

How to obtain a returnvalue from DTSRun utility

Discussion in 'SQL Server DTS-Related Questions' started by Bartuls, Jun 14, 2004.

  1. Bartuls New Member

    Is it possible to obtain a return value from the DTSRun utility?
    I want to launge a DTSJob from a batchfile.
    In the same batchfile I want to check the DTS package did execute without failure.

  2. satya Moderator

    Check the status of the DTS scheduled job by using SP_HELP_JOBHISTORY, refer to books online for more information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Bartuls New Member

    Thank you for replying.

    Using SP_HELP_JOBHISTORY is possible as long as you are operating in the Query Analyser or in a stored procedure.
    I am running the DTSRun utility from a DOS batch file (yes they are still used by some modern systems :[ ).
    I want to build some error handling in the batchfile and then it would convenient if the DTSRun utility would return some kind of a return code indicating the result (succes of failure) of the DTS package.

    The other option I came up with is having the DTS package writing a file somewhere in case of an error and the checking in the batch file whether that file exists or not.
    But I think the return code from the DTSRun utility is a much better solution, only I did not find any information whether teh DTSRun utility does return something.

  4. Raulie New Member

    Why don't you just record the status in the Windows Event log. You can do this under the package properties Logging tab or as a switch in the DTSRUN<br /><br />/W NT_event_log_completion_status [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. Bartuls New Member

    Thanks for the suggestion Lazy DBA (nice nick name <img src='/community/emoticons/emotion-5.gif' alt=';)' />), but I already record the status in the Windows Event log.<br /><br />The problem is that this package is executed by a sophisticated batch scheduler (OPS). This scheduler starts batch jobs by executing DOS batch files (sophisticated? My 'you know what'!).<br /><br />In the DOS batch file the result of the execution of the package have to be evaluated, so I need some feedback, accessable in a DOS batch file, relating to the result of the execution of the package (OK or failed). I have searched for some examples how to read the event log from a DOS batch file, but I did not find it (yet). <br />The most convenient way to obtain the result is when the DTSRun utility gives some kind of return code like:<br /><br />ReturnCode = DTSRun etc....
  6. Bartuls New Member

    I found it.
    DTSRun returns an errorlevel.
    errorlevel = 0 indicates the execution is OK.
    other errorlevels indicate execution is not OK.

    In the batch file:

    DTSRun /S %DTSServerName% /N %DTSJobName% /E
    IF %errorlevel% EQU 0 GOTO VerwerkingOK

    :VerwerkingFout
    Set OPSExCode=1
    GOTO Einde

    :VerwerkingOK
    Set OPSExCode=0
    :Einde

    Thanks for the tips
  7. Jes New Member

    <br />Similarly how to set the returnValue from DTSRun Utility to a CA-7 batch scheduler...just like u've done for OPS..?<br /><br />I've been breakin' my head over this and am goin' bonkers ...<img src='/community/emoticons/emotion-3.gif' alt=':O' />(
  8. satya Moderator

Share This Page