Diff between Lookup Transforamtion and Merge Transformation (Inner Join Type) in SSIS Package - SQL Server Integration Services( SSIS) Tutorial

Scenario:

In this video we will perform a demo or using Merge Join with Inner Join option and Lookup and see how the both produce different results for same input rows.


What you will learn in this video

  1. How to write TSQL Statement for Inner Join
  2. How to use Merge Join Transformation in SSIS Package with Inner Join type
  3. How to use Lookup Transformation in SSIS Package with Matched Output 
  4. Find out how Inner join returns duplicate records for duplicate records



Script used in the Demo of : Difference between Merge Join Transformation ( INNER JOIN Type) and Lookup Transformation


Select * into #Temp
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]
    )t

--Insert into dbo.SourceTable and dbo.LookupTable
    Select * into dbo.lookupTable from #Temp
    Select customerid,CustomerName into dbo.SourceTable from #Temp



--Insert duplicate record
insert into dbo.lookupTable    
    SELECT N'5' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'NA' AS [Region]



What is the difference between Inner Join in Merge Join Transformation and Lookup Transformation in SSIS Package



  Related Posts / Videos on Lookup Transformation