SSIS - How to Redirect Invalid Rows From Flat File Source [Data Validation]

Scenario:

Let’s say we load data from flat file on daily basis. There is SaleAmount Column that should have only integer (numbers). If somehow the wrong data is provided in that column we do not want to fail the package instead of that we want to redirect those rows to error output.

Solution:

Different Transformations in SSIS Provide the error output in case the operation cannot be perform on input data. We can redirect error rows from Different sources, transformations and destinations as well. In out example we are using flat file source.

Step 1:

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

CountryName,SalePersonName,SaleAmount

uSA,aamir shahzad,100

Italy,andy,300

UsA,Mike,500

brazil,Sara,1000

INdia,Neha,200

Brazil,Anna,200

Mexico,Anthony,500

Change the data type to Integer in Flat File Source ( connection Manager) as Shown below

Step 2:

After choosing the correct data types for the inputs, go to Error Output Table on Left and then configure as shown below

Step 3: 

Include two multicast transformation just to test your package with correct data as given above. All the records will go to Success Output as shown below

Step 4: 

Let's add one more row to input file with wrong SaleAmount

Mexico,Anthony,test500 and execute the package.

As we can see that the error row is redirect to error output and rest of rows went to success output. SSIS does not provide a column name due to which the row is redirected.Custom Code can be written to find out more details about column name and description. In this example we can go back to our flat file and check the data manually or we can load this file into some staging table and write  Tsql queries to validate and find out which row does not have valid data and got rejected in loading process.