Question 104648 in Azure Question posted in
The official documentation to get you started can be found here.

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

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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


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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search