Script Task Exception Handling in SSIS
November 22, 2010
When working with Script Task of SQL Server 2008 Integration Services, it is important to ensure that your custom code complies with the most elementary programming practices. Learn how to handle unexpected errors that interfere with the successful execution of your code.
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
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