ETLS Overview

ETLS is an ETL tool for SugarCRM™ & SuiteCRM™ data. Here are the key components that let users quickly and easily:

  • Work seamlessly across all SugarCRM & SuiteCRM editions using the latest API interfaces
  • Extract data from various database sources, CSV, Spreadsheets and Access files, as well as cloud-based Amazon, Azure, Dropbox and Google Drive files.
  • Transform extracted data via popular scripting technologies.
  • Upload Module Records to SugarCRM and and SuiteCRM instances using RESTful interfaces.
  • Pull SugarCRM and SuiteCRM data from cloud-hosted instances for reporting and database updates.

    ETLS supports multiple user configurations and runs interactively or unattended. One of its key features is the ability to create relationships in one step using a single data pass - relationship is the key word of any CRM system.

    Other key features include the capability to provide real-time data from external data sources to SugarCRM & SuiteCRM modules and speedier data uploads using bulk API methods.

    ETLS lets users control multiple configurations for uploading data to different modules. In the example below (1) selects the configuration, (2) selects the data source, and (3) provides the login for the target system. The Setup tab (4) lets users specify the a parent module (5) and optionally a child module along with its relationship to the parent module.

Data Sources

A data source is associated with each ETLS configuration and connects to:

  • Spreadsheets, Access and CSV files
  • Microsoft SQL databases
  • Other ODBC data sources.

ETLS provides a service task for Spreadsheets and CSV cloud-based data feeds from Email, Amazon, Azure, Dropbox and Google Drive.

ETLS comes with demo configurations using inputs from Excel, Access, SQL Server and a CSV file.

Data Transformations

Preparing data and formatting it for the upload process often requires data transformations. Using SQL as a first level of data transformation, when possible, is a good start in formatting the source data.

In addition, ETLS provides a transformation hook named UserLoader, that lets users manipulate the data at multiple points during the upload process to the target your SugarCRM & SuiteCRM instance. Any additional business logic to validate and transform the data can be based on the UserLoader hook and the data source. Transformation examples in C#, PowerShell, PHP and VB.Net are provided as a guide.

Real-Time External Data Integration and Reporting

A couple of WEB options that run on IIS or Apache servers under Windows OS, can provide real-time table formatted output and reports output.

These options are available in both on-demand and on-premise environments and are compatible with versions 6.x and 7.x in all SugarCRM & SuiteCRM editions.

In the example below, a ShellMaster service add-on, allows users to drill down master-detail data, based on their selected ETLS data sources.

integrate example

ETLS and its companion add-on ShellMaster offer a quick and cost-effective way of integrating real-time data from back office and legacy applications into SugarCRM & SuiteCRM, as shown for Open Orders above.

Crystal Reports users that would like to integrate their reports in real-time, can contact infoSpectrum for their ShellCrystal add-on. The Invoice shown above is one such affordable Crystal Report integration.