Pipeline REST filter query to Xero

New Contributor

Hi there, I am trying to import data from Xero using a pipeline and do so fine when I do not filter the REST request. But when I try to add a standard filter to the REST call, I receive the below error from Data Factory.

 

Message=Column 'Date' contains an invalid value '/Date(1669680000000+0000)/'. Cannot convert '/Date(1669680000000+0000)/' to type 'DateTime'

 

Here is my REST query / parameters:

https://api.xero.com/api.xro/2.0/Invoices?where=Date >= DateTime(2022,11,11)

 

Now I know this is an issue with Data Factory as if I replicate the REST call with POSTMAN or the Xero API Explorer it works perfectly and returns items.

 

So, my question is, what exactly is Data Factory doing with the request to receive this error? As the call is correct (validated by Xero) but Data factory is somehow breaking the request. 

 

Thanks,

Tucker

2 Replies

@tuck411 

 

It seems as though Data Factory keeps trying to convert the DateTime string I am passing to Xero in the REST query. I've tried to convert it into a string, or somehow use a string literal, but Data Factory keeps trying to convert the DateTime into a function instead of passing it as a string to Xero. Any help would be massively appreciated.

 

Thanks

@tuck411, if using a Mapping Data Flow, can you try to set the Base URL of the REST call to be parameterized based on a variable which represents the date value? See "Example 1: Variables in QueryParameters" within this documentation.

 

Something like "https://api.xero.com/api.xro/2.0/Invoices?where=Date >= {varInvoiceDate}" where varInvoiceDate is set as a string variable "DateTime(yyyy, mm, dd)".