Use Linux to run Invantive Data Hub to Copy Teamleader to Azure SQL Server

Wiki (en)
, , , , ,
1

Just like the Windows version, the Linux version of Invantive Data Hub can copy data from Teamleader or many of the other 60 supported platforms to SQL Server on Azure. This note helps you setting up Invantive Data Hub on Linux and scheduling a job to copy data from a Teamleader environment and a MySQL database to Azure.

Instructions

Installation and Configuration

Perform the following steps to install Data Hub on Linux after acquiring a subscription key:

  • Choose a user to run the program.
  • Create a folder for this user like ~/jobs which will contain the program and the job definitions.
  • Download and install .NET Core version 2.1 or newer (2.2 or newer preferred) from Microsoft.com.
  • Download Invantive Data Hub from download.invantive.com:
cd ~/jobs
mkdir bin
cd bin
wget https://download.invantive.com/current/Invantive%20Data%20Hub-win-x64-net8.0-current.zip
gzip -d Invantive*.tar.gz
tar xf Invantive*.tar
rm -f Invantive*.tar
  • Create a batch file ~/jobs/bin/datahub173182.sh:
#!/bin/sh
cd "~/jobs/bin/Invantive Data Hub for Core 17.31.82 netcore21"
dotnet Invantive.Producer.Data.Hub.dll $*

  • Make the batch file executable using chmod such as:

    chmod +x ~/jobs/bindatahub173182.sh

  • Run the batch file and enter the license key as usually on Data Hub.

Job

Execute the following steps to create a job to copy Teamleader to SQL Server:

  • Make sure Teamleader has been implemented.
  • Acquire Teamleader credentials.
  • Acquire the MySQL database credentials.
  • Make sure your Azure SQL Server is running.
  • Make sure a database and user have been defined in SQL Server.
  • Acquire the Azure SQL Server credentials.
  • Configure settings*.xml in ~/Invantive as explained in Settings.xml XML-format for virtual SQL databases, such as:
<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  VersionUpdateDate="2019-04-26T14:11:42.5942772Z"
  version="5"
  forcedefault="false">
  <group
    name="GROUP"
    sortingOrder="0">
    <connection
      name="GLOBALDATABASE"
      sortingOrder="1000">
      <database
        order="0"
        alias="tl"
        provider="Teamleader"
       	userLogonCodeMode="Hidden" 
	    passwordMode="Hidden" 
        defaultUserLogonCode="xxx" 
        defaultPassword="xxx" 
        connectionString="api-group-authentication=false" 
      />
      <database 
       order="10" 
       alias="mysql" 
       provider="MySql" 
       userLogonCodeMode="Hidden" 
       passwordMode="Hidden" 
       connectionString="Server=localhost;Database=db-with-dashes-partners;Uid=xxx;Pwd=xxx;" 
       AllowConnectionStringRewrite="false" 
      />
      <database 
       order="20" 
       alias="sqlserver" 
       provider="SqlServer" 
       userLogonCodeMode="Hidden" 
       passwordMode="Hidden" 
       connectionString="Server=tcp:xxx.database.windows.net,1433;Initial Catalog=reporting;Persist Security Info=False;User ID=xxx;Password=xxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" 
       AllowConnectionStringRewrite="false" 
      />
    </connection>
  </group>
</settings>
  • Define a SQL file ~/jobs/sql/copy-teamleader-to-sql-server.sql containing the statements to execute, such as:
local remark
local remark Copy data from Teamleader to Azure SQL Server for Power BI.
local remark

local remark MySQL as source

create or replace table mysql_partners@sqlserver
as
select * from def.[db-with-dashes-partners].partners@mysql

local remark Teamleader as source

create or replace table tl_contacts@sqlserver
as
select td.*
from   teamleader.v1.contacts@tl t
join   teamleader.v1.contact@tl(t.id) td
  • Create a batch file ~/jobs/batch/copy-teamleader-to-sql-server.sh to run the job:
#!/bin/sh
export INVANTIVE_CONN='GROUP\GLOBALDATABASE'
export INVANTIVE_SQL_FILE=~/jobs/sql/copy-teamleader-to-sql-server.sql
export INVANTIVE_LOG_FILE=~/jobs/log/copy-teamleader-to-sql-server.log
export INVANTIVE_LOG_FILE_OVERWRITE=False
export INVANTIVE_INTERACTIVE=False

~/jobs/bin/datahub173182.sh /verbose \
    /database:"${INVANTIVE_CONN}" \
    /file:"${INVANTIVE_SQL_FILE}" \
    /logfile:"${INVANTIVE_LOG_FILE}" \
    /logoverwrite:"${INVANTIVE_LOG_FILE_OVERWRITE}" \
    /interactive:"${INVANTIVE_INTERACTIVE}"
  • Test the new batch file and check the log output.

Schedule using Cron

The job should run daily. The Linux variant of the Windows Task Scheduler is cron. For repeating jobs, you can instruct cron to repeat a program daily by adding an entry into the so-called crontab”. For instance, to run at 6:15:

0 6 * * * /home/name/jobs/batch/copy-teamleader-to-sql-server.sh