SSIS Error Handling Examples - MSDN Documentation

Error Handling Examples in SSIS

This section provides practical examples and best practices for implementing robust error handling within your SQL Server Integration Services (SSIS) packages. Effective error handling is crucial for maintaining data integrity, ensuring package reliability, and providing clear diagnostics when issues arise.

Understanding SSIS Error Outputs

SSIS data flow components generate error outputs for rows that fail validation or processing. Understanding how to redirect these rows and handle them separately is the first step in comprehensive error management.

Learn More about Error Outputs

Implementing OnError Event Handlers

Event handlers, such as OnError, can be configured at the package, container, or task level to respond to specific events, including errors. This allows for centralized logging, notification, or package termination.

Explore Event Handler Examples

Configuring Redirect Rows for Specific Errors

Learn how to use the output paths of transformations to redirect rows that cause specific error conditions (e.g., data type conversion errors, constraint violations) to dedicated error tables or files.

See Redirect Rows in Action

Error Outputs in Data Flow Components

Many data flow transformations provide an error output. When a row encounters an error that cannot be processed by the component's standard output, it can be routed to this error output.

Example: Deriving Column and Error Output

Consider a scenario where you are deriving a new column, and some rows might cause conversion errors. You can configure the component to send these erroneous rows to a separate destination.

// SSIS Data Flow Transformation: Derived Column
// Configuration Example:
Output column: "NewCalculatedValue"
Expression: "SomeColumn * 2"
Error configuration: "Redirect rows to error output"

When configuring the connection from the Derived Column transformation, you'll see an "Error Output" path. Connect this path to a separate destination, such as a flat file or a staging table for error logging.

Using OnError Event Handlers

Event handlers provide a powerful mechanism to react to runtime events. The OnError event handler is triggered whenever an error occurs in the associated scope (package, container, or task).

Example: Logging Errors to a Table

You can create an OnError event handler at the package level to log critical error information to a dedicated error log table.

Steps:

In the SSIS Control Flow, switch to the Event Handlers tab.
Select the package scope.
Choose the OnError event from the dropdown.
Add an Execute SQL Task to the event handler.
Configure the Execute SQL Task to insert error details into your log table.

Variables commonly used in OnError event handlers:

System::ErrorDescription: Provides a description of the error.
System::ErrorCode: The numeric error code.
System::SourceDescription: The name of the task or component that raised the error.
System::PackageName: The name of the package.

// SQL Statement for an Execute SQL Task in OnError Event Handler
INSERT INTO ErrorLogTable (PackageName, ErrorDescription, ErrorCode, SourceDescription, EventTime)
VALUES
(
    ?, // Parameter for System::PackageName
    ?, // Parameter for System::ErrorDescription
    ?, // Parameter for System::ErrorCode
    ?, // Parameter for System::SourceDescription
    GETDATE()
);

Redirecting Rows for Specific Error Handling

This pattern is useful when you want to collect rows that fail a specific transformation rule for later review or reprocessing.

Example: Handling Data Type Conversion Failures

Suppose you have a flat file with a column that should be numeric, but some rows contain non-numeric data, causing a data type conversion error.

Use a Flat File Source.
Add a Data Conversion transformation.
Configure the Data Conversion transformation to convert the problematic column to a numeric data type (e.g., `DT_I4`).
Crucially, for this conversion, set the Error Output Column Handling to Redirect rows to error output.
Connect the Error Output of the Data Conversion transformation to a destination, such as an OLE DB destination pointing to an error table.
Configure the OLE DB destination to map the columns, including the error description.

The error table might look like this:

CREATE TABLE StagingErrorData (
    OriginalRowID INT,
    ProblematicColumnValue VARCHAR(255),
    ErrorMessage VARCHAR(500),
    ErrorColumnName VARCHAR(100),
    ErrorColumnLineNumber INT
);