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