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 

3 comments:

  1. The blog posts by Tech Brothers are always worth reading and informative.I love the way they explain about any particular topic through a practical example irrespective of it being SSIS or any other topic in SQL.

    ReplyDelete
  2. You were great and everyone received so much from your experience and knowledge
    Absolutely amazing, thank you for sharing your knowledge with me.
    메이저사이트
    경마

    ReplyDelete
  3. I don’t even know how I ended up here, but I thought this post was great.
    I do not know who you are but certainly you’re going to a famous blogger if you are not already
    바카라사이트
    토토사이트

    ReplyDelete