question

StephanHoffmann-0377 avatar image
0 Votes"
StephanHoffmann-0377 asked ShaikMaheer-MSFT commented

ADF Xero Connector fails with dates prior to 1970

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
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @StephanHoffmann-0377 ,

Welcome to Microsoft Q&A Platform. Thank you for posting query here.

From the error message, it seems some column values which is string type in Xero getting converting in to int type based on Sink type and some row for that column has a value which cannot be converted as int because its not actual integer value.

Between, you are saying <JournalDate>1920-08-01T00:00:00</JournalDate> is creating issue and you cannot update that in Xero, So best way is try to load that data as string in to Sink. When you use string type column from Sink for that data you wont get error.

Click here to know more above Xero connector in Azure data factory.

Please let me know if this helpful. Feel free to let us know if any further queries.


0 Votes 0 ·

Hi @StephanHoffmann-0377 ,

Following up to see you get chance to look above comment and share any updates?

0 Votes 0 ·

Hi ShaikMaheer-MSFT,

The error is at the source not the sink, therefore changing the data type has no effect

Kind Regards
David

0 Votes 0 ·

Hi ShaikMaheer-MSFT,

Is there any update on this? Your original response does not offer a solution. Changing the type to "string" in the source and sink, and only sink still returns the same error

Kind Regards
David

0 Votes 0 ·

Hi @DavidCox-5279 ,

Thank you for your ask. Looks strange to me because usually when you have source and sink both string then possibility of error is very less.

Could you please post it as new question with complete details on error message and configurations? Thank you.

0 Votes 0 ·

0 Answers