How to process output from stored procedure in Azure Data Factory

Anonymous
2022-07-13T17:09:24.837+00:00

I am trying to create pipeline in Azure Data Factory to validate data.
So, the logic is:
If the output of the stored procedure is 1, I will send email (using Azure Logic App) that we have an error.
If the output of the stored procedure is 0, do nothing.

This is detail of sp:

BEGIN  
 
  declare @LastDay datetime;   

  set @LastDay =(  
SELECT distinct top 1  
          cast(convert(char(11), [DateLoad], 113) as datetime) as DateLoad  
FROM [dbo].[Table]  
order by DateLoad desc  
)  
If @LastDay = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))  
begin  
return 0  
end  

else  

begin  
return 1  
end  

END  

Here is current data flow (that I think it might be a solution, but I doubt it at this moment):
I know how to connect to Azure Logic App (after this process is completed), but I just want to know how to process the output of 1.

220467-image.png

When I researched online, I see options of using of "Lookup" or " Set Variable" etc.

https://www.sqlservercentral.com/articles/reading-stored-procedure-output-parameters-in-azure-data-factory
https://microsoft-bitools.blogspot.com/2020/12/adf-snack-use-stored-procedure-output.html#:~:text=At%20the%20moment%20the%20Stored,but%20there%20is%20a%20workaround.&text=ADF%20can't%20handle%20output,row%20in%20your%20SELECT%20query.

This is a simple process, and I would like to get some feedback before I get deeper thinking. :)

Thank you.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,095 questions
0 comments No comments
{count} vote

Accepted answer
  1. VinodKumar-0434 266 Reputation points
    2022-07-13T19:20:35.587+00:00

    Hi @KingJava,

    Here are the steps that will help you.

    For the look up you have to create a dataset as in below screenshot. (While create it will ask for SQL server linked service and the table name(optional))

    220543-pic1.png

    Then in the next step you can select the proc that you are trying to call as in screenshot 2.

    220496-pic2.png

    Debug will give you this result. the result key can be different in your case. That is the column header from your proc.

    220561-pic3.png

    If your proc is returning values in integer, please create a variable with string as we don't have int support type for variable.

    220497-pic4.png

    We will convert the output of the stored proc into string using string() function. please use the below code in the set variable value field. I have used result at the end you have to use you column name

        @string(activity('Call Proc').output.firstrow.result)  
    

    220571-pic5.png

    Your second question is why not stored procedure activity
    Reason : Stored procedure activity does not capture the result dataset. So we won't be able to read the output. Procedure activity can be used to call the procedure without waiting for response.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. VinodKumar-0434 266 Reputation points
    2022-07-13T17:44:00.527+00:00

    HI @KingJava , Welcome to the Microsoft Q&A Platform, and thanks for your query.

    You are correct we have to assign the output of the proc activity to a variable and then you can validate the condition in if activity. if the value is 1 then call logic app to send out communication and you can ignore the 0 condition.

    Hope this help your question.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".