How to redirect bad data Rows from Flat File Source to avoid Package failure?

Suppose we want to load data from flat file on frequently basis. There is Emp_Id Column that should have only integer or numeric data. If somehow the wrong data is found in that column we do not want to fail the package instead of that we want to redirect those rows to error output.

We can redirect error rows from Different sources, transformations and destinations as well. In our example we are using flat file source.

First Create connection to text files by using Flat File Connection Manager. I have used below data for initial testing

Emp_Id,Name

1,vipin

2,biru

ABC,XYZ – bad data which should not insert in to destination table

Change the data type to Numeric in Flat File Source.

Then change the Properties of Error and Truncation to Redirect row of selected column as shown below

Now run the package , As we can see that the error row is redirect to error output and rest of rows went to success output.