How to read/load data in Xero using SSIS

Introduction

Xero is well known Cloud based Accounting Software. In this post you will learn how to implement Xero API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using SSIS in few clicks. We will use SSIS JSON Source Connector to Read data from Xero and Load into SQL Server / other target (Using OAuth Connection). We will also discuss reverse scenario to Write data to Xero (API POST for Insert or Update in Xero) using SSIS Web API Destination and SSIS JSON Generator Transform

Prerequisites

Before we look into Step-By-Step section to extract and load data from Xero to SQL Server let’s make sure you met following requirements.

1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
3. ZappySys SSIS PowerPack installed. Click on the link to download FREE trial.
4. You have basic familiarity with REST API concepts and Xero API.

Concepts of Xero REST API

API Authorization

Before you can access Xero API you must register account and App. There are 3 types of Apps to access Xero API.

1. Private App (X509 Certificate used to sign requests)
2. Public App (Use 3-legged OAuth. Token expires in 30 mins and then you have to authorize again from UI)
3. Partner App (Use 3-legged OAuth. Token expires in 30 mins.. but refresh token supported to get new token for unlimited times without Authorization via Login UI)
4. OAuth2.0 Authentication (Recommended) 

In this article we will use Private App to access your company data in Xero.

Data formats

Xero API supports Xml and JSON both formats over the same URL (endpoints). If you want data in JSON format then you have to pass following Header (else data in Xml format will be returned). If you are using SSIS JSON Source Connector then make sure following header is added. For SSIS XML Source no need to add this header.

Step-By-Step : Xero API Integration in SSIS

Now lets look at steps needed to fetch data from Xero and load into SQL Server.

Configure OAuth2.0 Connection for Xero API (Recommended)

So let’s how to create the Xero OAuth2.0 connection. For more on OAuth2.0 refer to this article.

1. Right-click inside the Connection Managers area again and click “New Connection…”
2. From the connection type list select “ZS-OAUTH” connection type.
   

   Create new SSIS OAuth API Connection Manager

3. Select OAuth Provider which Custom, enter the CleintID and Secret and set the URLs as below and desired scopes that are required for the API Call.
   Auth URL:
   
   https://login.xero.com/identity/connect/authorize

   1	https://login.xero.com/identity/connect/authorize

   Token URL:
   
   https://identity.xero.com/connect/token

   1	https://identity.xero.com/connect/token

   Scopes: Add your desired scopes based on your API call requirements.
   
   Note: To get a refresh token, you must request the offline_access scope. A refresh token allows you to refresh your access token and maintain an offline connection.
   offline_access

   https://developer.xero.com/documentation/guides/oauth2/scopes/#offline-access

   

   OAuth2.0 Connection

4.  Now go to the Advanced tab and set the redirect URL as below and also you need to set the same URL in the Xero API App.
   
   https://zappysys.com/oauth2

   1	https://zappysys.com/oauth2

   

   Set Callback/Return Url in OAuth Connection

5. Now Click on the Generate Token Button and logged in with Xero username and password and grant permission.
6. That’s it your token is generated. If you got the blank Screen like this after following all the steps. Close that window.
   

   OAuth2.0 connection : Blank Window at last

7. And it will ask you to use the Different Method to generate the Token using system default browser click on YES.
   

   Different Method to generate the Token using system default browser

8. It will open the URL in the browser there you need to log in and grant the permission.
9. After that, you will able to view the access token in the browser. you can copy from there also.
   

   Generated Token

10. At the last, it will again focus on the connection and there it will ask you to save the token if you want to store it. if not then you can manually copy and paste that token.
    

    OAuth Connection : Store the Tokens

11. That’s it now click on the Test Connection button to test it.
    

    Generate Token and Test Connection

Register OAuth App Private for Xero (Private App) OAuth Connection (Depreciated)

Here is how to configure Xero to use Private App (click on below link).

https://developer.xero.com/documentation/auth-and-limits/private-applications

In the above link during these steps openssl command may throw error about openssl.cnf is missing or cannot load. If you face such error then don’t worry just refer to following workaround.

https://stackoverflow.com/questions/14459078/unable-to-load-config-info-from-usr-local-ssl-openssl-cnf

Basically you can either supply config file path in each command OR set path by issuing SET command before any other command. See below examples (Assuming you installed OpenSSL to c:\OpenSSL-Win64 folder)

— OR —  type before command before any other openssl commands

Once you generate public key, private key and, pfx file we are ready to call Xero API in SSIS

Configure Xero (Private App) OAuth Connection

