I have a simple pipeline where I look up a date from an Azure SQL DB table, and then try pass this to an XML query for Dynamics.
The lookup activity succeeds, output looks fine, I get an error message for the XML query which I have been trying to resolve for a long time without any success so far.
This is the error message I get
And this is the XML query I am using in the source query:
<fetch>
<entity name="account" >
<all-attributes/>
<filter type="and">
<condition attribute="modifiedon" operator="on-or-after" value="@{activity('LookupAccountSQL').output.firstRow.modate}" />
</filter>
</entity>
The lookup activity name is LookupAccountSQL, and the SQL query is simple as below: (also debug runs successfully for this activity):
SELECT CAST(MAX(modifiedon)) as modate FROM dbo.account
Any help is greatly appreciated!
Thanks to the helpful comments below my question I figured out the problem. The lookup activity returns a datetime regardless of the fact that I use cast in the SQL query.
The solution is to put a formatDateTime() function on top in the Fetchxml.
<fetch>
<entity name="account" >
<all-attributes/>
<filter type="and">
<condition attribute="modifiedon" operator="on-or-after" value="@{formatDateTime(activity('LookupAccountSQL').output.firstRow.modate,'yyyy-MM-dd')}" />
</filter>
</entity>