SSIS- How to Redirect Duplicate Records In SSIS Or Remove Duplicate Records In Data Flow Task


We need to load data from Flat file source but we want to redirect all duplicate records to Audit table or File  with count and load only records which are unique to our destination table.

Solution :

We will be using Aggregate Transformation with Conditional Split Transformation to perform this requirement.

Step 1:
Drag Data Flow Task to Control Flow Pane and then Drag Flat File Source inside Data Flow Pane. Create Connection to source file as shown below

Click on Columns and see if data is displaying correctly and then click Ok.

Step 2:
Bring Aggregate Transformation to Data Flow Pane and connect Flat File Source to Aggregate Transformation and configure as shown. We need to group them and get count.

Step 3: 
Drag Conditional Split Transformation to Data Flow Pane and connect Aggregate Transformation to it. By using Conditional Split we will be redirecting duplicate records to output where we can write them to table/file. I have converted [Count All] to Int by using Cast Function (DT_I4). 
Conditional Split Transformation is going to create two outputs, DuplicateRecords for duplicate records and CorrectRecords for unique records.

Step 4:
Bring two Multicast Transformations and connect each of output from Conditional Split Transformation to them. Put Data Viewer between them so we can display the records. In real time you will be writing these records to your destination tables/files.

Final Output:
By using the Conditional Split, the duplicate records are redirected to Multicast 1 transformation and unique records are directed to Multicast transformation.

Video Tutorial for the post


  1. คาสิโนออนไลน์ที่น่าเชื่อถือและมีความเป็นมืออาชีพที่สุดในตอนนี้
    โปรโมชั่นGclub ของทางทีมงานตอนนี้แจกฟรีโบนัส 50%
    เพียงแค่คุณสมัคร สล็อตออนไลน์ กับทางทีมงานของเราเพียงเท่านั้น
    สมัครสล็อตออนไลน์ >>> Goldenslot
    สนใจร่วมสนุกกับ คาสิโนออนไลน์ คลิ๊กได้เลย
    มีทั้งคาสิโนออนไลน์ หวยออนไลน์ ฟุตบอลออนไลน์ สล็อตออนไลน์ และอื่นๆอีกมากมาย

  2. I think this is actually a very informative blog post about SSIS and SQL.There is always a need to find out more and more aspects.

    SSIS Postgresql Write

  3. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...