Copy data from Xero using Azure Data Factory or Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article outlines how to use the Copy Activity in an Azure Data Factory or Synapse Analytics pipeline to copy data from Xero. It builds on the copy activity overview article that presents a general overview of copy activity.

Supported capabilities

This Xero connector is supported for the following capabilities:

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that are supported as sources/sinks, see the Supported data stores table.

Specifically, this Xero connector supports:

• OAuth 2.0 authentication.
• All Xero tables (API endpoints) except "Reports".
• Windows versions in this article.

Getting started

To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

• The Copy Data tool
• The Azure portal
• The .NET SDK
• The Python SDK
• Azure PowerShell
• The REST API
• The Azure Resource Manager template

Create a linked service to Xero using UI

Use the following steps to create a linked service to Xero in the Azure portal UI.

1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:

   • Azure Data Factory
   • Azure Synapse

   

2. Search for Xero and select the Xero connector.

   

3. Configure the service details, test the connection, and create the new linked service.

   

Connector configuration details

The following sections provide details about properties that are used to define Data Factory entities specific to Xero connector.

Linked service properties

The following properties are supported for Xero linked service:

Example: OAuth 2.0 authentication

{
    "name": "XeroLinkedService",
    "properties": {
        "type": "Xero",
        "typeProperties": {
            "connectionProperties": { 
                "host": "api.xero.com",
                "authenticationType":"OAuth_2.0", 
                "consumerKey": {
                    "type": "SecureString",
                    "value": "<client ID>"
                },
                "privateKey": {
                    "type": "SecureString",
                    "value": "<client secret>"
                },
                "tenantId": "<tenant ID>", 
                "refreshToken": {
                    "type": "SecureString",
                    "value": "<refresh token>"
                }, 
                "useEncryptedEndpoints": true, 
                "useHostVerification": true, 
                "usePeerVerification": true
            }            
        }
    }
}

Example: OAuth 1.0 authentication

{
    "name": "XeroLinkedService",
    "properties": {
        "type": "Xero",
        "typeProperties": {
            "connectionProperties": {
                "host": "api.xero.com", 
                "authenticationType":"OAuth_1.0", 
                "consumerKey": {
                    "type": "SecureString",
                    "value": "<consumer key>"
                },
                "privateKey": {
                    "type": "SecureString",
                    "value": "<private key>"
                }, 
                "useEncryptedEndpoints": true,
                "useHostVerification": true,
                "usePeerVerification": true
            }
        }
    }
}

Sample private key value:

Include all the text from the .pem file including the Unix line endings(\n).

"-----BEGIN RSA PRIVATE KEY-----\nMII***************************************************P\nbu****************************************************s\nU/****************************************************B\nA*****************************************************W\njH****************************************************e\nsx*****************************************************l\nq******************************************************X\nh*****************************************************i\nd*****************************************************s\nA*****************************************************dsfb\nN*****************************************************M\np*****************************************************Ly\nK*****************************************************Y=\n-----END RSA PRIVATE KEY-----"

Dataset properties

For a full list of sections and properties available for defining datasets, see the datasets article. This section provides a list of properties supported by Xero dataset.

To copy data from Xero, set the type property of the dataset to XeroObject. The following properties are supported:

Example

{
    "name": "XeroDataset",
    "properties": {
        "type": "XeroObject",
        "typeProperties": {},
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<Xero linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by Xero source.

Xero as source

To copy data from Xero, set the source type in the copy activity to XeroSource. The following properties are supported in the copy activity source section:

Example:

"activities":[
    {
        "name": "CopyFromXero",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Xero input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "XeroSource",
                "query": "SELECT * FROM Contacts"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Note the following when specifying the Xero query:

• Tables with complex items will be split to multiple tables. For example, Bank transactions has a complex data structure "LineItems", so data of bank transaction is mapped to table Bank_Transaction and Bank_Transaction_Line_Items, with Bank_Transaction_ID as foreign key to link them together.

• Xero data is available through two schemas: Minimal (default) and Complete. The Complete schema contains prerequisite call tables which require additional data (e.g. ID column) before making the desired query.

The following tables have the same information in the Minimal and Complete schema. To reduce the number of API calls, use Minimal schema (default).

• Bank_Transactions
• Contact_Groups
• Contacts
• Contacts_Sales_Tracking_Categories
• Contacts_Phones
• Contacts_Addresses
• Contacts_Purchases_Tracking_Categories
• Credit_Notes
• Credit_Notes_Allocations
• Expense_Claims
• Expense_Claim_Validation_Errors
• Invoices
• Invoices_Credit_Notes
• Invoices_ Prepayments
• Invoices_Overpayments
• Manual_Journals
• Overpayments
• Overpayments_Allocations
• Prepayments
• Prepayments_Allocations
• Receipts
• Receipt_Validation_Errors
• Tracking_Categories

The following tables can only be queried with complete schema:

• Complete.Bank_Transaction_Line_Items
• Complete.Bank_Transaction_Line_Item_Tracking
• Complete.Contact_Group_Contacts
• Complete.Contacts_Contact_ Persons
• Complete.Credit_Note_Line_Items
• Complete.Credit_Notes_Line_Items_Tracking
• Complete.Expense_Claim_ Payments
• Complete.Expense_Claim_Receipts
• Complete.Invoice_Line_Items
• Complete.Invoices_Line_Items_Tracking
• Complete.Manual_Journal_Lines
• Complete.Manual_Journal_Line_Tracking
• Complete.Overpayment_Line_Items
• Complete.Overpayment_Line_Items_Tracking
• Complete.Prepayment_Line_Items
• Complete.Prepayment_Line_Item_Tracking
• Complete.Receipt_Line_Items
• Complete.Receipt_Line_Item_Tracking
• Complete.Tracking_Category_Options

Lookup activity properties

To learn details about the properties, check Lookup activity.

Related content

For a list of supported data stores by the copy activity, see supported data stores.