Understand Lookup Transformation Modes ( Full Cache, Partial Cache and No Cache) Demo - SQL Server Integration Services(SSIS) Tools

In this video we will do perform some experiments to understand following

What is Full Cache Mode in Lookup Transformation and How it works in SSIS Package
What is Partial Cache Mode in Lookup Transformation and How it works in SSIS Package
What is No Cache Mode in Lookup Transformation and How it works in SSIS Package


we will use SQL Server Profiler to capture live SQL Statements to while changing the mode from Full Cache to Partial or from Partial Cache to No Cache.


Sample Data used in the Test File

CustomerId,CustomerName,StreetAddress,City,State,RegionCode
1,Aamir,1234 Street,Sahiwal,NY,NA
1,Aamir,Test Address,Charlotte,NC,NA
1,Aamir,1234 Street,Sahiwal,NY,NA
2,M Raza,Test Address,Charlotte,NC,NA
2,M Raza,Test Address,Charlotte,NC,NA
2,M Raza,Test Address,Charlotte,NC,NA
3,Aamir,1234 Street,Sahiwal,NY,NA
1,Aamir,1234 Street,Sahiwal,NY,NA

Lookup Table

SELECT *
INTO dbo.Customer2
FROM (
    SELECT N'3' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,NULL AS [State]
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'2' AS [CustomerId]
        ,N'M Raza' AS [CustomerName]
        ,N'Test Street Address' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'4' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,NULL AS [State]
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'1' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,NULL AS [State]
        ,N'AS' AS [Region]
    ) t;

In depth Demo What are Lookup Transformation Modes ( Full Cache, Partial Cache and No Cache) in SSIS Package - 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 Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache)
• 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)
• Diff between Inner join ( Merge Join Transformation) and Lookup Transformation ( Matched Output)