stored-proceduresazure-data-factorypipeline

Azure Data Factory - wait until stored procedure is executed before continuing the next activities

I have an ADF pipeline in which a number of activities are defined, including a stored procedure. This stored procedure is followed by other activities. The stored procedure uses a number of pipeline variables to execute a merge-insert-delete statement.

The pipeline by itself does entirely what it is supposed to do. All steps can be executed, all steps execute what I want them to execute.

But...the merge-insert-delete statement can take some time. Depending on the table, this can be 1 second, can also take 4 minutes. And here is the problem now. When the activity stored procedure MergeInsertDelete is triggered in the pipeline, it does not wait for the query to end, the subsequent activity, in this case the if condition, is already executed.

So I want to obtain that the If condition starts only when the generated query from the stored procedure is executed.

I've solved this for now by creating a separate pipeline for this MergeInsertDelete for all my pipelines, but I've had to create 120 separate pipelines as a result, and I suspect that this is really not the intention. In those separate pipelines I can check the 'wait on completion' checkbox, this somehow forces me to finalize the query.

Solution

  • Store procedure activity it will only trigger the store procedure, and it doesn't wait for the completion and doesn't provide any output.

    The possible workaround you can try is to add output parameter in in your Store procedure

    CREATE  PROCEDURE MergeInsertDelete4
@status NVARCHAR(50) OUTPUT
AS
BEGIN
----your Stored procedure-----
SET @status =  'Success';
END;

    So, after completion of stored procedure code execution it will set the value to output parameter.

    And call this this stored procedure from script activity or lookup activity as below:

    DECLARE @outputStatus NVARCHAR(50);
EXEC MergeInsertDelete4 @status = @outputStatus OUTPUT;
SELECT @outputStatus AS  Status;

    So only after execution of whole Stored procedure script activity or lookup activity will show as succeeded and then you can execute next activities.