Sunday, 27 December 2020

ADF Snack: use stored procedure output

Case
I want to execute a Stored Procedure in Azure Data Factory and use its output further on in the pipeline. The stored procedure activity does not return the output. How do you accomplish that?
Stored Procedure Activity not working for output
























Solution
At the moment the Stored Procedure Activity in ADF doesn't handle output parameters, but there is a workaround.

1) Alter Stored Procedure
ADF can't handle output parameters, but you can add a SELECT statement at the end to return the value. Make sure to add an alias, but also make sure to only return one row in your SELECT query.
-- Alter Stored Procedure
ALTER PROCEDURE AddNumbers
(
    -- Parameters
    @number1 as int
,	@number2 as int
,	@Result as int output
)
AS
BEGIN
	-- Do the math
    set @Result = @number1 + @number2
	-- For ADF
	select @Result as Result
END

2) Use Lookup Activity instead
Now instead of using the Stored Procedure Activity we will be using the Lookup Activity.
  • Add a Lookup activity to the pipeline and give it a descriptive name
  • On the Settings tab, add a new Source Data pointing to the database with the Stored Procedure. Leave the table property of the dataset empty (we will use a Stored Procedure instead).
  • After adding the Source dataset, choose Stored Procedure under the Use query property
  • Next select your Stored Procedure
  • Import the parameters
  • Set the parameter values (output parameters that are not used by ADF should be Treat as null
  • Make sure the option First row only is enabled
  • Now debug your pipeline and compare the output of the Lookup activity with the Stored Procedure activity. In the next step we will show you the expression to retrieve this value.
Using the Lookup activity to execute a Stored Procedure














3) Getting the output of the Stored Procedure
Now with an expression in a next activity you can retrieve the output value of the Stored Procedure executed in the Lookup activity: @activity('Do some more math').output.firstRow.Result
The yellow part is the name of the Lookup activity and the green part is the result:
Compare the red part with the expression
















If you want to store the result in a pipeline variable then you might need to add a type conversion @string(activity('Do some more math').output.firstRow.Result)

Convert int to string





















Summary
The Stored Procedure activity is pretty useless when trying to get some output from a Stored Procedure, but there is a simple workaround with the Lookup activity. In the next blog post we will show an other example of using the output of a stored procedure within ADF.

3 comments:

  1. Good Afternoon. I have a follow up question on this. I am forcing a RETURN value of 200 for when something in the stored procedure errors. I do not want my next step in ADF to run if the stored procedure errors. How did i incorporate that in ADF?

    ReplyDelete
    Replies
    1. After SP activity , add your next activity only on Success condition. So that ADF pipeline will fail and will not proceed to next step

      Delete
  2. Thanks for sharing. Very useful advice.

    ReplyDelete