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 

• How To Use Dynamic Query in Lookup Transformation in SSIS Package
• Use Stored Procedure with Parameters in Lookup Transformation ( Full Cache Mode) in SSIS Package 
• How to Force Lookup Transformation to Work as Case In-Sensitive in SSIS Package
• Does Lookup Transformation perform Left Outer Join or Not in SSIS Package
• How Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache)
• How to Handle "Row yielded no match during lookup". Error in SSIS Package
• How Lookup Transformation works for Duplicate Records in Reference Data Set
• How to Load Slowly Changing Dimension Type 1 by using Lookup Transformation ( UpSert Operation)
• Detailed Demo on Lookup Transformation Modes ( Full Cache, Partial, No Cache) by using SQL Server Profiler