Edit

Lesson 4: Add error flow redirection with SSIS

  • Article

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

To handle errors that may occur in the transformation process, Microsoft Integration Services lets you decide on a per-component and per-column basis how to handle data that Integration Services can't transform. You can choose to ignore a failure in certain columns, redirect the entire failed row, or fail the component. By default, components in Integration Services are configured to fail when errors occur. The failed component in turn causes the package to fail and processing then stops.

Rather than letting failures stop package execution, you may configure and handle potential processing errors as they occur. One option is to ignore failures altogether so your package always runs successfully. You can also redirect the failed row to another processing path, where the data and the error can be persisted, examined, or reprocessed.

In this lesson, you create a copy of the package that you developed in Lesson 3: Add logging with SSIS. Working with this new package, you create a corrupted version of one of the sample data files. The corrupted file causes a processing error to occur when you run the package.

To handle the error data, you add and configure a Flat File destination that writes any failed rows to an error file.

Before Integration Services writes error data to the file, you include a Script component that gets error descriptions. You then reconfigure the Lookup Currency Key transformation to redirect any data that couldn't be processed to the Script transformation.

Prerequisites

Note

If you haven't already, see the Lesson 1 prerequisites.

Lesson task

This lesson contains the following tasks:

Start the lesson

Step 1: Copy the Lesson 3 package