Trapping DTS errors with Transact-SQL
Joseph Sack, www.JoeSack.com

I've often been asked what the "best" method is for trapping DTS errors. This is a very broad question. What errors you care about capturing can differ from project to project, and package to package.

Do you want to capture runtime errors (such as a "divide by zero" or OLE DB connection error timeouts) or logical errors (a table was empty and shouldn't be, or a flag was set to "N" but needs to be "Y")?

Error trapping in most cases, should fit the following requirements:

• Should differentiate between runtime errors (functionality itself) and logical errors (business requirements).
• Of these errors, only those errors you CARE about should be tracked.
• The error(s) should be in a READABLE format (error numbers are only okay if accompanied by an error string).

The commonly used methods for capturing DTS package errors include:

• Error logging via the Logging tab of the DTS Package Properties. This logging captures all DTS activities at a physical level, reporting runtime errors, # of rows updated, and tasks started and completed.
• Making use of the "On Failure" workflow within your package. This technique is best for capturing "Logical" errors. For example, you could use ActiveX to force an "On Failure" event if a table's data didn't meet a certain business condition.
• Viewing the 255 character chunk of history for each step in the SQL Server Agent Job History. This is really the same data you will see in a log file, only truncated for each job step (255 characters per DTS package).
• Using an Output file from the Advanced tab of a SQL Server Agent Job step. This is also the same data as the log file, capturing physical events and steps during the package runtime.

In this article, I will address how to capture runtime errors (not logical errors) by using Transact-SQL. Transact-SQL allows us to get more control about what data we capture, present, and keep.

In order to follow along, I recommend you create a package with the following settings:

1. In the DTS Designer, add a connection object to a SQL Server Instance you have access to.
2. Add an Execute SQL task. Within the tasks, add the following Transact-SQL code. "SELECT 1/0"
3. Save the package.

When executed, this package is should fail due to a "Divide by Zero" error. This error is captured into a temporary table with the following Transact-SQL code:

CREATE TABLE #DTSErrorOutput
        (ErrorString varchar(500) NULL)

INSERT #DTSErrorOutput
EXEC xp_cmdshell 'DTSRun /S"ServerName" /E /N "PackageName"'

SELECT DISTINCT 'Package Name',
       GETDATE() as 'Report Date',
       LTRIM(ErrorString)
FROM #DTSErrorOutput
WHERE ErrorString LIKE '%Error string%'

DROP TABLE #DTSErrorOutput

This code chunk does a few things. It first creates a temporary table to hold the output of our xp_cmdshell call. We then populate this table using both xp_cmdshell and DTSRUN, where ServerName is the name of your server and PackageName is the name of your package.

Next we select from the temporary table to show all results that have the chunk "Error string". If a runtime error occurs, you should see such a line in the output. If no errors occur, then the output table will only have background runtime information. Lastly, we drop the temporary table.

The results of your query should return the name of the package, the date you reported the error, and the actual error string.

This chunk of Transact-SQL can be adapted to your requirements, or built in to other stored procedure calls or history tables. This technique can be used to track history of package errors too. Simply add logic to output your results to a history table. You could even link a web page front end to the table results, so that project team members or clients can stay up on nightly load or other batch based DTS package outcomes.