Load data from Exact Online into SQL Server using SSIS

Introduction

In this article, you will learn how to load data from Exact Online into SQL Server using SSIS. Exact Online is a popular CRM, ERP and HRM tool which also lets you manage your finances. As an example, we will use Exact Online contacts as data to load it into a SQL Server database. We will use ZappySys SSIS PowerPack connector to get contacts from Exact Online and then use standard SSIS connector OLE DB Destination to load data into a table. This SSIS PowerPack connector will be used to get the data:

JSON Source

Step-by-step – Load data from Exact Online into SQL Server using SSIS

Getting started

We will get these contacts and load them into a SQL Server database:

Loading Exact Online contacts into SQL Server

The first thing you will need to do is to create an OAuth application in Exact App Center and then we will proceed in getting the data and loading it into a SQL Server database.

Create OAuth application in Exact App Center

1. Visit Exact App Center and login:
   

   Exact App Center login window

2. Once logged in, click on Manage my apps link:
   

   Exact App Center login area

3. Create a new OAuth application:
   

   Exact Online API applications management page

4. Give it a name.
5. In Redirect URI field enter: https://www.zappysyscom/oauth2
   
   

   Registering Exact Online API application to load data into SQL Server

6. Hit Register.
7. Don’t close the browser, we will use App information later.

Create OAuth Connection Manager to connect to Exact Online

1. Create a new SSIS package.
2. Create a new OAuth Connection Manager, by right-clicking on the Connection Managers pane:
   

   Creating OAuth Connection Manager to connect to Exact Online API application

3. Select ZS-OAUTH to create one:
   

   Selecting ZS-OAUTH option to create OAuth Connection Manager

4. Then open your Exact App Center API application configuration we left open in the previous step. We will use that data from that window.
5. Select Custom as OAuth Provider.
6. Select Authorization Code Grant as OAuth Grant Type.
7. Copy Client ID and paste it into Client Id field.
8. Copy Client secret and paste it into Client Secret field.
9. Set Authorization Url to https://start.exactonline.nl/api/oauth2/auth.
10. Set Access Token Url to https://start.exactonline.nl/api/oauth2/token.
    
    

    Configuring OAuth Connection Manager to access Exact Online data via API

11. Then open Advanced tab and copy paste Redirect URI to Callback/Return Url field:
    

    Configuring OAuth application Callback/Return URL

12. Then click on OAuth2 Grant Options tab and check option below.
13. Finally, since Exact Online refresh token keeps changing, enter a filename where to store the refresh token:
    

    Configuring OAuth2 Grant specifics

    NOTE: Can be a non-existent file or must be an empty, existing file.

Getting data from Exact Online

1. Drag and drop Data Flow and then JSON Source into it:
   

   Dragging and dropping Data Flow Task into Control Flow

2. Double click on JSON Source to modify it.
3. Check Use Credentials checkbox and select OAuth 1 as your Connection Manager.
4. Add HTTP Header named Accept and set the value to application/json.
5. In Array Filter field enter $.d.results[*].
6. In Enter Path or Web URL field enter this URL:
   https://start.exactonline.nl/api/v1/current/Me?$select=CurrentDivision
7. Hit Preview button.
8. Copy your division number, you will need it when constructing a URL for getting contacts.
   

   Getting division number

9. Now enter this URL in the field:
   https://start.exactonline.nl/api/v1/{division}/crm/Contacts?$select=FirstName,LastName
   Replace {division} with your division number.

Configuring JSON Source to get Exact Online data and load it into SQL Server

Pagination

To paginate through the results, perform these steps:

1. Open Pagination tab.
2. In Next Link/Cursor Expression field enter $.d.__next

Configuring pagination in JSON Source to paginate through Exact Online contacts

Loading Exact Online data into SQL Server

1. Drag and drop OLE DB Destination or similar connector.
2. Connect JSON Source with OLE DB Destination.
3. Create a new connection.
4. Click New to create a new table.
5. Hit OK.
6. Select Mappings to map the columns.
7. Hit OK to close the window.
8. Execute the package.

Configuring OLE DB Destination to load Exact Online data into SQL Server

The results

Once you execute the package, you should see similar results in Visual Studio and SQL Server Management Studio:

Executing SSIS package and loading Exact Online data  into SQL Server

Successfully loading Exact Online data into SQL Server

As you see, these are the same Exact Online contacts showed in the Getting Started section.

Conclusion

You learned how to load data from Exact Online into SQL Server using SSIS and ZappySys SSIS PowerPack. At first, we created an Exact Online OAuth application, then created an OAuth Connection Manager based on its information and finally loaded contacts using JSON Source connector.

References

Exact App Center

API methods

Get contacts API method

Pagination