SQL Query in Azure Dataflow does not work when using parameter value in where clause-CodePudding

I use a Azure Datafactory Pipeline.

Within that pipeline i use 2 activities:

Lookup to get a date value This is the output:

"firstRow": { "Date": "2022-10-26T00:00:00Z"
A dataflow which is getting the date from the lookup in 1 which is used in the source options SQL query in the where clause:

This is the query:

"SELECT ProductID ,ProductName ,SupplierID,CategoryID ,QuantityPerUnit ,UnitPrice ,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued,LastModifiedDate FROM Noordwind.Products where LastModifiedDate >= '{$DS_LastPipeLineRunDate}'"

When i fill the parameter by hand with for example '2022-10-26' then it works great, but when i let the parameter get's its value from the Lookup in step 1 the dataflow fails Error message:

{"message":"Job failed due to reason: Converting to a date or time failed due to an invalid character. Details:null","failureType":"UserError","target":"Products","errorCode":"DF-Executor-Conversion"}

This is the parameter in the pipeline view, but clicked on the dataflow:

I have tried casting the date al kind of things but not the right thing. Can you help me.

CodePudding user response：

I have reproduced the above and got the below results.

My source sample data from SQL database.

enter image description here

For demo, I have used set variable for the date and given a sample date like below.

enter image description here

Created a string parameter and given this variable value to it.

enter image description here

In your case pass the lookup firstrow output date.

I have used below dataflow expression in the query of dataflow source and got the desired result.

concat('select * from dbo.table1 where d1 >=','\'',$date_value,'\'')

enter image description here

Result in a target SQL table.

enter image description here

CodePudding user response：

I have created an activity set variable:

De eerste pipeline geeft nog steeds de juiste datum aan. Ik heb deze zelfs nog even omgezet naar datetime voor de zekerheid.

Ik kan de variabele aanmaken met string, dit heb ik gedaan. Code: @activity('LookupLastPipelineRunDate').output.firstRow

Ondanks dat de activiteit Set variable fout loopt lijkt het alsof de datum netjes binnen komt als input:

En toch krijg ik weer een foutmelding:

Als ik de foutmelding lees, zegt hij dat er geen Datum gestopt kan worden in de variabele omdat dat van het type String is. maar als ik een nieuwe variabele aanmaak kan ik ook alleen maar voor string, boolean en array kiezen dus is er geen andere optie.

Omdat ik op onderstaande link heb gekeken.