Script Task Exception Handling in SSIS

While SQL Server 2008 Integration Services greatly simplify the creation of packages by including a number of pre-defined tasks and components that can be employed in order to satisfy a variety of your custom data extraction, transformation, and loading needs, in some cases it is necessary to venture into areas extending beyond those traditionally associated with database management. One of the primary examples of such a scenario is configuration of Script Task, which requires at least limited development skills. Even if you find that accomplishing your objectives by leveraging the .NET Framework and SSIS object model is relatively straightforward, it is also important to ensure that the resulting code complies with the most elementary programming practices. In this article, we will explore one of them, known in the technical parlance as structured exception handling, whose purpose is to deal with unexpected errors that interfere with successful execution of your code.

In our recent article demonstrating how the Script Task can be used to query Active Directory, we decided to temporarily forgo any error checking, assuming that all prerequisites have been fulfilled. As we pointed out, this was done strictly for the sake of simplicity and should be avoided in production deployments. While the code we created did yield the desired outcome in absence of any error conditions, it was bound to fail if any of our assumptions has not been satisfied. In addition, troubleshooting such failures would be difficult without any clues suggesting an underlying cause. Let's examine how these shortcomings can be addressed.

In general, the recommended approach to dealing with runtime errors in the .NET Framework envrionment is to take advantage of its built-in structured exception handling mechanism. Its representation in Visual Basic .NET 2008 takes the form of the three-part Try/Catch/Finally construct (which, by the way, has its Transact-SQL equivalent). The Try block contains code that carries out a serviceable task. Any error that is encountered during its execution throws an exception, transferring control to the Catch section, which implements error handling logic. Its name is consequential, meant to convey the fact that errors are captured and not returned to the caller (corresponding, in our case, to the Script Task in which the Visual Studio for Team Applications (VSTA) project is hosted), effectively controlling their negative impact. The code following the optional Finally clause is invoked after either Try or Catch statements complete and typically is intended for closing cleanup activities.

From the programming standpoint, errors encountered during execution of the Try block are instantiated as objects of Exception class. Its properties, documented on the msdn Web site, allow you to retrieve information that simplifies identifying the root cause of a failure, such as name of the offending method (TargetSite property), a descriptive message associated with an error (Message property), or, if you are planning on more in-depth troubleshooting, frames present on the call stack at the time when the exception was thrown (StackTrace property). Their values can be displayed interactively via message boxes (during an ad hoc debugging session) or captured via the standard SSIS logging mechanism with the FireError method of the Dts.Events property (for details, refer to the Raising Events in the Script Task msdn article) called explicitly within the Catch block. (Keep in mind that such call counts toward the threshold imposed by MaximumErrorCount property of the parent container and, depending on its value, might automatically result in the task failure).

It is worth pointing out that there are other approaches to dealing with runtime issues. The first one, known as unstructured exception handling relies on the On Error GoTo statement, placed typically at the beginning of a code block. Errors that are encountered during its execution are handled according to the specifics of the GoTo clause. In particular, On Error GoTo Line targets a specific line number or label, On Error Resume Next transfers control to a statement immediately following the one that failed, On Error GoTo 0 disables error handling for the current procedure, and On Error GoTo -1 sets the current exception to Nothing. The other approach utilizes the If/Then/Else statement, which is designed to address predictable issues that are expected to surface during runtime. This is accomplished by evaluating whether conditions that lead to those issues are true and redirecting flow of execution such that their negative effect is either eliminated or at least mitigated. Note that the principle employed by the Try/Catch/Finally construct is different, since it handles exceptions (including those that you did not anticipate) once they occur, interrupting current code sequence following the Try statement and switching automatically to the Catch block. While its processing overhead is higher than If/Then/Else, it offers more straightforward implementation (lacking, for the most part, dependencies on the logic implemented in the Try code) and superior level of protection.

To conclude our presentation, let's take a look at our sample code that illustrates use of the Script Task for the purpose of querying Active Directory (which first draft was included in our previous article), but this time, with structured exception handling in place (as well as a single If/Then statement):

Public Sub Main()
        
' Add your code here
 
Dim dsSearcher As System.DirectoryServices.DirectorySearcher 
Dim dsResults As System.DirectoryServices.SearchResult
Dim userName As String
 
Try
        userName = Dts.Variables("UserName").Value.ToString()
        dsSearcher = New _
               System.DirectoryServices.DirectorySearcher("LDAP://DC=widgets,DC=com")
 
        dsSearcher.Filter = _
               "(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & userName & "))"
        dsSearcher.SearchScope = SearchScope.Subtree
        dsSearcher.PageSize = 1000
 
        dsResults = dsSearcher.FindOne
 
        If dsResults.Properties("employeeID").Count > 0 Then
               Dts.Variables("EmployeeID").Value = _
                  dsResults.Properties("employeeID").Item(0).ToString
               MessageBox.Show(Dts.Variables("EmployeeID").Value, "EmployeeID", MessageBoxButtons.OK)
        End If
 
        Dts.TaskResult = ScriptResults.Success
 
Catch ex As Exception
 
        MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK)
        Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message.ToString(), "", 0)
        Dts.TaskResult = ScriptResults.Failure
 
End Try
 
End Sub

» See All Articles by Columnist Marcin Policht