How To Use Dynamic Query in Lookup Transformation in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

Scenario:

We have a big reference table from which we need to extract records in Lookup so we can use against Source File. The table has the data from three regions AS,EU and NA. The files come with the name AS_FileName,EU_FileName and NA_FileName, Instead of extracting all the records for lookup , we want to extract the records according to the region Name. How can we perform that?

What we will learn in this video

1. How to create SSIS Package from scratch
2. How to Loop through files by using For-each Loop Container in SSIS Package
3. How to Read the part of File Name and Save into Variable by using Expressions
4. How to make use Variable to make Lookup Query dynamic in SSIS Package
5. How to filter the rows for Lookup Reference Data Set by using Variable Value

Solution:

Let's create Lookup ( Reference Table) with some Data.

--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]
        ,'Germany' 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]
        ,'France' 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;

Query for the Lookup

Select CustomerId,CustomerName,Region from [dbo].[Lkp_Customer] Where Region='AS'

Expressions for RegionCode Variable
Substring( @[User::FileName],1,2)


Expressions for Flat File Connection Manager
@[User::LocalFolder]+"\\"+ @[User::FileName]

How to use Variable in Lookup Transformation SQL Query to Make it Dynamic in SSIS Package

  Related Posts / Videos on Lookup Transformation 

• 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)
• Detailed Demo on Lookup Transformation Modes ( Full Cache, Partial, No Cache) by using SQL Server Profiler
• Diff between Inner join ( Merge Join Transformation) and Lookup Transformation ( Matched Output)