ETLS Overview

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

  • Work seamlessly across all SugarCRM™ editions using the latest API interfaces
  • Extract data from various database sources, as well as CSV, Excel and Access files
  • Transform extracted data via popular scripting technologies
  • Upload Module Records to SugarCRM™ instances using a RESTful web service
  • Pull SugarCRM™ cloud hosted data 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 SugarCRM™ 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™ modules and speedier data uploads using bulk API methods.

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

Data Sources

A data source is associated with each ETLS configuration. ETLS uses data from:

  • Microsoft SQL
  • Access, Excel and CSV files
  • ODBC data sources.

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 SugarCRM™ 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#, VB.Net, PowerShell and PHP 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™ 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™, 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.