Once you generate certificate using openssl and register Private App for Xero we are ready to move to SSIS piece. Lets see how to do that.

1. Open Visual Studio
2. Open existing SSIS Project or create new using File > New > Project > Choose “Integration Services Project” Type under Business Intelligence template category
3. Open Package
4. Right click inside Connection Managers area and click “New Connection…”
5. From the connection type list select “ZS-OAUTH” connection type.

   

   Create new SSIS OAuth API Connection Manager

6. On the connection select “Xero (Private App)” from Service Provider Dropdown
7. Enter Client ID (Obtain this from App created in Previous section) App Selection leave “Use Default OAuth App” selected. If you created custom OAuth App then select “Use Custom OAuth App” Option
8. Enter some fake value in Client Secret (not needed when you use Private App). Secret will come from certificate file like below.
   

   SSIS OAuth Connection – Configure Xero API Connectivity using Private App (Certificate File Approach)

9. Now go to Certificate tab and Check Use Certificate Enter, Enter certificate location and password like below.
   

   SSIS OAuth Connection – Configure X509 Certificate File Option for Xero Private App (Select PFX file)

10. Click OK to save OAuth connection manager

So in the next section from the upper connections, we need to use any of the one connection only for Xero API now let’s try to read data from Xero using SSIS JSON Source.

Xero recently added OAuth 2.0 support so that now recommended way to use as Connection.

Configure JSON / REST API Source – Read data from Xero

Now let’s look at how to configure SSIS JSON Source to read data from Xero API

1. In the control flow ssis toolbox Drag and drop data flow task
2. Double click Data flow
3. Drag ZS JSON Source (REST API or File) from SSIS Toolbox and drop on Data flow designer
4. Double click ZS JSON Source to configure it.
5. Set the following properties
   1. Enter URL
      
      https://api.xero.com/api.xro/2.0/Invoices

      1	https://api.xero.com/api.xro/2.0/Invoices

   2. Check Use Credentials and select OAuth connection we created in the previous section
   3. For Headers grid. Click on Raw Edit button and remove enter following (remove default headers)
      
      Accept: application/json

      1	Accept: application/json

   4. Click on Select filter and select an array node as below to generate filter expression (e.g. $.Invoices[*] )

Configure SSIS JSON /REST API Source – Read Xero Invoices

Also, add following header in case of multiple tenants are being used in XERO environment –

Configure Xero REST API Pagination in SSIS JSON Source

By default, Xero may not send you all records and you may have to setup pagination to read more data after the first response.

For example, you like to read LineItems from Invoices. For that make sure you use below URL and include page=1 to start with. If you do not include page=1 then Line items are not returned (Yes its ODD but clearly documented here).

1. Enter URL as
   
   https://api.xero.com/api.xro/2.0/Invoices?page=1

   1	https://api.xero.com/api.xro/2.0/Invoices?page=1

2. Select Filter as $.Invoices[*].LineItems[*]
3. Go to Pagination tab
4. Select Pagination by URL Parameter
5. Enter page as pagination parameter name like below
   

   REST API Looping/Pagination via URL Page Number Parameter (Loop until last page detected)

6. Click OK to save

Now when you run package you will see all LineItems for each Invoice will be returned.

Loading Xero data to SQL Server

Now last step is loading Xero data to SQL Server. For example purpose we will load data into SQL Server but you can load into any Target (e.g. Flat file, Oracle, Excel) using Microsoft or ZappySys Destination connectors

To load Xero data into SQL Server perform following steps

1. Drag OLEDB destination on Data Flow surface
2. Connect ZS JSON Source to Destination
3. On OLEDB destination select / create new SQL Connection and then Click “New Table”
4. Click on Mappings tab and click OK to save
5. Execute Package

Configure SSIS OLEDB Destination – Loading Xero Data into SQL Server Table

Xero to SQL Server Column Mappings for OLEDB Destination

Loading Xero data to SQL Server in SSIS

POST data to Xero (Insert or Update)

So far we have seen how to read data from Xero. Now let’s look at how to write data to Xero.

There are two ways you can achieve this.

1. Use Web API Destination in Data Flow
2. Use REST API Task in Control Flow

Read this article to learn more about API POST using SSIS

Using SSIS Web API Task (Write data from SQL Server to Xero)

Using REST API Task to POST data to Xero

If you have JSON/XML data already prepared and if you like to POST it to Xero API then REST API Task would be easy to use.

