Does Lookup Transformation perform Left Outer Join or Not in SSIS Package - SQL Server Integration Services( SSIS) Tutorial

Scenario:

We often hear from developers that we can perform Left Outer Join by using Lookup Transformation instead of using Merge Join.
In this video we will learn if that is possible or not?

Items we will learn in this video
How to Create Two tables with some Sample Data to perform Left Join in TSQL
How to use two tables to perform Left Join by using Merge Join Transformation
How to use Lookup Ignore Failure feature to ignore errors if source record does not match with lookup reference data.
Analyize the output of TSQL Code output, Merge Join output and Lookup Output to see if all three produce same results?

Sample Tables with Sample Data

--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]
    ) t;



--Create New Sample Tables

Select * into dbo.Customer1 from dbo.lkp_customer
Select top 4 * into dbo.Customer2 from dbo.lkp_customer

Select * from dbo.Customer1
Select * from dbo.Customer2

--Write Left Join TSQL Query
Select L.CustomerId,L.CustomerName,L.Region AS LeftRegion,R.Region AS RightRegion
from dbo.Customer1 AS L
Left outer join 
dbo.Customer2 AS R
on L.Customerid=R.Customerid


Can Lookup Transformation perform Left Outer Join operation in SSIS Package - SSIS Tutorial



  Related Posts / Videos on Lookup Transformation