0

I use a Azure Datafactory Pipeline. enter image description here

Within that pipeline i use 2 activities:

  1. Lookup to get a date value This is the output:

    "firstRow": { "Date": "2022-10-26T00:00:00Z"

  2. 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:

enter image description here

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: enter image description here

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

UPDATE: After a question from Rakesh: This is the activity parameter @activity('LookupLastPipelineRunDate').output.firstRow

Ronald
  • 13
  • 1
  • 7
  • Is the pipeline expression `@activity('lookup').output.firstRow.Date`? could you please add that one also for better understanding? – Rakesh Govindula Oct 27 '22 at 00:48

2 Answers2

0

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

Rakesh Govindula
  • 5,257
  • 1
  • 2
  • 11
  • Thanks but your idea dit not work. Maybe i'm doing something wrong. Let me explain in my answer so i can use pictures to explain – Ronald Oct 27 '22 at 18:33
0

I have created an activity set variable:

enter image description here

The first pipeline still returns the right date. I even converted it just to be sure to datetime.

enter image description here

I can create a variable with type string. enter image description here Code: @activity('LookupLastPipelineRunDate').output.firstRow

Regardless of the activity set variable that fails, it looks like the date enters nicely as an input in the Set variable activity

enter image description here

And still a get an error: enter image description here

When i read this error message, it says that you can't put a date in a string variable. But i can only choose string, boolean and array, so there is no better option for this.

I also reviewd this website. enter link description here

There for i have altered the table which contains the source data which i use in the dataflow. I Deleted the column LastModifiedDate because it has datatype datetime. Now i created the same column with datatype datetime2 I did this because i read that datetime2 has less problems with conversions.

enter image description here

Ronald
  • 13
  • 1
  • 7