In case you are also among those who are struggling to catch the value of output parameters returned by SQL Server stored procedures in Azure Data Factory’s “Stored Procedure” activity then you are looking at the right page to learn a workaround for this. By default, ADF Stored Procedure activity doesn’t differentiate between the stored procedure’s parameters as input or output e.g. we have the following stored procedure
It has one input parameter and one output parameter but when you import parameters in ADF stored procedure activity, these are shown as –
i.e. no differentiation between input vs. output parameter. As a result, you can not collect the value returned by output parameter after procedure’s execution.
As a workaround, you can use Lookup activity which allows you to capture the value from its output. You can call the stored procedure in lookup activity as follows –
You can capture the value of output returned by stored procedure in next activity as –
where “ID” is the column name so you can refer to your column names as firstrow object’s properties.
However, to get this working, you need to ensure that you are returning the value of output parameters as SELECT statement inside the stored procedure so that it becomes a “single” row and gets read by firstrow object in the next activity i.e.
