Duplicate Data in Lookup Transformation and Possible Outcomes in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

In this video we will explore what happen if we have duplicate records in reference data set and we are choosing some values from reference data. Which Record values will be chosen when we will match with Source data and produce matching output.

What we will learn in this video

  1. How to create data from flat file in SSIS Package
  2. How to have duplicate records by Customer Name and different data Points for other columns in SQL Server Table and use that in Lookup Transformation as reference data set.
  3. Observe the output to understand which reference record will be used if we will have multiple records in reference data set.


Source file used for this demo
CustomerName,SaleAmt
Aamir,1000
M Raza,500

Lookup Table used for this demo with duplicate records by CustomerName
--drop table [dbo].[Lkp_Customer]
CREATE TABLE [dbo].[Lkp_Customer](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL,
    [Region] [varchar](2) NOT NULL
) 

insert into  [dbo].[Lkp_Customer]

SELECT *
FROM (
    SELECT N'1' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,'PJ' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'2' AS [CustomerId]
        ,N'M Raza' AS [CustomerName]
        ,N'Test Street Address' AS [StreetAddress]
        ,N'Lahore' AS [City]
        ,N'PJ' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'3' AS [CustomerId]
        ,N'Robert' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'FrankFurt' AS [City]
        ,'NA' AS [State]
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'4' AS [CustomerId]
        ,N'John' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Paris' AS [City]
        ,N'NA' AS [State] 
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'5' AS [CustomerId]
        ,N'Sam' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'NA' AS [Region]
    UNION ALL
       SELECT N'6' AS [CustomerId]
        ,'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'XX' AS [Region]
        
    ) t; 


    Select * From dbo.Lkp_Customer


Duplicate Records in Lookup Data Set in SSIS Package and Possible Output - SSIS Tutorial



  Related Posts / Videos on Lookup Transformation