question

EduardKudlaev-1936 avatar image
0 Votes"
EduardKudlaev-1936 asked EduardKudlaev-1936 commented

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.
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
81813-capture1.png

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.
81709-capture2.png

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
81822-capture3.png
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
81719-capture4.png
but dataflow failed81629-capture5.png

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


azure-data-factory
capture1.png (11.6 KiB)
capture2.png (31.9 KiB)
capture3.png (56.8 KiB)
capture4.png (30.6 KiB)
capture5.png (50.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JikaiJackMa-MSFT avatar image
0 Votes"
JikaiJackMa-MSFT answered JikaiJackMa-MSFT commented

Hello @EduardKudlaev-1936 ,

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EduardKudlaev-1936 Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

0 Votes 0 ·

Hello, yesterday I have a call with support team, and may be a bug in adf. But for now I do not received confirmation. The solution not works in my case.

0 Votes 0 ·

@eduardkudlaev-1936 Thanks for the updates.

0 Votes 0 ·

Hi @EduardKudlaev-1936,

I just heard from our support team about your case. I believe either concat('\'', $LoadStartDate, '\'') or "'{$LoadStartDate}'" is working to resolve the "incorrect syntax" error as you already noticed. However for your sequel question that why no data is populated, the reason is your stored procedure doesn't generate any output so that data flow application can't read it + convert to output properly. As a simple fix, could you please change the following part of your stored procedure from:

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

to:

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

Then try to import schema -> execute data preview again. Hope this helps.

0 Votes 0 ·
EduardKudlaev-1936 avatar image
0 Votes"
EduardKudlaev-1936 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered EduardKudlaev-1936 edited

Hello @EduardKudlaev-1936,
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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @HimanshuSinha-MSFT,
Thank you for your answer.
I've tried to apply your solution, and you are right it works, but unfortunately only in case if we pass null as a parameter.

Here it is pipeline run id with error ba7afc73-5bfc-467b-9371-07a17b1a9ebb. In this case I've passed parameters with datetime value and it fails.
56c7a363-78d5-461f-b014-951f9d7ff335 - I've passed to parameter null and it works fine.

To debug it I even create a table which will store parameters passed to stored procedure. If we pass null record created in the table, but in case parameters it not create a record. It seems that stored proc even ton executed.

Also could you tell me if it is possible to take a look on code prepared by dataflow source to run stored procedure? I expect to see something like EXECUTE stored_proc_name @par1 = val1...

0 Votes 0 ·
EduardKudlaev-1936 avatar image
0 Votes"
EduardKudlaev-1936 answered EduardKudlaev-1936 commented

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.
87842-importprojection.gif



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EduardKudlaev-1936 ,

Thanks for the update! Yes the sproc configuration looks good to me now and I think there's a UX defect which caused the output doesn't show up properly. Before the UX fix is deployed, could you try to apply the following workaround in your data flow?

  1. Click the script button for your data flow entity:
    88641-image.png

  2. Find the SQL sproc source(s) and change this property "resultset" from true to false:
    88546-image.png

  3. Click Ok and try to import schema/data preview again


Let me know if you still see any issues with above steps, thanks.



0 Votes 0 ·
image.png (68.7 KiB)
image.png (96.1 KiB)

Hello,
Thank you for a lot of workarounds provided by you. Appreciated that.
Today I've tried to change the property, but it is still something wrong in my case.
88557-importprojection3.gif


0 Votes 0 ·