Copy Exact Online to SQL Server

Dutch version

Besides using managed data replication with Invantive Data Replicator, you can also create a copy of tables in the 70+ supported platforms on a Microsoft SQL Server environment. This entry describes how to configure an automated daily copy from Exact Online to an on-premises SQL Server.

A copy to SQL Server on Azure or AWS is also possible using this approach, but perhaps Invantive Data Replicator, an Invantive Cloud-application or the Exact Online Azure Data Factory Connector of Invantive Cloud are more practical.

Also, Invantive Cloud also provides ETL functionality similar to Azure Data Factory. For an example, please consult:

The Exact Online copy in this sample consists of:

  • general ledger accounts,
  • all transactions and their lines,
  • cost unit definitions,
  • employees,
  • sales orders plus lines,
  • sales invoices plus lines
  • accounts and their addresses and classifications,
  • items, item groups and stock positions,
  • price lists.

To add more tables, pick one from the thousand available Exact Online tables and documented in:

The steps in this topic are self-contained, but given the many and complex components involved it is recommended to use the free hour explanation to get up and running quickly. The free explanation can be scheduled through:

Install Software

First you will need to install Invantive Data Hub on a PC or server. Typically this is done using a trial version of Invantive Data Hub, but you can also use Invantive Query Tool with a premium license. You will find a download on https://download.invantive.com such as https://download.invantive.com/current/Invantive%20Data%20Hub-current.msi.

Then run Invantive Data Hub and configure a trial license which allows unlimited access to almost all Invantive SQL functionality and drivers for 30 days.

Prepare Environment

You will need credentials to log on to Exact Online and SQL Server, and a database to place the replica in. Perform the following steps:

  • Get access to an Exact Online environment with one or multiple companies using a log on code with password.
  • Save the user’s QR-code and associated secret key (explanation).
  • Register an Exact Online application .
  • Open SQL Server Management Studio and log on as user ‘sa’.
  • Create a new database named exactonline.
  • Create a new database user exactonline with SQL authentication.
  • Make the new database user db_owner of the new database.

Configure distributed database

By default, Invantive Data Hub automatically configures over 70 connections for platforms. However, these are all databases each addressing one platform such as Salesforce. To copy data from the Exact Online platform to SQL Server you will need to defined a so-called “distributed database”. A distributed database bundles multiple data containers such as all of Exact Online the Netherlands, Salesforce and SQL Server into one virtual distributed database.

Create a file settings-distributed.xml in %USERPROFILE%\invantive. %USERPROFILE% when entered in the address bar of the Windows Explorer typically navigates to a folder named C:\users\LOGONCODE.

The settings.xml XML-based file format is described in Settings.xml XML-format for virtual SQL databases.

The contents of this file should be:

<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
 version="5" 
 forcedefault="false"
>
  <group name="***COMPANY NAME***" >
    <connection name="exact-online-nl-sql-server"
    >
      <database order="0" 
       alias="eol" 
       provider="ExactOnlineAll"
       defaultUserLogonCode="EXACTONLINEUSERCODE"
       defaultPassword="EXACTONLINEUSERPASSWORD"
       connectionString="api-url=https://start.exactonline.nl;api-client-id=***CLIENT ID***;api-redirect-url=https://clientredirect.invantive.com;totp-secret=TOTPSECRET"
       AllowConnectionStringRewrite="false"
      />
      <database alias="sqlserver" 
        provider="SqlServer" 
       connectionString="Data Source=***HOST***;Database=***DATABASE NAME***;UID=***DATABASE USER LOG ON CODE***;PWD=***DATABASE USER PASSWORD***"
       AllowConnectionStringRewrite="false" 
      />
    </connection>
  </group>
</settings>

This approach uses the Implicit Grant Flow, which is easier to set up. It is also possible to use the Code Grant Flow, but that requires more reading. In scenarios where no parallel processing occurs, the addition of just client-secret=secret to the connection string of Exact Online suffices.

Remember to replace the following placeholders:

  • COMPANY NAME: your preferred company name like ‘Acme Corp.’.
  • CLIENT ID: the client ID retrieved from Invantive or your own client ID. When you use your own client ID, you will also need to change the redirect URL.
  • HOST: the name or IP-address of your SQL Server provider.
  • DATABASE NAME: the name of the database you’ve created, so exactonline.
  • DATABASE USER LOG ON CODE: the name of the user account, so exactonline.
  • DATABASE USER PASSWORD: the password of the user account.
  • EXACTONLINEUSERCODE: the Exact Online log on code.
  • EXACTONLINEUSERPASSWORD: the password of the Exact Online user.
  • TOTPSECRET: the TOTP-secret associated with the QR-code.

Copy Script

The following Invantive SQL script creates and maintains the copy of Exact Online companies in SQL Server.

Save the following contents as exact-online-copy.sql in a folder named C:\test:

local remark When an error occurs on replication, continue replication 
local remark and report the error at the end of the process.

local on error continue

local remark Select all available Exact Online companies.

use all@eol

local remark Do not cache the HTTP requests to save memory for very large environments.

set use-http-memory-cache@eol false

create or replace table eol_salesorders@sqlserver
as 
select *
from   exactonlinerest..salesordersbulk@eol

create or replace table eol_salesorderlines@sqlserver
as 
select *
from   exactonlinerest..salesorderlinesbulk@eol

