sql-serverstored-proceduresazure-data-factory

How to get date String output from stored procedure?


I have a stored procedure in Azure SQL DB that returns 2 output parameters, startDate and endDate based on an input date string.

This is the stored procedure:

CREATE PROCEDURE dbo.SP_DEDUCE_START_DATE_END_DATE
    @inputDateTimeString NVARCHAR(MAX) NULL,
    @startDate NVARCHAR(MAX) NULL OUT,
    @endDate NVARCHAR(MAX) NULL OUT
AS
BEGIN
    -- exact logic omitted as to demonstrate the 2 outputs date string only
    SELECT 
        @inputDateTime AS inputDateTime, 
        @startDate AS startDate, @endDate AS endDate

    RETURN;
END
GO

What I am looking for: a complete end-to-end solution to:

  1. get the 2 output date strings startDate, endDate from the stored procedure
  2. store the 2 date strings to 2 pipeline variables START_DATE, END_DATE (as I have other processing logic to affect the pipeline variables)
  3. feed the 2 pipeline variables to downstream dataflow(s) as parameters

What I have tried/viewed:

  1. The link in this SO answer - It's close, but not a complete answer as the syntax seems incomplete
  2. This link mentioned that Stored Procedure activity cannot be used to access the SP outputs; will need lookup activity instead

Solution

  • I would like to share my final end-to-end working solution.

    Here is a simplified overview of my final pipelines: enter image description here A lookup activity to trigger the SP; followed by setting 2 variables, then feed to downstream dataflow.

    The syntax of parameters supplied to the SP: enter image description here You have to check Treat as null option for the 2 outputs. Then supply the date string to SP.

    To read the SP outputs and set the variables(only used START_DATE here as example since END_DATE is identical): enter image description here

    Put in @activity('SP_DEDUCE_START_DATE_END_DATE').output.firstRow.startdate

    To feed the 2 variables to downstream dataflow: enter image description here

    Put in @variables('START_DATE') in pipeline expression.