How can i catch the Duplicate Rows From Sorter Trans in a Separate Target Table ?

Question

I have a workflow which loads data from a Flat file to a Stage Table after a few basic checks on a few columns. In the mapping, each time my check fails (meaning if the column has an invalid value) , I make an entry to a ErrorFlatFile with an error text.

Now , I have two targets in my mapping. One being the Stage table and the other is the Error Flat File.

What i want to achieve is this ? Even if there is one entry in the ErrorFlatFile (indicating there is an error in the source file ) , I want to truncate the Target Stage Table.

Can someone Please help me with how i can do this at the session level.

Answer 1

When you use 'Select Distinct' option , the Sorter Transformation discards the duplicate records.

If you want to trace out the duplicate records, then send the sorted data into Expression Transformation. Add a new
variable and compare the variable with the input data. If the data matches then flag the record or else pass the default row.And load all the flagged records into a separate table.