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 





5 comments:

  1. Excellent Explanation. I learn a lot from this demo. Thank you.

    ReplyDelete
  2. Meritstep is the Top & Best Software & IT Training Institute for Workday Online Training Course & Learning Organizations India, USA, UK. Enroll Now for Workday Online Training Classes.

    ReplyDelete
  3. That’s a very good article posted by the blogger and I think it was very good for beginners like me since I recently learned SQL and wanted to properly learn the functions of JOIN and UNION. I also have a good post for SSIS Insert or Update functions.

    SSIS Upsert

    ReplyDelete
  4. You have noted very interesting details ! ps nice internet site . More about the author

    ReplyDelete