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 

54 comments:

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

    ReplyDelete
  2. 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
  3. 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
  4. How to Loop through files by using For-each Loop Container in SSIS Package ?shareit vidmate app

    ReplyDelete
  5. 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
  6. 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
  7. I feel like I'm becoming a genius because there are so many good writings in the world.안전놀이터추천

    ReplyDelete
  8. 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
  9. 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
  10. 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
  11. 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
  12. How do you make this blog look this sick! Email me if you get the chance and share your wisdom. Id be thankful! 경마사이트

    ReplyDelete
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. I’m very pleased to discover this site. I want to to thank you for ones time for this particularly wonderful read!! I definitely savored every part of it and i also have you saved as a favorite to see new information on your blog. 메이저놀이터


    ReplyDelete
  34. 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
  35. My curiosity was solved by looking at your writing. Your writing was helpful to me.룰렛사이트I want to help you too.

    ReplyDelete
  36. It seems like I've never seen an article of a kind like . It literally means the best thorn. It seems to be a fantastic article. It is the best among articles related to 바카라사이트. seems very easy, but it's a difficult kind of article, and it's perfect.

    ReplyDelete
  37. 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
  38. Traditional bookstores have always existed on high streets, but in the digital age, the internet is proving to become a serious competitor to traditional brick and mortar stores. This article examines both sides of the coin and provides an appropriate insight into the phenomenon of shopping of books online. 메이저사이트추천

    ReplyDelete
  39. Excellent read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch as I found it for him smile Therefore let me rephrase that: Thank you for lunch. 메이저사이트

    ReplyDelete
  40. This is a very nice blog and learned more knowledge to read this post thanks for sharing this informative post. ติดต่อ igoal 88

    ReplyDelete
  41. 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
  42. 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
  43. I think your website has a lot of useful knowledge. I'm so thankful for this website.
    I hope that you continue to share a lot of knowledge.
    This is my website.
    머니상

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

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

    ReplyDelete
  46. Ok, thanks for the information and have a good day! mobdro

    ReplyDelete
  47. I’m impressed, I have to admit. Truly rarely should i encounter a blog that’s both educative and entertaining, and without a doubt, you’ve hit the nail within the head. Your notion is outstanding; the pain is an issue that insufficient everyone is speaking intelligently about. I am very happy that we stumbled across this inside my try to find some thing relating to this. 메이저토토추천

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

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

    ReplyDelete