ADF Xero Connector fails with dates prior to 1970

Stephan Hoffmann 6 Reputation points
2021-09-03T09:27:40.843+00:00

We want to integrate ADF with Xero. When we copy data from the Journals table we receive this error

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Support] (50090) Conversion from string to number failed with value '',Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Support] (50090) Conversion from string to number failed with value '',Source=XeroODBC_sb64.dll,'

After a lot of digging around we found the problem being a journal that has a date of <JournalDate>1920-08-01T00:00:00</JournalDate>, which in JSON comes back as "JournalDate": "/Date(-1559520000000+0000)/". And its this record where the copy activity fails.

I cannot change this record in Xero (an update just adds a correcting journal, but doesnt update this one), and I cannot copy the data because of this.

Is there anything I can do? Like can I tell the connector to use XML format? That seems to use a sensible notation...

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
7,187 questions
{count} votes