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