REST API Task Body can be direct string, variable (e.g. {{User::varSomeData}} or Body can come from File 

SSIS REST API Task – POST data to Xero (Create contacts)

Read data from Xero Report API

In previous examples we saw how to read data from various Xero API endpoints but now lets look at how to call Xero Report API. For example if you like to extract data displayed in below then you can extract it in Xml or JSON format. Below report is in HTML format but we will show you how to get similar information in JSON format and load into SQL Server Table.

Xero Report Example – View Profit and Loss Report in Xero Portal

To extract above information you can call below API Endpoint (e.g. Profit & Loss Report)

You can use Variables to make above URL dynamic in JSON Source as below

Each report has its own API URL so make sure you refer to the connect URL for calling different report.

Here is sample response from API

Now lets look at steps to configure JSON Source to extract your Xero Report Data.

1. Drag new JSON Source from SSIS Toolbox
2. Double click JSON Source to configure it
3. In the Path / Web URL, enter API Report URL to call desired Report Start / End Date or any other expected Parameter by report (Refer to API Documentation). For our example we will enter below URL
   
   https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2017-02-01&toDate=2017-02-28

   1	https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2017-02-01&toDate=2017-02-28

4. Check Use Credentials and Select Connection we created in the Previous Section
5. In the Filter enter $.Reports[*].Rows[*].Rows[*]
6. Now go to 2D Array Transform Tab and configure like below. If your UI varies then use Properties window to edit property manually.
   Basically you need to edit
   Transformation Type: 2-dimensional array Column Name Filter: $.Reports[0].Rows[*].Cells[*].Value Row Values Filter: $.Cells[*].Value
7. Now Click Preview Data. If you get error Column ” does not belong to table nosqldata. Then most likely you using old version with Known Issue (i.e. v2.9.7.10822 or older). Get latest version for fix. For workaround of this issue you must follow next section to specify columns manually. Columns must match count.

Here is how it will look like.

Configure Transform Options for Xero Report API (Dynamic Column Title)

Read from Xero Report API using JSON Source in SSIS (Dynamic Columns Example)

Fixing Dynamic Columns Problem in for Xero Report API

Now let’s look at one common scenario which you have to think. SSIS data flow has fixed metadata so if your Report is generating Dynamic Columns each time based on Date Range then it will be trouble for you because all your columns may come as null if initial date range changes.

To address this issue you have to use Static Column Names as below.

NOTE: Use REST API Task to obtain Raw JSON and review First Row which contains Number of columns
For example if your API return some thing like below then you can use 5 columns (one Blank header + 4 Months)

Once we know how many columns we should expect from API response, we can perform following steps. We will now configure our JSON Source component to use Static Columns.

1. Drag new JSON Source from SSIS Toolbox
2. Right click JSON Source > Click on Properties (You can do below using UI too but in old version no option for that – See below this section)
3. Enter following values for property
   1. Filter:  $.Reports[*].Rows[*].Rows[*]
   2. ArrayTransformationType: TransformComplexTwoDimensionalArray
   3. ArrayTransCustomColumns: Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13    (Assuming you have 12 months to compare)
   4. ArrayEnableCustomColumn: True
   5. ArrayTransRowValueFilter: $.Cells[*].Value
4. Now double click JSON Source to configure
5. In the Path / Web URL, enter API Report URL to call desired Report Start / End Date or any other expected Parameter by report (Refer to API Documentation). For our example we will enter below URL
   
   https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2017-02-01&toDate=2017-02-28

   1	https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2017-02-01&toDate=2017-02-28

6. Check Use Credentials and Select Connection we created in the Previous Section
7. In the Filter enter $.Reports[*].Rows[*].Rows[*]
8. Now click Preview.

For Old Version use Properties Grid

Configure Xero Report Data Extract using SSIS Property Grid (For Older Version)

For New Version Use UI (It has Specify Columns Manually Option)

Read from Xero Report API using parameters in JSON Source in SSIS (Static Columns Example)

Common Errors

Deployment to Production

Conclusion. What’s next?

In this article we have learned how to load data from Xero to SQL Server using SSIS ( drag and drop approach without coding). We used SSIS JSON / REST API Connector to extract data from Xero REST API using OAuth. JSON Source Connector makes it super simple to parsing complex / large JSON Files or any Web API Response into rows and column so you can load into database like SQL Server. Download SSIS PowerPack to try many other automation scenarios not discussed in this article.

Keywords:

Xero Integration with SQL Server | How to extract data from Xero in SSIS? | How to read data from Xero API? | Loading Xero Data into SQL Server. | Xero to SQL Server | SQL Server to Xero | SSIS Xero Integration