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

Question

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.
First I tried to define input parameters as datetime2, but I found article that datime2 not supported and denifition of my stored procedure looks like this

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 stored procedure I use logic to implement full or incremental load using dynamic sql. SP works well on azure sql server.

My pipeline looks following

In lookup I check last modified date to know shall it be full or incremental load.

I passed to dataflow 2 parameters, that defined there as a string.

expressions have the following code:
LoadStartDate @activity('GetLastLoadData').output.value[0].LastLoadedDate

LoadEndDate @if(equals(activity('GetLastLoadData').output.value[0].LastLoadedDate, null), null,pipeline().TriggerTime)

In data flow I've try to setup stored procedere call

Utilizing expressions
LoadStartDate iif(length($LoadStartDate) > 23,left($LoadStartDate, 23),toString(null()))
LoadEndDate iif(length($LoadEndDate) > 23,left($LoadStartDate, 23),toString(null()))
The goal of above expression get string in format '2021-01-25T13:56:56.952'

Diring dubug it seems that parameter values are ok

but dataflow failed

Does anyone have an idea what is wrong? Any ideas appreciated.

Answer 1

Hello @Eduard Kudlaiev ,

Sorry for the inconvenience, the issue here is since the parameter value has space in the middle, you can imagine that the generated SQL statement has something like "EXEC [test].[proc] @LoadStartDate=2021-03-26 17:53:23.873", which would cause the syntax error as you have noticed.

As a workaround, in the dynamic content, you can replace $LoadStartDate with concat('\'', $LoadStartDate, '\'') to force to add the quote for parameter value. Please let me know if it still doesn't work for you.

Thanks,
Jack

Answer 2

it seems that abilities working with the stored procedure in the sink are very limited.
I've tried to prepare text like execute [schema_name].[sp_name] @param1= val1... It failed with error Incorrect syntax near the keyword 'EXECUTE'.
Micorsoft, seriously????

Only what is work without any problem it is a procedure with no parameter.

I am very disappointed with such limitations in data factory.

If I will find a solution I will post it here. For not it seems that I have to utilize staging instead of relying on stored procedure as a source.

Answer 3

Hello @Eduard Kudlaiev ,
Thanks for the ask and using the Microsoft Q&A platform .

I did tried out the below expression and it worked for me .

iif(length(toString($LoadStartDate)) > 23 ,left(toString($LoadStartDate), 23),'null')  

Please do let me know how it goes .
Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 4

Thank you for suggesting expression concat('\'', $LoadStartDate, '\'') or "'{$LoadStartDate}'"
but stored proc works well in management studio, and dataflow even did not get a schema. Gif attached. Do you have a idea with this strange behavoir? I more than sure that something wrong on ADF dataflow side.