How to Load Lookup Data Parallel in SSIS Package ( Cache Transformation) - SQL Server Integration Services (SSIS) Tutorial

In this video we will learn how Lookup Transformations work in sequence to load the data into memory by performing a demo.

In second part of demo , we will learn how to use the Cache Transformations to load Lookup Data to Memory parallel and then use those Cache Transformation connection managers in the Lookup Transformations.

In scenarios where we have to use multiple lookup Transformations in Data Flow Task and those look up Transformation can take long time to load data on pre execution as they load data into memory one after one.

By using Cache Transformations, we can read data from multiple sources in parallel and then load into Memory so we can make use of Cache Transformations in other Data Flow Tasks and avoid sequential loading of Lookup data into memory.


Script used in " Load Lookup Data Parallel in SSIS Package"

--Create table [dbo].[dbo.Src_Customer]
CREATE TABLE [dbo].[dbo.Src_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].[dbo.Src_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;

CREATE TABLE dbo.LkpCustomer (
    CustomerId INT
    ,CustomerName VARCHAR(100)
    )
GO

INSERT INTO dbo.LkpCustomer
VALUES (
    1
    ,'Aamir'
    )
    ,(
    2
    ,'M Raza'
    )
    ,(
    3
    ,'Robert'
    )

CREATE TABLE dbo.LkpSale (
    CustomerName VARCHAR(100)
    ,SaleAmt INT
    )

INSERT INTO dbo.LkpSale
VALUES (
    'Aamir'
    ,100
    )
    ,(
    'M Raza'
    ,200
    )
    ,(
    'Robert'
    ,300
    )