in Azure ADF Data Flow used in a Pipeline passing date as string parameter : Job failed due to reason: com.microsoft.dataflow.Issues: DF-DSL-002 - Parameter stream has parsing errors Parameter(s) with errors: df_delta_date. Not honoring the datatype

Question

Hi,

1 have a Pipeline calling a Data Flow.

The dataflow receives as parameter a value date that comes from a a database. The source field is from an azure SQL table and field table is datetime2.

The return value on a select on it is : 2023-11-22 07:32:02 in MSSQL Studio.

This return value is set in a parameter called: df_delta_date of the Dataflow.

Example of the input in the monitoring:

            "df_delta_date": "2023-11-29T09:02:22.6733333Z",

We I run the pipeline calling the Data Flow, I have the error:

Job failed due to reason: com.microsoft.dataflow.Issues: DF-DSL-002 - Parameter stream has parsing errors

Parameter(s) with errors: df_delta_date. Not honoring the datatype of parameter(s) could be one of the causes.

When I hardcoded the value with '2023-11-29T09:02:22.6733333Z'

then I see that in the input on of the Data Flow:

            "df_delta_date": "'2023-11-29T09:02:22.6733333Z'",

we can see that it keeps the single-quote

What do I need to set in the parameter ?

the field that contained the value concatenate with ' at the begining and end ?

regards,

Answer 1

Hi,

Passing the timestamp to the data flow requires explicit conversion from pipeline expression as mentioned under Passing in timestamps, as given in https://learn.microsoft.com/en-us/azure/data-factory/parameters-data-flow#use-parameters-in-a-mapping-data-flow.

But, what i tried I am telling here,

My use case is this: I get a timestamp from a SQL table, using look up activity and passing it to the data flow via data flow parameter. In data flow i have to use this parameter value for where clause of my source.

Lookup output:

"firstRow": {
		"ts": "2023-11-30T04:21:48.37Z"
	}

This lookup output is taken to data flow.

My data flow param type: string.

My data flow param name: timestamp

Pipeline expression on the data flow parameter: (click on data flow activity from pipeline, choose pipeline expression and give the below)

@activity('Lookup1').output.firstRow['ts']

Data flow param screenshot:

This takes my timestamp value as a string to the data flow.

From my data flow source, i am reading this data flow parameter to filter out source data from a sql table,

From data flow source option, i mentioned below query-

"select * from dbo.testtbl where ts='{$timestamp}'"

Where, {$timestamp} is the data flow parameter that brings in my timestamp value for filtering records from testtbl.

This works fine for me. Instead of trying with timestamp type, you can declare it as string in data flow and try the above.

Please let us know if you have questions. Would be happy to assist.

Thanks