Menu
Whenever I execute a stored procedure in the ADFv2, it gives me an output as
{
"effectiveIntegrationRuntime": "DefaultIntegrationRuntime (Australia Southeast)",
"executionDuration": 34
}
even though I have set 2 variables as output in the procedure. Is there any way to map the output of the stored procedure in the ADFv2? Till now I can map the output of all the other activities but not of Stored procedures.
835
On the Edit trigger page, review the warning, and then select Save. The pipeline in this example doesn't take any parameters. Click Publish all to publish the change. Go to the Monitor tab on the left to see the triggered pipeline runs.
Search for Lookup in the pipeline Activities pane, and drag a Lookup activity to the pipeline canvas. Select the new Lookup activity on the canvas if it is not already selected, and its Settings tab, to edit its details. Choose an existing source dataset or select the New button to create a new one.
You use data transformation activities in a Data Factory or Synapse pipeline to transform and process raw data into predictions and insights. The Stored Procedure Activity is one of the transformation activities that pipelines support.
A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.
Stored procedure call in Data factory (v2) does not capture the result data set. So you cannot use the stored procedure activity to get the result data set and refer it in next activities. Workaround is to use lookup activity to call exact same stored procedure as lookup will get you the result data set from stored procedure. Replace your Stored procedure activity with lookup and it will work.
120
You could use a lookup activity to get the result. Please reference this post. https://social.msdn.microsoft.com/Forums/azure/en-US/82e84ec4-fc40-4bd3-b6d5-b742f3cd1a33/adf-v2-how-to-check-if-stored-procedure-output-is-empty?forum=AzureDataFactory
Update by Gagan: Instead of getting the output of SP (which is not possible in ADFv2 right now), I stored the output in the table and then apply lookup-foreach to the table to get the value.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
