Dataflow parameter used in adf query option

Question

Pls advice. I need to use dataflow query option where I have fetchxml in which filter needs to pass a dataflow parameter as value from main pipeline,

here is sample code :

<fetch>  
<entity name="customer">  
<attribute name="name" />  
<attribute name="city" />  

<filter type="and">  
<condition attribute="modifiedon" operator="on-or-after" value="',  

[Data flow parameter]

'" />  
</filter> 

</entity>  
</fetch>

Answer 1

Hi N2120,
Great job. Thank you for sharing your resolution details here. Since, you cannot accept your own answer. I am adding your resolution details here. Kindly consider hitting Accept Answer button. Accepted answers help community as well.

found the solution by passing the parameter in query option of dataflow using dynamic sql option inside query
using the following expression with a dataflow parameter date passed from outer pipelne concat('<fetch> <entity name="customer"> <attribute name="name" /><attribute name="city" /> <filter type="or"> <condition attribute="modifiedon" operator="gt" value="', $date, '" /> </filter> </entity> </fetch>') and this worked

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

Answer 2

Hi N2120,

Thank you for posting query in Microsoft Q&A Platform.

The date-time format for @{activity('customer').output.parameters.date} is invalid, or value is outside the supported range."}}) As per above error, it seems that value what you are getting in the @{activity('customer').output.parameters.date} is not in the expected format. Let's assume as per your query syntax you are expecting date value in YYYY-MM-dd format, but if you are getting a different format then above error will come.
Kindly consider using formatDateTime() function to format the value and use in the fetch XML query accordingly.

Please consider checking below link to know about formatDateTime() function.

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#formatdatetime

Hope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

Answer 3

I can see that there is a syntax problem in your FetchXML, try the following :

<fetch>  

  <entity name="customer">  

    <attribute name="name" />  

    <attribute name="city" />  

    <filter type="and">  

      <condition attribute="modifiedon" operator="on-or-after" value="@{activity('YourDataFlowActivityName').output.parameters.YourParameterName}" />  

    </filter> 

  </entity>  

</fetch>