Dtsrun Error Level
Contents |
van GoogleInloggenVerborgen veldenZoeken naar groepen of berichten
Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 AdministrationBackup and Recovery Cloud High Availability Performance Tuning execute ssis package from command line with parameters PowerShell Security Storage Virtualization DevelopmentASP.NET Entity Framework T-SQL Visual Studio dtexec install Business IntelligencePower BI SQL Server Analysis Services SQL Server Integration Services SQL Server Reporting Services InfoCenters dtexecui Advertisement Home > Business Intelligence > DTS: Error Handling Revealed DTS: Error Handling Revealed Oct 31, 1999 Brian Lawton and Don Awalt | SQL Server Pro https://groups.google.com/d/topic/microsoft.public.sqlserver.dts/g0AILeBmxXY EMAIL Tweet Comments 2 Advertisement Downloads 6196.zip Errors got you down? Here's help! If you've programmed with Data Transformation Services (DTS), you'll probably agree that error handling is one of the most confusing and challenging problems the DTS developer faces. In our July 1999 article, "The DTS Development Guide," we looked briefly at http://sqlmag.com/business-intelligence/dts-error-handling-revealed the built-in error-handling options the Package Designer offers. This month, we discuss in depth some programmatic opportunities for using the DTS Object Model to handle errors. We also examine DTS event handling. The DTS Perspective on Errors DTS doesn't consider an error to be a definitively right or wrong result, but rather the status of work accomplished. DTS leaves the interpretation of that status to the developer. Thus, developers can control the execution of a package, task, step, or transformation by monitoring the state or return value of the executed operation. For example, when transforming data via an ActiveX script, developers can control the processing by setting the return code in the script to any one of the values in the DTSTransformStatus enumeration list, which Table 1, page 46, shows. Table 2, page 46, lists other DTS status codes and result constants (their specific values are available in Books Online—BOL). Each of these lists of pre
How to obtain a returnvalue from DTSRun utility Discussion in 'SQL Server DTS-Related Questions' started by Bartuls, Jun 14, 2004. Bartuls New Member Is it possible to obtain a return value from the DTSRun utility? I want to launge a http://www.sql-server-performance.com/forum/threads/how-to-obtain-a-returnvalue-from-dtsrun-utility.18836/ DTSJob from a batchfile. In the same batchfile I want to check the DTS package did execute without failure. Bartuls, Jun 14, 2004 #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. satya, Jun 14, 2004 #2 Bartuls New Member Thank you for replying. Using SP_HELP_JOBHISTORY is possible as dtsrun error 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 dtsrun error level 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. Bartuls, Jun 14, 2004 #2 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
/W NT_event_log_completion_status [
] Raulie, Jun 14, 2004 #2 Bartuls New Member Thanks for the suggestion Lazy DBA (nice nick name 
), but I already record the status in the Windows Event log.
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'!).
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).
The most convenient way to obtain the result is when the