This articles guides you through the creation of an elementary application module on Invantive Cloud to replicate data from Exact Online to a table re-created in SQL Server every time the module is executed. Advanced and/or scheduled replication is available using Invantive Data Replicator and/or Invantive Data Hub.
These steps apply to both Azure SQL Server and on-premises Microsoft SQL Server. Invantive Cloud automatically adjusts the behavior depending whether it is an Azure SQL Server or Microsoft SQL Server database.
If you prefer to use Azure Data Factory, you can find a comprehensive list of setup steps at Connect Azure Data Factory to Exact Online, 180 days free. Azure Data Factory provides a different set of functionality and may add some cost depending on your Azure Data Factory setup and underlying agreements.
Instructions
Prepare Database with Exact Online and SQL Server
Execute the following steps to create a (virtual) database which allows direct access to SQL Server and Exact Online:
- Create a database with Exact Online as data container on https://cloud.invantive.com/app/setup/databases/new.
- Open the database on Invantive Cloud.
- Click on “Data Containers“.
- Check that the Exact Online data container has alias “eol“.
- Add a data container to the database using SQL Server.
- Check that the SQL Server data container has alias “sqlserver“
Application module
Execute the following steps:
- Create a new application on https://cloud.invantive.com/app/development/applications/new.
- The name can be “Sample“ and description “Sample Exact Online replication“.
- Click on “Modules”.
- Add a new module with code “EOL2SQLSERVER” and description “Replicate data from Exact Online to SQL Server“
- The source code specifies that a HTML page is to be printed after copying the data from Exact to SQL Server:
declare
l_output varchar2;
begin
l_output := '';
l_output := l_output || '<h2>Replicate</h2>';
--
-- Copy the Accounts from all Exact Online companies
-- to the SQL Server database as a new table.
--
create or replace table eol_accounts@sqlserver
as
select *
from exactonlinerest..accountsbulk@eol
;
--
cloud_http.set_use_template(true);
cloud_http.set_response_body_text(l_output);
end;
Execute Replication
Execute the following steps to execute the replication:
- Go back to the new created application.
- Click on “Execute“.
- Choose the database you have created.
- Click on “Execute“.
- Click on the module.
- After some time, depending on the amount of accounts in Exact Online, a HTML page is rendered.
Now check your SQL Server database and see the new table.
Execute Exact Online / SQL Server integration using Curl
The module can be executed interactively from Invantive Cloud. With App Online, you can also run the module using a browser or curl.
Execute the following steps to run the module:
- Determine the URL to use on App Online as follows.
- Run the application.
- Choose the database.
- Find the module you wish to run from App Online.
- Select the link “Copy Link“ at the bottom right corner of the module.
- The link is now on your clipboard.
- Paste the link in a new browser window.
- Enter your Invantive Cloud user and password (which is shared with App Online).
- In case an authentication error occurs please ensure that on the database the correct IP addresses are whitelisted.
From curl, you can run the module using the following syntax:
curl \
--basic \
--user user...:password... \
--output c:\temp\results.html \
--url https://app-online.cloud/apps/.../databases/.../modules/.../
A more extensive sample using curl to retrieve data from Invantive Cloud is available at Pre-load data in OData-cache to avoid Power BI timeouts.
Instead of curl other apps can be used such as Postman or Azure Logic Apps.