I have a requirement where the source is .txt which contains the duplicate record. I need to redirect the duplicate record ..and also any sceond duplicate row should be sent to REJ table and first should be send to PRJ table. I cant use Sort as it redirects all the duplicate rows and i cant insert the first row and also a staging table is not allowed to be created by the DBA !Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/

You can try to write Query in Ole DB Destination to get the First row and duplicate row WITH CTE (Col1,Col2, Dup_Count) AS ( SELECT Col1,Col2, ROW_NUMBER() OVER(PARTITION BY Col1,Col2 ORDER BY Col1) AS Dup_Count FROM Table ) select * FROM CTE WHERE Dup_Count =1 WITH CTE (Col1,Col2, Dup_Count) AS ( SELECT Col1,Col2, ROW_NUMBER() OVER(PARTITION BY Col1,Col2 ORDER BY Col1) AS Dup_Count FROM Table ) Select * FROM CTE WHERE Dup_Count =2 Shailesh , Please mark the post as answered if it answers your question.

Can I just clarify. Where there are duplicates you want to accept the first row but reject all subsequent rows, correct? This is an interesting little problem. You basically need to order the rows by the duplicated value then, using a script component, give each row an ID which resets itself when a new value is encountered. You can then use a Conditional Split to send all rows where ID=1 to a "keep" output and all the rest to a "reject" output. Hope that makes sense! @jamiethttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hi Jamie, Can you Share any Sample Script ?Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/

Hi Jamie, Can you Share any Sample Script ? Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/ No, I don't have one available, sorry.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Have a look here: Extracting Duplicate Primary Row Keys in SSIS. Talk to me now on

Here is an other solution with a Script Component. This example deduplicates on the first three columns: Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com