Handling Errors in SSIS: Data Redirection Error Description

The T-SQL script to create and populate the SSISErrors table and the demonstration package can be downloaded here.

I recently posted on how to handle data flow errors using error redirection.  While effective this technique lacks certain aspects of traditional error handling, first and foremost the ability to see and/or record the error description.  As discussed in the afore mentioned post I have outlined how to successfully handle and redirect failed rows that occur in a data flow task, this post builds on error redirection to include recording and retrieving error information.

For brevities sake I am going to begin where I left off from my last post, with a package using error redirection with one minor change.  The redirection package used a flat file destination for the redirected rows which will be replaced with an OLEDB destination to ease logging and querying.  The OLEDB destination redirects errors to a table called ErrorRedirection that is similar to the target table, DataRedirect, except the col1 data type is INT and it has two additional columns, ErrorCode and ErrorColumn.

image

image

When redirecting failed rows you notice that the data path includes the additional columns, ErrorCode and ErrorColumn, which provide information on the reason for redirection.  Consider this information a framework of sorts in that, unless you have memorized the error codes and associated descriptions you are still left guessing the reason.

Microsoft provides an html table that outlines all of the error codes and descriptions that can be found online here.  In order to implement this solution it is easiest to replicate that table in SQL which will allow us query the error information from redirected tables and join the SSIS Errors code column.  The script to create and populate the SSISErrors table is included in download at the end of this post.

The data flow tab connects to two script tasks using OnSuccess precedence contstraits:

image

Each precedence constraint evaluates the RowCount variable and if an error occurs and a row is redirected then the execution path of the package will go to the Error Occurred script task.  the code below shows the VB.NET code that is uses a data reader to query the redirected data from the ErrorRedirection table and join the ErrorCode column to the SSISErrors table.



Public Sub Main()

Dim cmd As SqlCommand
Dim cmdtext As String
cmdtext = “SELECT DecimalCode, Description FROM [ErrorRedirection] r JOIN SSISErrors e ” & _
” ON r.ErrorCode = e.DecimalCode”

Dim cn As New SqlConnection(“Server=localhost; Database=Adventureworks2012;Integrated Security=SSPI”)
cmd = New SqlCommand(cmdtext.ToString, cn)

cn.Open()
Dim dr As SqlDataReader
dr
= cmd.ExecuteReader
While dr.Read

Dts.Events.FireError(CInt(dr.Item(0).ToString()), “Invalid Data Type Task Error Raised”, dr.Item(1).ToString(), _
“”, 0)
MessageBox.Show(“Error Code ” & dr.Item(0).ToString() + dr.Item(1).ToString())
End While
If
cn.State <> ConnectionState.Closed Then
cn.Close()
cn.Dispose()
cmd.Dispose()
End If
Dts.TaskResult = ScriptResults.Success
End Sub

The code is somewhat self explanatory.  The SQL command is passed to the data reader to query the error from the redirected table and the SSISErrors table.  The reader iterates through each row and the returned error description using a While loop.  Each time a record is returned the Dts.Events.FireError method is called passing in the error description.  This will cause the script task to fail while passing the custom error description.  Finally a message box is displayed to show the error description, which is just for demonstration.

Obviously there may be just reasons why you would prefer that the package and task not fail, which simply means removing or replacing the Dts.Events.FireError method, but this does demonstrate that with a little extra work you are able to maintain the error information of redirected rows.

I think that it is VERY important to mention that when utilizing error redirection you need to insure that the destination is fault tolerant.  In one of my first attempts at error redirection in the data flow I create a table that replicated the original destination in every part except constraints and keys.  When an error occurred for things like truncation or data type mismatches at the destination it also failed at the redirected table.  Keep this in mind when creating your destination to insure that these types of errors don’t occur.

The T-SQL script to create and populate the SSISErrors table and the demonstration package can be downloaded here.

SSISEndtoEnd

Leave a Reply


Hit Counter provided by Sign Holders