How to obtain a returnvalue from DTSRun utility | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to obtain a returnvalue from DTSRun utility

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.
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.
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.
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=’:)‘ />]
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….
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
<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’ />(
Jes
Please continue discussion on your individual post, as it will be quicker than posting on a continued one.
Referhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10260 link. 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.
]]>