How to Force Lookup Transformation to Work as Case In-Sensitive in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We are getting the source records from a flat file and we need to lookup against SQL Server Table. We know the by default Lookup behave as case sensitive when we match the records. Can we make the Lookup to work as case insensitive when match the source records to destination. Will it be good practice, What other options we have to perform case insensitive match?

In this video we will learn

  1. How to read data from Flat File Source
  2. How to perform lookup with Full Cache and see limitations
  3. How to perform Lookup with No Cache and Partial Cache and see if it works as case insensitive
  4. Find as solution to perform Case insensitive match with Full Cache mode

Script used in this video demo for Lookup Transformation in SSIS Package

--drop table [dbo].[Customer_Dst]
CREATE TABLE [dbo].[Customer_Dst](
    [CustomerId] [int] NULL,
    [SalesAmt] int
   ) 


--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;


    Select Customerid,CustomerName,Region from dbo.Lkp_Customer
    
    select * from dbo.Customer_Dst


Source Sample File used in the demo
CustomerName,CustomerRegion,SaleAmt
Aamir,AS,1000
Raza,AS,500



Understand Case Sensitivity of Lookup Transformation in SSIS Package - SSIS Tutorial




  Related Posts / Videos on Lookup Transformation