Xero Accounting Software Integration and Synchronization

Xero accounting software data can be integrated and synchronized codeless with various other data sources using the Layer2 Cloud Connector Enterprise Edition via our Xero data provider. Please take a look here for systems and applications to connect, like SQL databases, Microsoft Office 365, SharePoint, Exchange, Dynamics and many more.

Xero is a popular accounting software that serves more than 475.000 small businesses to get a real-time view into their cashflow at any time from any device.

Xero data can be integrated und synchronized codeless with many systems and applications on-premises (locally) or in other clouds using the Layer2 Cloud Connector.

Benefits of Xero Data Integration and Synchronization

Integrating Xero via the the Layer2 Cloud Connector has the following features and benefits:

• Many systems and apps can be connected: Often requested data providers like ODBC, OLEDB, OData, XML etc. are included in the distribution package, vendor specific ADO.NET data providers like for SQL Server, Oracle, mySQL etc. can be used, also 3rd party data providers. On-premises and cloud-based systems and apps are supported. Please find your specific data source or destination here.
• Fast and risk-free codeless implementation: No programming or tools required. Setup in a few minutes guided by a wizard. Sample connection templates included. Just adapt your connection string and data query. 
• No touch integration: Does not require any customization, changes or installation on FreshBooks Xero or the external data source side, e.g. SQL or SharePoint.
• Flexible sync options: Depending on data provider, one-way or two-way sync, insert/update/delete operations can be blocked, if required. Scheduled update in background, or on-demand only.
• Reliable architecture: A connection is established only for data synchronization – not for general Xero usage.
• All Xero features provided: As external data is directly cached in Xero, all the great Xero features are provided as usual.
• Best performance: Fast synchronization, supports large datasets. No bulk-import - data changes are processed carefully. Users directly work with cached data sets in Xero for best user experience.
• Highest Security: No need to open your local network for access from outside. Secure communication, e.g. SSL/HTTPS (depending on data provider). No 3rd party or clouds involved. The Layer2 Cloud Connector runs as a Windows service on a Windows server or client in your secure internal network and directly connects both ends for sync.

Xero Specific Data Integration Settings

​To connect to any Xero data entity or query the data source must be configured as follows in the Layer2 Cloud Connector Connection Manager:

Fig.: Sample connection configuration to connect to a Xero invoices list.

Please note the following Xero specific settings.

• Choose the “Xero” as the Data Provider for your data entity (source and / or destination).
• Make use of a connection string like this: XeroAppAuthentication=PRIVATE; OAuthAccessToken=MyConsumerKey; OAuthAccessTokenSecret=MyConsumerSecret; OAuthClientId= MyConsumerKey; OAuthClientSecret= MyConsumerSecret; CertificateStoreType=PFXFILE; CertificateStore=C:\MyFolder\MyKeyFile.pfx; CertificateSubject=MyCertificateSubject; CertificateStorePassword=MyCertificatePassword;
• Add a data query and choose one of the available entities. Accounts, BankTransactions, BankTransfers, Contacts, CreditNotes, Employees, ExpenseClaims, Invoices, Items, ManualJournals, Payments, Receipts, TaxRates and more are supported. Example: Select * from Invoices
• If you are using this provider as a data source in an uni-directional connection, choosing a primary key is not required as the provider sets one automatically.

Step-by-Step Intros For Xero Integration And Synchronization

​Just some ideas what to connect next with Xero data queries: 

• Sync with 3rd party tools like ERP/CRM/CMS based on SQL databases like Oracle, mySQL or Microsoft SQL Server.
• Sync with Microsoft SharePoint or Office 365 for better collaboration (for example with customers or partners) or for intranet publishing. Search, change notifications and workflows fully supported.
• Integrate with Microsoft Dynamics CRM/ERP/NAV etc.
• Archive Xero data in a local database for custom reporting / analysis.
• You will find more systems to connect here.

Xero Data Integration - Known Issues and Workarounds

• If you want to set this up for automated synchronization, then you have to configure the ADO.NET provider for private access to Xero via SSL certificate. Public/Partner access needs manual re-authorization of the OAuth credentials every 30 minutes.
• There are Xero API usage limitations that may be encountered while using the for Xero. There is a daily limit of 1000 API calls against a single Xero organisation in a rolling 24-hour period. In addition to the daily limit, a single access token can only be used up to 60 times in a rolling 60-second period. There is also a Rate Limit. If you encounter a rate limit, the Xero API will return an HTTP 503 (Service Unavailable) error, with the following message: "oauth_problem=rate limit exceeded".
  Note: If you encounter a rate limit, do not continue to make requests, as this may continue to add to your limitation. If necessary, you may need to queue requests. When working with the provider, some operations may result in multiple requests to the API. For example, updating an existing record will result in two requests: one to get the current record, and one to submit changes.
• Writing to Xero (selection Xero as target) is still not supported.

OAuthClientID=<your_client_id>;
OAuthClientSecret=<your_client_secret>;
InitiateOAuth=GETANDREFRESH;

Select Statement:

SELECT * FROM Messages WHERE ChannelID=’CBZVAAE6L’ AND SearchTerm Like ‘’;

You can get all available tables with:  

SELECT * FROM sys_tables;

You can get all available views with:  

SELECT * FROM sys_views;

Ready to go next steps?