How Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache) - SQL Server Integration Services(SSIS) Tutorial

Scenario:

In this video we will learn how Lookup Transformation deal with Null values those we get from source and try to match with Reference data set in lookup Transformation.
We will experiment with three modes of lookup transformation and see the output.

What we will learn in this video

1. How to read data from flat file source with Null values
2. How to read data in Lookup Transformation as Reference Data Set from a Table
3. How to Map the columns in Lookup Transformation and see the matching output
4. How to change the modes of Lookup Transformation from Full Cache, Partial Cache and No Cache Modes and observe the output.

Source File used in this demo
CustomerName,CustomerRegion,SaleAmt
Aamir,AS,1000
M Raza,AS,500
,EU,400




SQL Script for Lkp_Customer Table

--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'0' AS [CustomerId]
        ,NULL AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'XX' AS [Region]
        
    ) t; 

How Null values are handles in Lookup Transformation in SSIS Package with Default Settings - SSIS Tutorial

  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 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
• Diff between Inner join ( Merge Join Transformation) and Lookup Transformation ( Matched Output)