SSIS Error Handling – Redirect bad rows

Introduction

In this article you will learn how to implement SSIS error handling (detect or redirect bad rows) for SSIS PowerPack components inside Data Flow. If you never heard about SSIS PowerPack then we’ve got to tell you it’s an add-on pack to get additional 45+ SSIS components and Task connectors (e.g. connectivity to REST API, JSON, XML, Azure, AWS, Google, Salesforce etc). In our previous article we saw how to fix common metadata related errors (e.g. truncation error).

SSIS Error Handling: Redirect bad rows to error output

If you want to detect records which are causing errors then you can redirect bad rows to error output using following way.

Redirecting bad rows to error output

To know which records causing failure you can redirect error output by dragging red arrow from component to downstream component (when prompted set Error and Truncation options to [Redirect row]).

SSIS Component Error Handling - Redirect bad rows to error output (Attach downstream destination)

SSIS Component Error Handling – Redirect bad rows to error output (Attach to destination)

View error output records and error message using DataViewer

If you wish to see which records are bad then you can use DataViewer. Right click on the Red arrow which is connected to downstream and click Enable DataViewer. Now execute your package (or Right click in Data Flow surface and Execute just this DataFlow)

SSIS Component Error Output - Add data viewer to see bad records and error message

SSIS Component Error Output – Add data viewer to see bad records and error message

Ignore bad records

If you simply don’t care about such errors and want to ignore them rather redirect then Set Error and Truncation options to [Ignore failure] in above step.

Posted in SSIS Components, SSIS PowerPack and tagged , .