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.

3 comments:

  1. I think there is a need to look for some more information about SSIS and its other useful aspects.

    SSIS PostgreSql Write

    ReplyDelete
  2. India has got a long list of Private and Government Banks which do support the financial needs of everyone in the country. India is the second largest country in terms of population and to meet everyone’s needs. There are numerous Banks established in India. In general the India Bank Holidays are normal on public Holidays, which applied through Nationwide. Though each bank in India has different regulations, Bank of India balance enquiry number all of them follow the same pattern of bank holidays in India for the given year.So if you are wondering what the list of bank holiday this month, then you should read this article. Where we talk about the holidays for the Banks in India. Do prefer different holidays as per their state culture and defined holidays.

    ReplyDelete