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 

34 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
  9. What a post I've been looking for! I'm very happy to finally read this post. 토토커뮤니티 Thank you very much. Can I refer to your post on my website? Your post touched me a lot and helped me a lot. If you have any questions, please visit my site and read what kind of posts I am posting. I am sure it will be interesting.

    ReplyDelete
  10. Pretty nice post. I just stumbled upon your weblog and wanted to say that I have really enjoyed browsing your blog posts. After all I’ll be subscribing to your feed and I hope you write again soon 먹튀검증업체 I would like to write an article based on your article. When can I ask for a review?!

    ReplyDelete
  11. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article :D 안전놀이터순위

    ReplyDelete
  12. This is a wonderful news regarding bloggers. It opens admission to a huge spectrum of individuals who are finding an establishment to express their inquiries. With your theme, I could gain the visibility We are having at this moment. Thanks just for this informative publish, I learned a whole lot! 토토사이트

    ReplyDelete
  13. How do you make this blog look this sick! Email me if you get the chance and share your wisdom. Id be thankful! 경마사이트

    ReplyDelete
  14. Well-written article. I was checking continuously to this website and Im really inspired! Very educational information, especially the fifth sentences. I really want this kind of info. I was looking for this particular knowledge for a very long. Thankx and best of luck. 카지노사이트

    ReplyDelete
  15. Useful information. Lucky me I found your web site by chance, and I’m shocked why this coincidence did not took place earlier! I bookmarked it. 메이저사이트

    ReplyDelete
  16. When I read an article on this topic, the first thought was profound and difficult, and I wondered if others could understand.. My site has a discussion board for articles and photos similar to this topic. Could you please visit me when you have time to discuss this topic? Keo hay

    ReplyDelete
  17. I'm glad I found this web site, I couldn't find any knowledge on this matter prior to.Also operate a site and if you are ever interested in doing some visitor writing for me if possible feel free to let me know, im always look for people to check out my web site 사설토토사이트

    ReplyDelete
  18. Hello, I am one of the most impressed people in your article. 메이저토토사이트 I'm very curious about how you write such a good article. Are you an expert on this subject? I think so. Thank you again for allowing me to read these posts, and have a nice day today. Thank you.

    ReplyDelete
  19. It is my first visit to your blog, and I am very impressed with the articles that you serve. Give adequate knowledge for me. Thank you for sharing useful material. I will be back for the more great post. 먹튀검증사이트 But by chance looking at your post solved my problem! I will leave my blog, so when would you like to visit it?!

    ReplyDelete
  20. An intriguing discussion may be worth comment. I’m sure you should write much more about this topic, may well be described as a taboo subject but generally folks are too little to chat on such topics. An additional. Cheers 토토사이트

    ReplyDelete
  21. I figure this article can be enhanced a tad. There are a couple of things that are dangerous here, and if you somehow managed to change these things, this article could wind up a standout amongst your best ones. I have a few thoughts with respect to how you can change these things. 메이저사설놀이터

    ReplyDelete
  22. Hello, I read the post well. 메이저토토 It's a really interesting topic and it has helped me a lot. In fact, I also run a website with similar content to your posting. Please visit once


    ReplyDelete
  23. 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.

    ReplyDelete
  24. 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
  25. 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
  26. This is the perfect post.메이저토토사이트 It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.


    ReplyDelete
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. When I read your article on this topic, the first thought seems profound and difficult. There is also a bulletin board for discussion of articles and photos similar to this topic on my site, but I would like to visit once when I have time to discuss this topic. 안전토토사이트


    ReplyDelete
  33. 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