Xero Azure Data Factory connector operates successfully for 30 minutes, then fails

Sebastian 41 Reputation points
2020-09-30T10:58:53.497+00:00

Hi,
The ADF Data Factory Xero connector runs successfully as a linked service for approx. 30 minutes, but then starts failing.

I am using the OAuth 2.0. config, and suspect it is to do with the refreshing of the token. Has anyone successfully used the ADF Xero connector with OAuth 2? If so, can you please provide some instruction on how?

I have followed the instructions at Xero for configuring OAuth 2 (links below), can confirm offline_access is scoped, and am obtaining the refresh token initially via Postman. I can also confirm that I am following the guidance at MS Docs (link below also).

Of particular interest is the statement at the MS Docs ADF Xero Connector page:

"
refreshToken | The OAuth 2.0 refresh token associated with the Xero application, used to refresh the access token when access token expires. Applicable for OAuth 2.0 authentication. Learn how to get the refresh token from this article.Refresh token will never expired. To get a refresh token, you must request the offline_access scope.Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault.
"
The statement "Refresh token will never expired" appears to be incorrect, and at 30 minutes my token can no longer be utilised.

Example error is as follows: 

{  
"errorCode": "2200",  
"message": "ErrorCode=UserErrorFailedToConnectOdbcSource,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400\r\nERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400\r\nERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400,Source=,'",  
"failureType": "UserError",  
"target": "Copy Xero Projects",  
"details": []  
  
Xero OAuth 2.0: https://developer.xero.com/documentation/oauth2/auth-flow#:~:text=Refreshing%20access%20tokens&text=Your%20app%20can%20refresh%20an,during%20the%20initial%20user%20authorization.&text=Each%20time%20you%20perform%20a,token%20returned%20in%20the%20response.

Data Factory Xero Connector: https://learn.microsoft.com/en-us/azure/data-factory/connector-xero

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,652 questions
Accepted answer
  1. Katy Nelson 111 Reputation points
    2020-10-14T08:31:34.063+00:00

    I've found a workaround:

    1. get the refresh key from a vault (you have to load it manually on the first run)
    2. a. Call the Xero API with an identity to get the next refresh key. Then store this key in the vault for the next run b. Call the Xero API to extract the data with copy data

    thanks katy

    4 people found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sebastian 41 Reputation points
    2020-10-27T10:46:15.337+00:00

    Hi @Anonymous , I can confirm this approach works. Thanks @Katy Nelson for the push in the right direction.

    Zandre, getting the refresh token via Postman was pretty straight forward (https://developer.xero.com/documentation/tools/postman).

    2 people found this answer helpful.
  2. Alan W 1 Reputation point
    2022-06-09T06:14:42.007+00:00

    Hi @Katy Nelson

    Thanks for this post, I have run into the same problem, your response has got me a long way. I have a question about the step in your pipeline, WebPostRefreshKey. Are you saving the new refresh token back into the Azure Key Vault, it does not seem to be an option to update an existing value, only to create a new version. Can you please expand on what is being done in this web component?

    Many thanks for your help

    Regards
    Alan

    0 comments
  3. maha kepakisan 1 Reputation point
    2022-06-09T06:28:44.727+00:00

    Hey Alan,

    I'm not Katy, but hope this help mate. That's right, you can only create a new version, but on the pipeline you can set your web call to only read the latest version of keyVault secret. It does mean you can end-up with a bunch of versions of the keyVault secret.

    Cheers