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 

25 comments:

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

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

    ReplyDelete
  3. How do you make this blog look this sick! Email me if you get the chance and share your wisdom. Id be thankful! ๊ฒฝ๋งˆ์‚ฌ์ดํŠธ

    ReplyDelete
  4. 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.
    VidMate | Bluestacks|
    Spice Money Login

    ReplyDelete
  5. While looking for articles on these topics, I came across this article on the site here. As I read your article, I felt like an expert in this field. I have several articles on these topics posted on my site. Could you please visit my homepage? ํ† ํ† ์‚ฌ์ดํŠธ๋ชจ์Œ


    ReplyDelete
  6. Your internet site has great web content. I assume it was a great possibility to transform my mind once more after reading this write-up. I'm creating like you. Would certainly you such as to see my blog post as well as request for comments? ๋ฐ”์นด๋ผ์‚ฌ์ดํŠธ

    ReplyDelete
  7. Youre so right. Im there with you. Your weblog is definitely worth a read if anyone comes throughout it. Im lucky I did because now Ive received a whole new view of this. ๋ฉ”์ด์ €์‚ฌ์ดํŠธ์ถ”์ฒœ


    ReplyDelete
  8. Many thanks for the article, I have a lot of spray lining knowledge but always learn something new. Keep up the good work and thank you again. ๋จนํŠ€์‚ฌ์ดํŠธ


    ReplyDelete
  9. That's a great article! The neatly organized content is good to see. Can I quote a blog and write it on my blog? My blog has a variety of communities including these articles. Would you like to visit me later? ํ† ํ† ์‚ฌ์ดํŠธ์ถ”์ฒœ


    ReplyDelete
  10. I was impressed by your writing. Your writing is impressive. I want to write like you.์•ˆ์ „๋†€์ดํ„ฐ I hope you can read my post and let me know what to modify. My writing is in I would like you to visit my blog.


    ReplyDelete
  11. I'm so happy to finally find a post with what I want. ๋ฉ”์ด์ €ํ† ํ† ์‚ฌ์ดํŠธ You have inspired me a lot. If you are satisfied, please visit my website and leave your feedback.


    ReplyDelete
  12. Oh, the data you've shared in this incredible article is just magnificent. I am definitely going to make more use of this data in my future projects. You must continue sharing more data like this with us. ๋ฉ”์ด์ €๋†€์ดํ„ฐ


    ReplyDelete
  13. That's a really impressive new idea! casino api It touched me a lot. I would love to hear your opinion on my site. Please come to the site I run once and leave a comment. Thank you.


    ReplyDelete
  14. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place. Samsung Galaxy M12 Price

    ReplyDelete
  15. This is a very nice blog and learned more knowledge to read this post thanks for sharing this informative post. เธ•ิเธ”เธ•่เธญ igoal 88

    ReplyDelete
  16. It’s hard to stay away from favorite games, especially today when we all have the best gaming tools. It’s not just about participating in sports, it’s about competition. We definitely don’t want to miss the opportunity to make ourselves a better player in the game than our opponents.
    GTA 5 APK
    GTA 6 APK
    GTA San Andreas APK
    GTA Vice City APK

    ReplyDelete
  17. I’ve been surfing online more than three hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content as you did, the web will be a lot more useful than ever before. WeClub

    ReplyDelete
  18. It is a great website.. The Design looks very good.. Keep working like that!. totobet hk

    ReplyDelete
  19. Amazing information providing by your article, thank you so much for taking the time to share a wonderful article.

    ReplyDelete
  20. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information... slot gacor

    ReplyDelete
  21. Thanks for your insight for your fantastic posting. I’m glad I have taken the time to see this. lcctoto

    ReplyDelete