Error Handling in SSIS

Sometime back I had to work on a small SSIS package that required error handling and notification. The package that I worked is working fine so far so thought I will write it down on this blog.


To give you a background on the package requirement. The requirement was to extract data from multiple excel files (with dimensional data) ,do some clean up and load (Type-2 dimensional handling) of data and load into the database. If there is any error during the extraction and or load then the error needs to be logged into a database table and an email notification had to be sent to the operations team.


This article shows only how the error handling was done in the package was achieved. Error logging and sending email is pretty simple. So these will not be covered here.

The following is a high-level structure of the package.

As you can see there is a Sequence container called “Load Excel files” that does the job of extract, transform and load(ETL) into the destination tables/database. It has multiple tasks(not shown in the above figure) within it that is executed in serial and/or parallel


Any error that occurs within any task in the sequence container is sent to a “Send Email on Error” Task and also logged into “Log Error in DB table” Task.

What follows are the step outline and the detailed steps required to configure error handling.

Step 1: Declare two package variables to capture error source and description.

For this package, @ErrorSource is to capture the source of the error. @ErrorDesc is to capture error description. Both the variables are string data types.

Step 2: Enable error event handling for the SSIS package.


Detailed Steps:

1. To handling Error events ,navigate to the Event handler tab in the Package design Window.
2. From Executable drop down select the name package for which error handling needs to be enabled. I’ve selected “MDS Import Dimensions” because it is my package.

3. From the event Handler drop down select OnError.

4. Click on the message “Click here to create an ‘OnError’ event handler for “Package Name”.

Step 3 : Capture the error.

1. Drag and drop a script task and rename it is something meaningful. I’ve named it to “Build Error String” as shown below.

2. Double click on the script task to open up the script task editor.
3. Select the Script language as Visual Basic
4. Select the ReadOnlyVariables as System::ErrorDescription and System::ErrorSource.
Note :- System::ErrorDescription is available only for Error event handlers.

5. Select the two package variables from Step 1 as ReadWriteVariables. The ReadWriteVariables are like output variables in the script task.

The following screenshot illustrates step 4 & 5

6. Click on the edit script button. This will open up the visual studio script editor. Type the following in the Main Method.

Public Sub Main()
Dts.Variables("ErrorDesc").Value = Dts.Variables("ErrorDesc").Value & Dts.Variables("ErrorDescription").Value.ToString()
Dts.Variables("ErrorSource").Value = Dts.Variables("SourceName").Value.ToString()
'MsgBox(Dts.Variables("ErrorDesc").Value.ToString())
Dts.TaskResult = ScriptResults.Success
End Sub

The code above the ErrorDescription generated by SSIS to the ErrorDesc package user defined variable (remember the package variable that was declared in Step 1 above). Likewise, it assigns the SourceName generated by SSIS is assinged to the user defined package variable ErrorSource.


So far so good, the package level error and its source are stored in the user variables ErrorDesc and ErrorSource respectively. When I ran the package in its current form I got same error message multiple times via mail and they appeared as got logged in the DB. This was annoying.


This is because SSIS bubbles up errors. So if I have a task in a container and the task throws an error I got the error message three times in my case once for the task, once for the container in which the task was hosted and once for the package.


The way to avoid this problem is to Concatenate the SSIS errors in another script task in the event handler called OnPostExecute as shown below.

Step 4 Concatenate the errors.

Detailed Steps

1. Declare a Package variable for holding the concatenated error string. For this example it is User::ConcatenatedError.

2. From Executable drop down select the name package for which error handling needs to be enabled. I’ve selected “MDS Import Dimensions” because it is the name of my package.
3. From the Event Handler drop down select OnPostExecute.
4. Click on the message “Click here to create an ‘OnError’ event handler for “Package Name”.
5. Drag and drop the script task rename it as required. I’ve renamed it to Assemble error.

Step 5 : Define the error scenario

6. Double click on the script task to open up the script task editor.

7. Set the ReadOnlyVariables and ReadWriteVariables in the Script Task Editor to as shown in the screen shot. Both these variables were declared in the steps above.

8. Click on the edit script button. This will open up the visual studio script editor for SSIS.
9. Type the following in the Main method.

Public Sub Main()
Dim errorDesc As String
Dim messages As Collections.ArrayList

Try

messages = CType(Dts.Variables("ErrorDesc").Value,Collections.ArrayList)

Catch ex As Exception

Return

End Try

For Each errorDesc In messages

Dts.Variables("ConcatenatedError").Value = Dts.Variables("ConcatenatedError").Value.ToString + errorDesc + vbCrLf

Next

Dts.TaskResult = ScriptResults.Success

We’re all set now, to use the User::ConcatenatedError variable and use it for error logging/notification.

Here are the steps to define the error path and its precedence.

Step 6: Convert the regular output path(Green connectors) to an Error path (Red connectors)


Detailed Steps:

1. Right click on the green arrow and select Failure from the drop down menu as depicted in screen below.

2. Double click on the arrow to open up the Precedence Constraint Editor as shown in screen below to set expression to the error flow path. Expressions allow to you have more flexibility to the error scenario.

2. Select “Expression and Constraint” from the Evaluation Operation drop down.
3. Leave the Value drop down to Failure.
4. In the text box next to Expression type @ErrorDesc != “”


What steps 2-4 means is that translates to is go to the error step when the Constraint = “Failure” and Expression @ErrorDesc != “” (meaning there is some error).


Now you can connect the error output to an email task or SQL execute task to email or log error to a database table.


Hope this is useful to you. Welcome feedback and/or suggestions on this error handling article.