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