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 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
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.
Ignore API Errors : JSON, XML, CSV Source
If you are using JSON Source, XML Source or CSV Source to call API then you might get Errors for various reason. For example if your URL accepts some parameters and you pass wrong value you might get Page not found (404) error. In many cases you want to fail this but sometimes you want to continue if specific error is returned. For such case Error Redirect (Explained above) wont work because Error Redirect only happens once data is returned (Successful API call) and Parsing Phase is started, however error like 404 (Page not found) fails component before data can be parsed. To ignore specific Web Errors you can use below options.
Continue on API Error Settings – By Status Code, Message or Any Error
Redirect WebExceptions / Server Error
There will be a time when you get Server Errors due to bad parameters or other issues. Here is how to redirect error in that condition.
Change the below option (Only available in the newer version)
Redirect API Webexception
API Error Retry Settings
When calling API you might get transient error which you need to retry after some time (i.e. when it fails due to API Limit Error). In such case you can use retry settings found on HTTP / OAUTH Connection.
Here is an example of OAuth Connection [Error Retry Settings]
Retry Options
