Import Teamleader data in an on-premise SQL Server database through Invantive Cloud

Wiki (en)
, , , , ,
1

Breaking Change

Teamleader has applied a breaking change on July 16, 2021 for use with the background OAuth provider used from Invantive Data Hub.

Invantive Query Tool and Invantive Control for Excel are not affected as they use the OAuth Implicit Grant Flow. Invantive Cloud is neither affected since it uses the the OAuth Code Grant Flow.

Whilst the Invantive development team works on an improved background OAuth provider, a workaround is available for Data Hub users downloading Teamleader database into an on-premise SQL Server database server. The workaround uses Invantive Cloud and it’s OAuth Code Grant Flow for Teamleader as a proxy.

Download Teamleader data to save in SQL Server on-premise database

A popular application of Invantive Cloud is to act as an OData producer for Azure Data Factory. This enables the replication of Teamleader transaction data, JIRA Service Desk, or ActiveCampaign into a SQL Server database or other data flows. This also works for all other supported platforms as a data source. For some background please consult:

However, Invantive Cloud can not access on-premise resources such as a database in an on-premise Microsoft SQL Server instance. The traditional implementation is to use Invantive Data Hub to directly connect to both the Teamleader database and SQL Server database. Then use either Invantive bulk copy SQL statements or Invantive Data Replicator to store the Teamleader data in the SQL Server database. Some samples of this deployment scenario on SQL Server are:

By downloading data from Invantive Cloud into an on-premise Data Hub or Data Replicator process you can still download from the Teamleader database and store them in a local Microsoft SQL Server database. Of course, it is also possible to save the Teamleader data in Microsoft SQL Server on Azure, but Azure SQL Server can also be accessed directly from Invantive Cloud so this latter more simple approach should be used.

The data flow from the Teamleader data source to the target database is as follows:

  1. Teamleader API servers
  2. Invantive Bridge Online with Teamleader driver using OAuth Code Grant Flow
  3. Invantive Data Hub with Invantive Bridge Online driver
  4. Invantive Data Hub with Microsoft SQL Server driver.

The same data flow applies for other sources such as by replacing (1) with the Visma.net Financials API servers and using the Visma.net Financials driver of Invantive with (2).

The set up of receiving data from the Teamleader data source into an on-premise Microsoft SQL Server database through Invantive Cloud consists of the following steps:

  1. Define a Teamleader database on Invantive Cloud.
  2. Define a database on Invantive Data Hub with the Bridge Online and Microsoft SQL Server drivers.
  3. Use Invantive SQL statements to bulk copy, replicate or synchronize the data into tables.

Pre-requisites

Make sure to use Invantive Query Tool and Invantive Data Hub in release 20.0.148 or newer, or 20.1.380 BETA or newer.

Define Teamleader database on Invantive Cloud

The Teamleader database can be configured by following the already documented steps on Invantive Cloud up to “Configure Power BI for Teamleader”.

It is assumed for the next steps that the Invantive Cloud user is ‘john.doe@acme.com’ with password ‘secret’. The download will take place using Invantive Bridge Online and the database has Database URL Segment ‘acme-teamleader’:

Teamleader database definition
Teamleader database definition724×317 19.3 KB

Make sure to allow access from your devices by whitelisting the Internet IP address in the Bridge Client IP-address list.

Define a database on Invantive Data Hub

The first step is to check connectivity by starting the Invantive Query Tool. Go to the database group “Various” and choose the “Invantive Bridge Online” database. Log on as shown:

Bridge Online log on
Bridge Online log on550×672 50.6 KB

Upon connection, the Bridge Online driver will establish the metadata for the Invantive Cloud database. In this case, this will resemble the standard Teamleader V1 and V2 catalogs:

Teamleader catalogs

with the addition of the Bridge Online data dictionary for native requests.

Naming Differences

When the catalog is opened, as with “Teamleader” and schema “V2” below, a striking difference will surface:

Bridge Online representation of Teamleader V2 Projects

The table name is not Projects but Projects@Tlr. This reflects the list of tables displayed in Power BI on Teamleader. The alias of the data container on Invantive Cloud becomes part of the table name from the perspective of the Query Tool. The following statement can be used to query this table:

select *
from   Teamleader.V2.[Projects@Tlr]

When an alias bol is defined on the Bridge Online data container, the query on the Projects table would read:

select *
from   Teamleader.V2.[Projects@Tlr]@bol

Multiple Data Containers

The data access to all Teamleader tables has been assured. As a next step, define a database for Data Hub and/or Data Replicator in a settings-sample.xml file in %USERPROFILE%\invantive, such as (format description):

<?xml version="1.0" encoding="utf-8"?>
<settings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3" forcedefault="false">
  <group name="Sample" id="d7bc8a41-db12-4b75-8664-19a8a52ef5a0" sortingOrder="0" >
    <connection name="Sample" id="54d6bdc4-dc09-5ed9-9763-680d05a6b1ba" >
      <database
       order="10"
       alias="bol"
       provider="InvantiveBridgeOnline"
       userLogonCodeMode="Hidden"
       passwordMode="Hidden"
       defaultUserLogonCode="john.doe@acme.com"
       defaultPassword="secret"
       AllowConnectionStringRewrite="false"
       connectionString="server=https://bridge-online.cloud;database=acme-teamleader"
      />
      <database
       order="30"
       alias="sqlserver"
       provider="SqlServer"
       userLogonCodeMode="Hidden"
       passwordMode="Hidden"
       connectionString="Data Source=sqlserver01.local;UID=myuser;PWD=mypassword"
       AllowConnectionStringRewrite="false"
      />
    </connection>
  </group>
</settings>

The connection will be available to Query Tool, Data Hub, and Data Replicator after scanning the files using the blue “Edit” button:

Connection to Teamleader and SQL Server
Connection to Teamleader and SQL Server550×505 45.9 KB

After logging on to the database, a connection is available to multiple data containers:

  • One data container bol for Teamleader through the Bridge Online server on Invantive Cloud
  • One data container sqlserver for the on-premise SQL Server instance.

Multiple data containers

The Teamleader data can be retrieved and stored in the current Microsoft SQL Server database using table load statements such as:

create or replace table projects@sqlserver
as
select *
from   Teamleader.V2.[Projects@Tlr]@bol

By repeating these steps for multiple tables, you can easily create a data warehouse or provide a BI tool of your choice with data.

Use Invantive SQL statements to copy, replicate or synchronize the data

Limitations

The replacement of a direct connection with Teamleader by a connection through Invantive Cloud has several limitations/drawbacks. The following limitations of this approach apply, partly already discussed above:

  • Less control on security: the business data flows through cloud servers, managed by Invantive. Many companies apply on-premise technology and direct connections to reduce the number of external managed components.
  • Less manageable: Invantive Data Replicator provides automatic metadata management, automatically adapting to the data retrieved. It also includes 24/7 availability through discrete versions. Despite that this approach automatically changes data types, it does not provide 24/7 availability as the tables are gone during copying.