Custom Error Handling in SSIS using Event Handlers

SQL Server Integration Services (SSIS) contains some really useful logging procedures but as with most things in SSIS, it is extensible. There are 2 methods of extending the logging capability of SSIS:

• Build a custom log provider
• Use event handlers

Error logging is crucial to troubleshooting and auditing efforts. If an error occurs in your SSIS package while it is executing in production loads, transformations, or transfers. you will want to know as much as possible about it.


As I said we are going to use event handlers to demonstrate the custom logging ability of SSIS. The event handlers provided with SSIS (N.B. The event handlers themselves are also extensible) are:

• OnError
• OnExecStatusChanged
• OnInformation
• OnPostExecute
• OnPostValidate
• OnPreExecute
• OnPreValidate
• OnProgress
• OnQueryCancel
• OnTaskFailed
• OnVariableValueChanged
• OnWarning

For the purposes of logging the most important ones are OnPostExecute, OnError & OnWarning.

Example:

Follow the below steps to implement the custom error handling in SSIS using event handlers.

Create a new SSIS project named ErrorTest

 Drag an Execute SQL Task onto the Control Flow

 Configure it (double-click or right-click and select Edit to open the editor) as shown below. Note any connection will suffice, so long as the SQL Server is running. Also note the SQL will generate an exception (intentionally).

 Click OK to close the editor. Right-click the Task and select Execute Task to test.The task should fail.

 Stop execution and click on the Event Handlers tab.

Check to be sure the Executable dropdown is set to the ErrorTestPkg (the package) and the Event handler dropdown is set to OnError. Click the link to create a new OnError Event Handler for the package. From the Toolbox, drag and drop a Data Flow Task onto the Event Handler 

 Double-click the Data Flow Task to edit it. From the toolbox, drag a Script Component transformation onto the Data Flow.

 The Script Component can be configured as a Source, Transformation, or Destination. Select Source

 Since a Source has only outputs (a destination has only inputs, and a transformation has both inputs and outputs), you will need to configure the Output. Rename the generic Output 0 to ErrorOutput.

 Click on Output Columns and click Add Column.Rename the Column ErrDescription

 Edit the ErrDesription column's properties - change the datatype to String (DT_STR) and the length to 5000

Add the ErrorDescription System variable to the ReadOnlyVariables property of the Script Component

 There are other variables you will add to your error handling script component. Remember variable names are case-sensitive, comma-separated, with no spaces in the list.

Click the Design Script button and add the following code snippet in the CreateNewOutputRows subroutine as shown below:

        With ErrorOutputBuffer 

            .AddRow() 

            .ErrDescription = Me.Variables.ErrorDescription 

        End With 

Some explanation: The name of the ErrorOutputBuffer was determined by the name you gave the Script Component Output earlier. When you changed the Output name to ErrorOutput, the script component's output buffer was changed to ErrorOutputBuffer.

To populate the buffer, you must add rows to it. Think of this as starting a new row in a table. If you're familiar with tables in Microsoft Word documents, you know when you start a new row it is empty until you populate it. The same is true here. You are creating a new, empty row in the output buffer to populate with data.

13. The final step is to load the row. Again, in your production-ready error event handler you will populate more columns in the output buffer than this one - this is for demonstration purposes only

14. Drag a flat file destination onto the Data Flow and connect it to the Script Component as shown.

15. Configure a flat file destination. When you configure a flat file destination, you are prompted for which type of file. Delimited files are fairly simple to read - especially with few fields

16. Select a location and name for your flat file. I chose C:\ErrorOutput1.txt

17. Click Columns to view the columns the file will contain. Note: the columns would be empty if you had not connected a data path from the Script Component prior to configuring the Flat File Destination

Click OK to proceed. Click the Mappings page to assign mappings between fields in the pipeline and fields in the destination

Since you built the destination from the metadata contained in the pipeline, auto-mapping should occur when you click Mappings

Click OK. Your Event Handler should appear as shown

Start debugging the package by pressing F5 or clicking the Debug Run arow. Your ExecuteSQL task should fail as before

Click on the Event Handlers tab to determine the run status of your Package OnError Event Handler. Double-click the Data Flow Task and note one row was written to your error file (ErrorOutput1.txt)

Open ErrorOutput1.txt file to view the error stored there

In this example, we built a package to demonstrate custom error handling in SSIS package and store that error to a Flat File.