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...