Lesson 4: Add Error Flow Redirection with SSIS
Applies To: SQL Server 2016
To handle errors that may occur in the transformation process, Microsoft Integration Services gives you the ability to decide on a per component and per column basis how to handle data that cannot be transformed. You can choose to ignore a failure in certain columns, redirect the entire failed row, or just fail the component. By default, all components in Integration Services are configured to fail when errors occur. Failing a component, in turn, causes the package to fail and all subsequent processing to stop.
Instead of letting failures stop package execution, it is good practice to configure and handle potential processing errors as they occur within the transformation. While you might choose to ignore failures to ensure your package runs successfully, it is often better to redirect the failed row to another processing path where the data and the error can be persisted, examined and reprocessed at a later time.
In this lesson, you will create a copy of the package that you developed in Lesson 3: Add Logging with SSIS. Working with this new package, you will create a corrupted version of one of the sample data files. The corrupted file will force a processing error to occur when you run the package.
To handle the error data, you will add and configure a Flat File destination that will write any rows that fail to locate a lookup value in the Lookup Currency Key transformation to a file.
Before the error data is written to the file, you will include a Script component that uses script to get error descriptions. You will then reconfigure the Lookup Currency Key transformation to redirect any data that could not be processed to the Script transformation.
This lesson contains the following tasks: