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 

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

    ReplyDelete
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. This is a very nice blog and learned more knowledge to read this post thanks for sharing this informative post. ติดต่อ igoal 88

    ReplyDelete
  21. 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
  22. 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
  23. It is a great website.. The Design looks very good.. Keep working like that!. totobet hk

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

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

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

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

    ReplyDelete