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.
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:
- Verbind Azure Data Factory met Teamleader Focus
- How to filter data from OData query on Azure Data Factory?
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:
- Teamleader API servers
- Invantive Bridge Online with Teamleader driver using OAuth Code Grant Flow
- Invantive Data Hub with Invantive Bridge Online driver
- Invantive Data Hub with Microsoft SQL Server driver.
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:
- Define a Teamleader database on Invantive Cloud.
- Define a database on Invantive Data Hub with the Bridge Online and Microsoft SQL Server drivers.
- Use Invantive SQL statements to bulk copy, replicate or synchronize the data into tables.
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.
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 ‘firstname.lastname@example.org’ with password ‘secret’. The download will take place using Invantive Bridge Online and the database has Database URL Segment ‘acme-teamleader’:
Make sure to allow access from your devices by whitelisting the Internet IP address in the Bridge Client IP-address list.
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:
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:
with the addition of the Bridge Online data dictionary for native requests.
When the catalog is opened, as with “Teamleader” and schema “V2” below, a striking difference will surface:
The table name is not
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
<?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="email@example.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:
After logging on to the database, a connection is available to multiple data containers:
- One data container
bolfor Teamleader through the Bridge Online server on Invantive Cloud
- One data container
sqlserverfor the on-premise SQL Server instance.
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.
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.