TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
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.
We will be using Aggregate Transformation with Conditional Split Transformation to perform this requirement.
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.
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.
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.
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.
By using the Conditional Split, the duplicate records are redirected to Multicast 1 transformation and unique records are directed to Multicast transformation.