Copy Exact Online to SQL Server

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

  • The copy 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.

Please make sure before running the script that you understand the differences between copying and replicating data in the last section of this entry.

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/release/msi/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 and no two-phase authentication.
  • 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.

Configure distributed database

By default, Invantive Data Hub automatically configures over 50 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 Exact Online the Netherlands, Salesforce and SQL Server into one spanning database…

Create a file settings-distributed.xml in %USERPROFILE%\invantive. This typically is a folder named C:\users\LOGONCODE.

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"
       connectionString="apiUrl=https://start.exactonline.nl;api-client-id=***CLIENT ID***;apiredirecturl=https://eolclientredirect.invantive.com" />
      <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>

Remember to replace the following placeholders:

  • COMPANY NAME: your preferred company name like ‘acme’.
  • 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.

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..salesorders@eol

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

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

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

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

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

create or replace table eol_items@sqlserver
as 
select *
from   exactonlinerest..items@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..glaccounts@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_transactions@sqlserver
as 
select *
from exactonlinerest..transactions@eol

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

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_USR=LOGONCODE
set INVANTIVE_ENCRYPTED_PWD=SECRET
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%" /user:"%INVANTIVE_USR%" /connection:"%INVANTIVE_CONN%" /encryptedpassword:"%INVANTIVE_ENCRYPTED_PWD%" /file:"%INVANTIVE_SQL_FILE%" /logfile:"%INVANTIVE_LOG_FILE%" /logoverwrite:"%INVANTIVE_LOG_FILE_OVERWRITE%" /interactive:%INVANTIVE_INTERACTIVE%
  • Please note that the encrypted password is associated with the device on which you have created the batch file.
  • To re-generate the encrypted password, please issue the statement local encrypt password in Invantive Data Hub or Invantive Query Tool.

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.

After the loading has finished, you can add 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 will be stored in one table for all companies. This eases consolidation.

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 https://superuser.com/questions/1178295/starting-invantive-data-hub-scripts-from-windows-task-scheduler.

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.