Professional SQL Server™ 2005 Integration Services

7.5. Structured Exception Handling

Structured Exception Handling (SEH) allows you to catch specific errors as they occur and perform any appropriate action needed. In many cases, you just want to log the error and stop execution, but there are some instances where you may want to try a different plan of action, depending on the error.

Here is an example of exception handling in Visual Basic.NET.

Public Sub Main()
    Try
      Dim fileText As String
      FileIO.FileSystem.ReadAllText("C:\data.csv")
    Catch ex As System.IO.FileNotFoundException
      'Log Error Here
      Dts.TaskResult = Dts.Results.Failure
      Return
    End Try
    Dts.TaskResult = Dts.Results.Success
End Sub

This trivial example attempts to read the contents of the file at C:\data.csv into a string variable. That is why this code was placed in a Try block. It is trying to perform an action that has the potential for failure. If the file isn't there, a System.IO.FileNotFoundException is thrown. The Catch block is the error handler for this specific exception. You would probably want to add some code to log the error inside the Catch block. The result is set to Failure and the script is exited with the Return statement. If the file is found, no exception is thrown, and the next line of code is executed. In this case, it would go to the line that sets the TaskResult to Success, right after the End Try statement.

If an exception is not caught, perhaps because an appropriate Catch statement is not in place, the exception propagates up the call stack until ...