Search code examples
stored-proceduresazure-data-factorydataflow

Pass datetime parameter from pipeline to dataflow source stored procedure azure data factory

I faced with the issue when I tried to pass datetime parameter from pipeline to dataflow stored procedure. I've using stored procedure for getting full or incremental dataset. Pipeline looks like this pipeline In lookup I am getting 'n/a' or last load date. to determine do I need full or incremental load. After that I passed it to dataflow with the expressions:

LoadStartDate activity('GetLastLoadData').output.value[0].LastLoadedDate LoadEndDate if(equals(activity('GetLastLoadData').output.value[0].LastLoadedDate, 'n/a'), '' ,pipeline().TriggerTime)

parameters

Output seems good and as far as I can see parameters of dataflow was set with datetime strings:

Input
{
    "dataflow": {
        "referenceName": "dataflow1",
        "type": "DataFlowReference",
        "parameters": {
            "LoadStartDate": "'2021-01-16 14:15:36.697'",
            "LoadEndDate": "'2021-03-25T18:08:48.7558444Z'"
        },

Stored procedure has two parameters defined as string. I am doing validation

 CREATE PROCEDURE [dbo].[Export]
@LoadStartDate VARCHAR(30) = NULL,
@LoadEndDate VARCHAR(30) = NULL

AS 

DECLARE @error VARCHAR(200) = NULL

IF (@LoadStartDate IS NULL AND @LoadEndDate IS NOT NULL) OR
   (@LoadStartDate IS NOT NULL AND @LoadEndDate IS NULL)
   SELECT @error = 'Parameters @LoadStartDate and @LoadEndDate should be specified.'

IF @LoadStartDate IS NOT NULL AND TRY_CAST(@LoadStartDate AS DATETIME2(3)) IS NULL 
SELECT @error = 'Parameter @LoadStartDate is not a date.'
IF @LoadEndDate IS NOT NULL AND TRY_CAST(@LoadEndDate AS DATETIME2(3)) IS NULL 
SELECT @error = ' Parameter @LoadEndDate is not a date.'

In management studio procedure works well but not in adf...

ADF dataflow has two parameters defined as a string ADF parameters

The setting of a source look like this: source settings

When I deteled input parameters everything works well( as a full load), but when I've tried to specify parameters it failed.

Error details
Error code
DFExecutorUserError
Troubleshooting guide
Failure type
User configuration issue
Details
{"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: Incorrect syntax near '-'.","Details":"Incorrect syntax near '-'."}
Source
Pipeline
LoadDataSetCompany_copy1
Data flow
dataflow1
Monitor
Data flow activity
LoadDataSet

I've also tried to select the expression checkbox, during passing parameter to dataflow but it did not help.

Does anybody have an idea what is wrong?

Appreciated the answers.

Solution

  • Per my experience, you may don't need to use Data Flow to build your pipeline. The stored procedure is new to Data Flow and may still have some issues.

    Please try Lookup active + Copy active to build the pipeline, it should works well.

    HTH.