With Invantive App Online, it is possible to execute procedure logic through the call of a hyperlink. This article shows a method for automatically executing an application module with a configurable schedule from Azure Data Factory. However, the entire processing is done through the application module and can therefore provide substantial financial savings for using Azure Data Factory.
The following steps are performed:
- Create a module that copies an Exact Online table to Azure SQL Server.
- Identify the App Online hyperlink of the module.
- Create an Azure Data Factory pipeline that calls the hyperlink and protocols the results into a table.
- Verify results.
These steps are intended to be illustrative; many other ways are conceivable that would better suit individual situations.
The JSON definitions of the Azure Data Factory elements used are shown separately at the end.
Create Module to Copy Exact Online table to Azure SQL Server
Within Invantive Cloud, the “Applications” form can be used to define an application module that copies data.
First create a database as described in Elementary Data Replication Module between Exact Online and Azure SQL Server.
Then create a module with code Hello, module language PSQL and the following source code as shown in the image:
begin
--
-- LOGIC HERE SUCH AS:
--
-- 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 AccountsIncremental@eol
-- ;
--
-- Report results as JSON.
--
cloud_http.append_line_to_response_body_text
( '{'
|| jsonelement('message', 'Everything fine.', false)
|| ', '
|| jsonelement('success', true, false)
|| '}'
);
exception
when others
then
--
-- Report failures.
--
cloud_http.append_line_to_response_body_text
( '{'
|| jsonelement('message', sqlerrm, false)
|| ', '
|| jsonelement('success', false, false)
|| '}'
);
end;
Determine App Online Hyperlink of Module
The module can be run directly using the “Run” button within Invantive Cloud, but within Invantive Cloud there is a run time limitation of 5 minutes, plus it is not possible to control Invantive Cloud from a machine.
Through Invantive App Online, the application module can also be executed through a hyperlink. The hyperlink can be invoked with so-called “Basic Authentication,” consisting of Invantive Cloud username and password.
Additionally, the database must allow connections from the IP addresses from which the hyperlink is called. When using Azure Data Factory, it is most cost-efficient not to have a fixed IP address, but many IP addresses must be listed or a allow-all * must be used.
First, configure the database in terms of allowed IP addresses as described in Fix itgenboe031 and itgenboe030 error on Power BI Refresh (Bridge Online access is not authorized from IP address ...).
Then go to the application and choose “Execute”:
If there are multiple databases, a dropdown is displayed with multiple databases. Select the desired database.
If there is only one database, it is selected automatically.
One or more cards will appear such as:
Choose “Copy Link” from the bottom right corner.
The hyperlink for App Online of the module and database is then placed on the clipboard.
Save the hyperlink in notepad.
Create Azure Data Factory Pipeline
Log on to Azure Data Factory.
Add a pipeline as shown and drag a ‘Copy data’ element into the pipeline found under ‘Move and transform’. Select this element and under the ‘General’ tab, complete it with a name, maximum run time of 12 hours, 2 attempts at errors and an interval between attempts of 5 minutes:
Link a source here as described in JSON format (the above example of the application module returns its progress via JSON):
The source is a HTTP-service:
Which is configured as follows:
Here, replace the URL with the saved hyperlink, and username and password with the Invantive Cloud account with which the linked service should run.
In this case, the JSON returns two fields:
Then the output can be saved to a table, for example:
This example uses an Azure table:
But, of course, it can also be done using a traditional table.
During the first run, the table is created automatically, but under “Mapping” the translations can also be specified, such as:
Finally, the pipeline needs to be automatically triggered regularly. This can be done by selecting the “Trigger” button. In this example, a trigger is used that leads to execution of the pipeline every 5 minutes (for production purposes, a significantly lower frequency is recommended).
The definition of the trigger for copying the Exact Online tables to SQL Server is shown below:
Verify Operation
After using the “Debug” button or by waiting ten minutes, the operation can be checked:
Go to Invantive App Online Monitoring and verify that the module has run successfully:
Go to the output table, for example by clicking “Open” in the “Sink” tab of the pipeline, and choose “Preview data.”
JSON-definitions
Pipeline
{
"name": "RunHelloWorld",
"properties": {
"activities": [
{
"name": "Run Hello World and copy to log table",
"type": "Copy",
"dependsOn": [],
"policy": {
"timeout": "0.12:00:00",
"retry": 2,
"retryIntervalInSeconds": 300,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "JsonSource",
"storeSettings": {
"type": "HttpReadSettings",
"requestMethod": "GET"
},
"formatSettings": {
"type": "JsonReadSettings"
}
},
"sink": {
"type": "AzureTableSink",
"azureTableInsertType": "merge",
"writeBatchSize": 10000
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "$['message']"
},
"sink": {
"name": "message"
}
},
{
"source": {
"path": "$['success']"
},
"sink": {
"name": "success"
}
}
],
"collectionReference": "",
"mapComplexValuesToString": false
}
},
"inputs": [
{
"referenceName": "OutputApp",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "LoggingTable",
"type": "DatasetReference"
}
]
}
],
"annotations": []
}
}
Output of App
{
"name": "OutputApp",
"properties": {
"linkedServiceName": {
"referenceName": "HelloWorld",
"type": "LinkedServiceReference"
},
"annotations": [],
"type": "Json",
"typeProperties": {
"location": {
"type": "HttpServerLocation"
}
},
"schema": {
"type": "object",
"properties": {
"message": {
"type": "string"
},
"success": {
"type": "boolean"
}
}
}
}
}
Azure-table
{
"name": "LoggingTable",
"properties": {
"linkedServiceName": {
"referenceName": "AzureTableStorage1",
"type": "LinkedServiceReference"
},
"annotations": [],
"type": "AzureTable",
"schema": [],
"typeProperties": {
"tableName": "mytable"
}
}
}
Trigger
{
"name": "RunHelloWorld5Min",
"properties": {
"annotations": [],
"runtimeState": "Started",
"pipelines": [
{
"pipelineReference": {
"referenceName": "RunHelloWorld",
"type": "PipelineReference"
}
}
],
"type": "ScheduleTrigger",
"typeProperties": {
"recurrence": {
"frequency": "Minute",
"interval": 5,
"startTime": "2023-10-26T14:20:00",
"timeZone": "W. Europe Standard Time"
}
}
}
}