Azure Data Factory V2 - Add TriggerTime to Kusto query

Question

Hi, I want to run Kusto query with a time parameter in ADF so I can debug conveniently. I try many times and want to know how to pass parameter to it although I can write 'let timestring = now()' to make code works. Here is part of my Kusto code.

let timestring = todatetime(@convertFromUtc(pipeline().TriggerTime,'GMT Standard Time','yyyy-MM-dd HH:mm:ss.fff'));
let biasDate = startofday(timestring);
let endDate = datetime_add('day', -2, biasDate);
let startDate = datetime_add('day', -3, biasDate);

and adf will tell me "
Failure happened on 'Source' side. ErrorCode=UserErrorKustoReadFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Read from Kusto failed with following error: Syntax error: Query could not be parsed: A recognition error occurred.."

Thanks!

Answer 1

Hello @GouKong and welcome to Microsoft Q&A.

I have seen something similar happen when trying to mix pipeline expression inside a SQL statment/query.

Could you try:

let timestring = todatetime(@{convertFromUtc(pipeline().TriggerTime,'GMT Standard Time','yyyy-MM-dd HH:mm:ss.fff')});

The difference is adding the curly braces { }. The curly braces serve two purposes.

1. They help Data Factory tell where to start and stop interpreting the content as an expression, as opposed to text to pass to Kusto
2. They convert the output of the contained expression to a string

Let us know whether this helps or not. If this solves your issue, please mark as accepted answer. If it does not help you, let us know, and we will look deeper into the issue.

Thank you for your patience.