I am successfully able to get the data from Invantive Query tool to my local SQL server. This is using an SQL Server query.
However, I am able to run the query at the moment only via the Invantive Query Tool. And I am looking for ways to automate the process so that it runs at least daily.
Is it possible to run the query via Python or run the query via command line? Or if you have any other suggestions to do it, I am open to hear it as well.
By design, the Query Tool is designed not to run from the command line, since it is a GUI application. There are various alternatives for unattended processing using a time-based schedule or another schedule:
Most users that load data into a local SQL Server database use Invantive Data Hub or the enterprise-variant Invantive Data Replicator. Invantive Data Hub connects to all sources and targets at the same time and using existing Invantive SQL-statement the data is streamed in a highly parallel fashion from sources to targets. A sample of a SQL-query to insert data in a new table is:
create or replace table targettable@sqlserver
as
select *
from exactonlinerest..ItemsIncremental@eol
An example can be found on Copy Exact Online to SQL Server.
The Invantive Data Hub and Invantive Data Replicator products require a separate license, with similar costs for similar volumes. Invantive Query Tool comes bundled with Data Hub and Data Replicator.
For programmers, the use of the OData4 connector Invantive Bridge Online may provide a simple way to exchange data from a local server. People have managed to use Google Functions, Azure Functions, Python and C# to download data from Exact Online through the Invantive Cloud into their local SQL Server instances. Sample code and executable can be found on:
Invantive Bridge Online is available with almost all Invantive subscriptions.
Note that it is also possible to use Invantive Cloud as a proxy for Invantive Data Hub as described on Store Exact Online data in an on-premise SQL Server through Invantive Cloud.
It is also possible to consume Invantive Bridge Online from the OData4 consumer of Microsoft Integration Services running on-premises. There is no guide specifically for Microsoft IS, but it highly resembles the staps for Azure Data Factory shown on Hoe verbind ik Exact Online met Azure Data Factory? (Dutch) or Connect Azure Data Factory to Exact Online, 180 days free.