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]).
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)
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.