create or replace table eol_salesinvoices@sqlserver
as 
select *
from exactonlinerest..salesinvoicesbulk@eol

create or replace table eol_salesinvoicelines@sqlserver
as 
select *
from   exactonlinerest..salesinvoicelinesbulk@eol

create or replace table eol_addresses@sqlserver
as 
select *
from   exactonlinerest..addressesbulk@eol

create or replace table eol_accounts@sqlserver
as 
select *
from   exactonlinerest..accountsbulk@eol

create or replace table eol_items@sqlserver
as 
select *
from   exactonlinerest..itemsbulk@eol

create or replace table eol_stockpositions@sqlserver
as 
select *
from   stockpositions@eol

create or replace table eol_pricelists@sqlserver
as 
select *
from   exactonlinerest..pricelists@eol

create or replace table eol_itemgroups@sqlserver
as 
select *
from   exactonlinerest..itemgroups@eol

create or replace table eol_costunits@sqlserver
as 
select *
from   exactonlinerest..costunits@eol

create or replace table eol_glaccounts@sqlserver
as 
select *
from   exactonlinerest..glaccountsbulk@eol

create or replace table eol_employees@sqlserver
as 
select *
from    exactonlinerest..employees@eol

create or replace table eol_accountclassifications@sqlserver
as 
select *
from   exactonlinerest..accountclassifications@eol

create or replace table eol_transactionlinesbulk@sqlserver
as 
select *
from   exactonlinerest..transactionlinesbulk@eol

Use the Exact Online API data model documentation to search for more needed tables, or browse through the graphical table relationship diagram.

Then, create a batch file to run this script. The easiest way to do so is to:

  • Start the Invantive Query Tool.
  • Log on to the database exact-online-nl-sql-server in your company group.
  • Open the file c:\test\exact-online-copy.sql.
  • Choose ‘Editor’ in the menu and then ‘Create Data Hub Batch Script’.
  • Select ‘Save’.
  • Name the batch file c:\test\exact-online-copy.bat.
  • The resulting batch file will resemble:
@echo off
rem
rem Script to run an Invantive SQL file with possible Invantive Script statements.
rem
rem Please note that the encrypted password is bound to the PC used to generate this file.
rem When you want to run this batch file on another PC, please generate a new encrypted password at that PC.
rem
rem In Invantive Query Tool and Data Hub you can use the 'local encrypt password' statement to generate.
rem a new password.
rem
rem Created by Invantive Query Tool on 08/02/2018 12:30:29.
rem
rem $Id: $
rem

set INVANTIVE_PRG=C:\Program Files (x86)\Invantive Software BV\Invantive Data Hub\Invantive.Producer.QueryEngine.exe
set INVANTIVE_CONN=COMPANY NAME\exact-online-nl-sql-server
set INVANTIVE_SQL_FILE=c:\test\exact-online-copy.sql
set INVANTIVE_LOG_FILE=c:\test\exact-online-copy.log
set INVANTIVE_LOG_FILE_OVERWRITE=False
set INVANTIVE_INTERACTIVE=False

"%INVANTIVE_PRG%" /connection:"%INVANTIVE_CONN%" /file:"%INVANTIVE_SQL_FILE%" /logfile:"%INVANTIVE_LOG_FILE%" /logoverwrite:"%INVANTIVE_LOG_FILE_OVERWRITE%" /interactive:%INVANTIVE_INTERACTIVE%

Copy Exact Online tables

To actually copy the data of Exact Online into SQL Server, please run the batch file c:\test\exact-online-copy.bat.

This will display something like:

The tables in SQL Server are created using bulk inserts from the data retrieved from Exact Online. Where necessary, you will need to adapt the select-clause to remap column names such as ‘User’ to something supported on SQL Server. Otherwise, Invantive SQL will automatically rewrite it to an allowed column name.

After the loading has finished, there will be a number of indices on the tables, based upon the distribution and quantity of the data. You can add more indexes on the tables for better performance, based upon your knowledge of the data model and the environment.
Identical table data from all selected Exact Online companies is automatically stored in one table for all companies. This eases consolidation across companies.

Of course you can filter the data sets retrieved from Exact Online for better retrieval performance, such as filtering on status to only retrieve open sales orders. Invantive SQL automatically translates your where-clauses to filters forwarded to Exact Online for better retrieval performance.

It is recommended to use the Microsoft Windows Task Scheduler to daily run the batch file as described on Starting Invantive Data Hub scripts from Windows Task Scheduler - Super User.

Differences

Compared with Invantive Data Replicator, there are a number of drawbacks which you should be aware of when using this approach:

  • Versioning: Data Replicator adds automatic versioning of data to enable 24x7 availability of data sets.
  • Data model: Data Replicator automatically maintains the data model and maps column names and table names into the possibilities of the target platform.
  • Performance: Data Replicator automatically tunes and partitions the data for very large data sets both in terms of partitions as well as individual partition size.
  • Incremental: Data Replicator provides incremental load strategies such as using web hooks and smart sampling targeting large volumes.
  • Audit: Data Replicator adds additional logging and auditing features to ease compliance with regulatory reporting applications.

In general, use Invantive Data Replicator for data volumes between 10 GB and 5 TB or over 100 companies. Use Data Hub for smaller volumes.