sqlazure-data-factoryazure-cloud-services

Dynamic SQL query in ADF pipeline

I am trying to replace a table in a SQL query dynamically in ADF pipeline. I tried these queries to get it:

select * 
from c 
where timestampToDateTime("@{concat(variables('lastUpdatedColumn'),'*1000')}") < 
"@{formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss.fffffffZ')}"

select * 
from c 
where TimestampToDateTime(variables('lastUpdatedColumn')*1000) < 
"@{formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss.fffffffZ')}"

But both queries are returning the same output - shown here:

 "select * from c where TimestampToDateTime(\"c._ts*1000\") < \"2024-07- 
  24T13:03:24.6140044Z\"",

It is being replaced with "c._ts*1000" . But I want to remove those slashes(,/). How could we remove those? Thanks in advance

Solution 
  • select * from c where timestampToDateTime("@{concat(variables('lastUpdatedColumn'),'*1000')}"") < "@{formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ss.fffffffZ')}""

    You have provided the where condition in double quotes(") in above query so it is taking as string that's the reason to get back slashes (\) as mentioned below:

    enter image description here

    To remove back slashes, remove double quotes to the condition as below:

    select * from c where timestampToDateTime(@{concat(variables('lastUpdatedColumn'),'*1000')}) < @{formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ss.fffffffZ')}

    You will get the output as shown below:

    enter image description here