Replicate Teamleader to SQL Server

Teamleader is an innovative Belgium-based software solution for service oriented businesses such as marketing agencies. Invantive has added a driver for the Teamleader API in 2017 to Invantive SQL. In this entry you will learn how to use this driver combined with Invantive Data Replicator to maintain a replica of Teamleader in a SQL Server environment.

Of course you can also use PostgreSQL to replicate into.

The replica consists of:

  • Projects including details and custom field values,
  • contacts
  • custom field definitions and
  • companies.

Install Software

First you will need to install Invantive Data Replicator 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 license including Invantive Data Replicator. 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 Teamleader and SQL Server, and a database to place the replica in. Perform the following steps:

  • Open Teamleader and go to https://app.teamleader.eu/apiwebhooks.php?show_key.
  • Note the API group (a number) and API key (a long text) listed. You will use these to log on to Teamleader using Invantive software.
  • Open SQL Server Management Studio and log on as user ‘sa’.
  • Create a new database named ‘teamleader’.
  • Create a new database user ‘teamleader’ with SQL authentication.

Configure connection with Data Replicator

By default, Invantive Data Hub automatically configures over 50 connections for platforms. For Invantive Data Replicator, you will need to add a manual definition.

Create a file settings-teamleader-data-replicator.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="teamleader-data-replicator"
     dataCacheConnectionString="provider=SqlServer;connectionString={Server=***HOST***;Database=***DATABASE NAME ***;User Id=***DATABASE USER LOG ON CODE***;Password=***DATABASE USER PASSWORD***}"
    >
      <database provider="Teamleader" alias="tl" />
    </connection>
  </group>
</settings>

Remember to replace the following placeholders:

  • COMPANY NAME: your preferred company name like ‘acme’.
  • HOST: the name or IP-address of your SQL Server provider.
  • DATABASE NAME: the name of the database you’ve created, so ‘teamleader’.
  • DATABASE USER LOG ON CODE: the name of the user account, so ‘teamleader’.
  • DATABASE USER PASSWORD: the password of the user account.

Replication Script

The following Invantive SQL script creates and maintains the replica.

Save the following contents as teamleader-replicate.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 Companies is a normal table on Teamleader; it is non-parameterized
local remark and just returns the results. Opposed to other platforms, Teamleader has
local remark very many parameterized APIs.
local remark Luckily, companies is easy to replicate. Just run a query, optionally
local remark with hints to specify a deviating refresh interval here of 20 hours.

select /*+ ods(true, interval '20 hours') */ count(*) 
from   companies@tl

local remark The Projects of Teamleader seems a table, but is actually a table
local remark function with a default parameter of '0' to include active and inactive
local remark projects. Table functions can not be replicated as-is, since their
local remark contents vary.
local remark By creating an in-memory copy we first download the right data
local remark and then create a replica in SQL Server using the ODS hint
local remark on the select statement below.

create or replace table projects@inmemorystorage as select * from projects@tl

select /*+ ods(true, interval '20 hours') */ count(*) 
from   projects@inmemorystorage

local remark Replicate contacts.

create or replace table contacts@inmemorystorage as select * from contacts@tl

select /*+ ods(true, interval '20 hours') */ count(*) 
from   contacts@inmemorystorage

local remark Replicate custom field definitions.

create or replace table custom_fields@inmemorystorage 
as 
select * 
from   custom_fields('PROJECT')@tl
union all
select * 
from   custom_fields('TASK')@tl
union all
select * 
from   custom_fields('COMPANY')@tl
union all
select * 
from   custom_fields('CONTACT')@tl

select /*+ ods(true, interval '20 hours') */ count(*) 
from   custom_fields@inmemorystorage

local remark Some custom fields have pre-defined lists of values.

create or replace table custom_field_options@inmemorystorage 
as
select cfn.* prefix with 'cfn_' 
,      cfd.* prefix with 'cfd_' 
from   custom_fields@inmemorystorage cfd
join   custom_field_options(cfd.id)@tl cfn
where  cfd.type='enum'

select /*+ ods(true, interval '20 hours') */ count(*) 
from   custom_field_options@inmemorystorage 

local remark Replicate project details.

create or replace table projectdetails@inmemorystorage
as
select pjtd.* 
from   projects@inmemorystorage pjt
join   project(pjt.id)@tl pjtd

select /*+ ods(true, interval '20 hours') */ count(*) 
from   projectdetails@inmemorystorage 

create or replace table project_custom_field_values@inmemorystorage
as
select pjtd.* 
from   projects@inmemorystorage pjt
join   project_custom_field_values_by_id(pjt.id)@tl pjtd

select /*+ ods(true, interval '20 hours') */ count(*) 
from   project_custom_field_values@inmemorystorage 

select /*+ ods(true, interval '20 hours') */ count(*) 
from   custom_fields@inmemorystorage

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 Teamleader using the group and API key noted.
  • Open the file c:\test\teamleader-replicate.sql.
  • Choose ‘Editor’ in the menu and then ‘Create Data Hub Batch Script’:
    teamleader-replicate-sql-server-batch-script
  • Select ‘Save’.
  • Name the batch file c:\test\teamleader-replicate.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\teamleader-data-replicator
set INVANTIVE_SQL_FILE=c:\test\invantive-replicate.sql
set INVANTIVE_LOG_FILE=c:\test\invantive-replicate.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.

Replicate Teamleader

To actually replicate the data of Teamleader into SQL Server, please run the batch file c:\test\teamleader-replicate.bat.

This will display something like:

When the batch file runs first, it will create a number of repository tables that contain metadata on the replication process. These can be recognized by their name starting with dc_:

teamleader-replicate-sql-server-repository

The actual replicated data in various versions will be stored in tables with a name starting with dcd_. The tables starting with dcs_ will remain empty; these are used to generate so-called “data vaults” that add time travel to the replicas.

Database indexes are created and maintained automatically, depending on the data volume and their statistics.

In the database, you will also find views being created. Depending on the configuration, you will see one view created per table in Teamleader. Such views typically start with tlr_ as an acronym for Teamleader:

teamleader-replicate-sql-server-views

Once Teamleader replication to SQL Server has finished, you can use the data from SQL Server by querying the views. It is recommended against to base queries on the tables, since they can contain multiple versions of the same data over time.

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.

Sample: Teamleader project titles and custom field values

Once the replicate is created, you can query SQL Server for a list of project titles and the value for a specific custom field:

select pjt.title
,      cfd.name
,      pce.value_ 
from   imy_projectdetails_r pjt
join   imy_custom_fields_r  cfd
on     cfd.id=5541 /* Externally visible custom field. */
left
outer
join   imy_project_ctxo2eld_values_r pce
on     pce.key_ = cfd.id
and    pce.id = pjt.id

Note that this query only works on SQL Server; it is not intended for use with the Teamleader driver of Invantive SQL. You will need to replace the SQL Server view names such as imy_projectdetails_r by their respective versions of Invantive SQL when you use this query on the Teamleader driver of Invantive SQL.