Use Stored Procedure with Parameters in Lookup Transformation ( Full Cache Mode) in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

Scenario: 

We need to extract the records from a reference table for Lookup Transformation and we are using a Stored Procedure. As Reference Table has records for multiple regions and we get Source Files for each region, we don't want to put all the records in memory if we can just get the records related to Source File Region. The Stored Procedure accepts the RegionCode as Parameter and get the related records.
How can we pass the parameter value to stored procedure in Lookup Transformation that we will extract from file name in SSIS Package?

What you 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 of Variable to make Lookup Query dynamic for Stored Procedure in SSIS Package
  5. How to filter the rows for Lookup Reference Data Set by using Variable Value in Stored Procedure in SSIS Package


Solution:

Let's create Sample Table for Reference data with some sample records and a stored Procedure that can accept RegionCode as input and return us CustomerId,CustomerName,RegionCode so we can map the CustomerName and RegionCode with input columns and add CustomerId as output columns from Lookup Transformation.

--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]
        ,'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 stored Procedure to use in Lookup Transformation
CREATE PROCEDURE dbo.GetCustomer 
@Region VARCHAR(100)
AS
BEGIN
    SELECT customerid
        ,customername
        ,Region
    FROM dbo.Lkp_Customer
    WHERE Region = @Region
    END






Source Flat File
CustomerName,CustomerRegion,SaleAmt
Aamir,AS,1000
M Raza,AS,500


Use Stored Procedure with Parameter in Lookup Transformation in SSIS Package - SSIS Tutorial




  Related Posts / Videos on Lookup Transformation 

9 comments:

  1. Lookup Reference Data Set by using Variable Value in Stored Procedure in SSIS Package 9apps.

    ReplyDelete
  2. Your article was very impressive to me. It was unexpected information,but after reading it like this 메이저토토사이트, I found it very interesting.

    ReplyDelete
  3. I think a lot of articles related to 메이저사이트 are disappearing someday. That's why it's very hard to find, but I'm very fortunate to read your writing. When you come to my site, I have collected articles related to this. My site name is .

    ReplyDelete
  4. Nice to meet you. Your post was really impressive. It's an unexpected idea. It was a great stimulus to me.How did you come up with this genius idea? Your writing ability is amazing. Like me, you may be interested in my writing. If you want to see my article, please come to 토토사이트!!

    ReplyDelete
  5. How to Loop through files by using For-each Loop Container in SSIS Package ?shareit vidmate app

    ReplyDelete
  6. Pretty nice post. I just stumbled upon your weblog and wished to say that I have truly enjoyed surfing around your blog posts. In any case I will be subscribing to your rss feed and I hope you write again soon 먹튀신고

    ReplyDelete
  7. I know what I'm going to do in the future. Thanks to you, I had a great time. I'll see you next time.사설토토사이트

    ReplyDelete
  8. I feel like I'm becoming a genius because there are so many good writings in the world.안전놀이터추천

    ReplyDelete