ADF Xero Connector fails with dates prior to 1970

Stephan Hoffmann 6

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

ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2021-09-07T06:25:51.503+00:00

Hi @Stephan Hoffmann ,

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.
ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2021-09-10T16:20:54.893+00:00

Hi @Stephan Hoffmann ,

Following up to see you get chance to look above comment and share any updates?
David Cox 1 Reputation point

2021-09-13T10:54:23.05+00:00

Hi ShaikMaheer-MSFT,

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

Kind Regards
David
David Cox 1 Reputation point

2021-09-15T14:17:19.037+00:00

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
ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2021-09-16T06:56:31.693+00:00

Hi @David Cox ,

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.
Stephan Hoffmann 6 Reputation points

2021-09-21T05:20:57.893+00:00

Hi,

the problem is not at writing to the sink - the issue is at reading the source. The problem is that the Xero API returns the date as "/Date(-1559520000000+0000)/" - and here the "to integer" conversion seems to fail.

I'd really love to try your suggested solution. Can you tell me how I can tell the Xero connector in ADF to import a column as string? I understand how to do that on the sink side ... but it never reaches that...

Thanks a lot,
Stephan
ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2021-09-21T09:24:45.093+00:00

Hi @Stephan Hoffmann ,

Keeping your sink column type as string should be fine to take data as string from Source. Could you please try and see.
Stephan Hoffmann 6 Reputation points

2021-09-22T09:16:20.67+00:00

Can you please elaborate why a setting on the sink fixes a problem on the source? I dont get it ....

Leaving aside the fact that I need it as a date column....
David Cox 1 Reputation point

2021-09-22T09:19:56.363+00:00

Hi ShaikMaheer-MSFT,

I concur with Stephan's latest comment, my thought is that there is an issue with the Xero driver being used
Stephan Hoffmann 6 Reputation points

2021-09-22T09:32:50.313+00:00

Hi @ShaikMaheer-MSFT ,

so I went back and tried to "keep it as string".

My target is a storage account json file. On the mapping screen I have no way of setting the data type - it says "any"

Still, same error happens when I run it...
Stephan Hoffmann 6 Reputation points

2021-09-22T09:39:12.313+00:00
And finally to rule out the sink as being the error, the error message states as source

Source=XeroODBC_sb64.dll

Its failing in that library ... not on the sink side.
ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2021-09-22T11:42:37.717+00:00

Hi @Stephan Hoffmann ,

Thank you for being patience on this issue. I am checking this internally with product team and get back to you with findings.
Stephan Hoffmann 6 Reputation points

2021-09-30T05:55:22.763+00:00

Hi @ShaikMaheer-MSFT ,
do you have an update for me in this issue?

Thanks,
Stephan
ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2021-09-30T06:38:15.703+00:00

Hi @Stephan Hoffmann ,

Thank you for being patience. For immediate assistance on this issue, if you have a support plan you may file a support ticket.
Stephan Hoffmann 6 Reputation points

2021-09-30T08:19:58.723+00:00

Ok, done that.

Thanks for your assistance.

Share via

ADF Xero Connector fails with dates prior to 1970