Overview

Integrator is an all-in-one cloud service for all your data integration projects, regardless of data location, format and volume. It is a completely online solution and does not require any local software installed except for a web browser.

You can access data in relational databases, files, web services, and social media web sites.

Glossary

How to start

In Integrator you create data integration flows that define the operations and then you run them or schedule for automatic execution.

Step 1 Create Connections to access data from relational databases, social media web sites, third-party web services and applications.

Step 2 Create Formats to define input and output data objects. This step is not required when working with relational databases.

Step 3 Create Flow by selecting pre-defined flow type from the gallery, selecting input and output connections and formats and defining mapping between sources and destinations.

Step 4 Test Flow by manually executing it from the Flow Builder and verifying that it works as expected.

Step 5 Schedule Flow to be executed periodically or triggered by events.

Integrator User Interface

Integrator is a web application with intuitive and easy-to-use user interface.

UI

  1. Left navigation bar which contains applications, such as Connections, Form Builder, etc.
  2. Grid, displaying Connections, Formats, Listeners, Flows, Schedules, etc.
  3. Top navigation bar: User Preferences, About, Help and Log Out.
  4. Filters and Tags.
  5. Tabs.
  6. Grid control elements, such as Edit, Delete, Duplicate, etc.

You can collapse left navigation bar by clicking the << button.

Most of the top level UI elements, such as grids, contain links to the "detail" screens. You can navigate back from the detail screen by clicking the Cancel or Back button.

Using Filters

To quickly find the Integrator resources, such as Connections, Flows, Formats, Schedules, etc. you can enter a part of the name or type in the Filter box. After this only the resources, having the entered string in their names, will be displayed.

Filters

Tagging resources

To help you manage your resources, such as Connections, Formats, Listeners, Flows and Schedules, you can optionally assign your own metadata to each resource in the form of tags. This topic describes tags and shows you how to create them.

Tag Basics

Tags enable you to categorize your Integrator resources in different ways, for example, by purpose, owner, or environment. Each resource can have multiple tags assigned. This is useful when you have many resources of the same type — you can quickly identify a specific resource based on the tags you've assigned to it. Each tag consists of a key which you define. Using a consistent set of tags makes it easier for you to manage your resources. You can group by and filter the resources based on the tags you add.

How to Tag resource

When editing resource, such as Connection, Format, Listener, Flow or Schedule, select or type in tag name. Press Enter.

add tag

How to remove Tag from the resource

When editing resource, such as Connection, Format, Listener, Flow or Schedule, click the X button in front of tag name.

How to group resources by assigned Tags

When you are in a grid, displaying resources, such as Connections, Formats, Listeners, Flows or Schedules, click the Enable Tags View.

group by tags

To switch back to the grid view, click this button again.

How to display tags only

Click the Welcome link on the top of the screen, select Expandable tags.

Expandable tags

Any grid, which contains tags will collapse to the "tags only" view.

Collapsed tags

How to filter by Tags

When you are in a grid, displaying resources, such as Connections, Formats, Listeners, Flows or Schedules, select one or multiple tags from the Tags drop-down.

filter tags

Sticky Filters

If you have a lot of flows, connections and formats, you are probably already using filters, tags and tag filters. You can configure Integrator to remember all active filters, so next time you open Connections, Formats, Flow Builder or Scheduler all previously configured filters are preset and active.

To enable sticky filters click the Welcome link on the top of the screen and select Remember filters value.

sticky filters

Disabling pop-up notifications

To disable pop-up notifications, such as Flow executed successfully, click the Welcome link on the top of the screen and select Hide flow execution notifications.

disable notifications

Connections

In order to perform data integration operations, you need to create a connection first. You can create a connection on the Connections page.

Step 1 To create a new connection, on the Connections page click the + button. Then click the corresponding connection type in the opened box.

Connections

To quickly find the corresponding type you can enter a part of the type name in the search box. After this only the types, having the entered string in their names, will be displayed. To filter connections by a category, you can click the corresponding connection category from the list to the left of connection types.

Step 2 After connection type has been selected, continue by entering connection parameters.

Connection parameters

Important: while we do store credentials, such as passwords, API keys, authentication tokens, etc. in our database we:

Step 3 After connection has been created you can test it by clicking the Test Connection button. Important:Test Connection button is not visible for some types of the connections, such as HTTP.

Step 4 After connection has been tested you can save it by clicking the Save button.

All saved connections are displayed in a grid on the Connections page and can be quickly filtered by name and type. Connections can be duplicated or deleted right from the grid.

All Connections

Relational Databases

Important: if you are using Integrator as a cloud service, when creating connection to the database, please make sure that:

  1. The database is Internet facing (available from the Internet).
  2. The IP address for the host integrator.etlworks.com is white-listed from your database server.

Integrator supports almost all relational databases as long as there is an JDBC driver for the database. The following drivers are included:

Step 1 In the Connections window click the + button and select Database in the left navigation bar. Select database from the gallery.

Databases

Step 2 Enter required parameters.

jdbc:postgresql://<host>:<port=5432>/<database> -> jdbc:postgresql://localhost:5432/mydb

Postgres

Connecting to database if it is not on the list

Tip: If your database is not on the list of the supported databases, you can still connect to it:

Step 1 Contact Etlworks support, so we can add a JDBC driver for your database. Or, if you are running Integrator on premise, simply drop JDBC driver into the application server lib folder and restart the server.

Step 2 Select Generic JDBC connection type.

generic JDBC

Step 3 Specify connection parameters.

JDBC

Temporary (staging) database

In Integrator it is possible to create a connection to the temporary, in memory database and use it as a staging area for the data. SQLite engine is used for the temporary databases. Temporary database is only visible to the database connection that originally opened it and is automatically deleted when the connection that created it closes.

To create connection to the temporary database:

Step 1 In the Connections window click the + button and type in temp. Select Temporary Database.

Temporary Database

Step 2 Enter required parameters.

Temporary Database parameters

Cloud Storages

To create connection to the cloud storage:

Step 1 In the Connections window click the + button, select Cloud Storage.

Cloud storages

Step 2 Select appropriate cloud storage connection type and enter required parameters..

Integrator supports the following cloud storages:

Important: you must have cloud service account (Amazon, Google, Microsoft, etc.) in order to access data stored in a cloud storage.

Amazon S3 and Google Storage

Important: to connect to the Google Cloud Storage you will need to enable Interoperability API. To enable: go to Google Cloud console->Storage->Settings->Interoperability->Enable and create a pair of Access Key and Secret.

Parameters:

Microsoft Azure Storage

Parameters:

File Storages

To create connection to the file storage:

Step 1 In the Connections window click the + button, select Files.

File storages

Step 2 Select appropriate cloud storage connection type and enter required parameters..

Integrator supports the following file storages:

FTP and SFTP

Parameters:

WebDAV

Parameters:

Server Storage

When you sign up to the Integrator, we create a storage volume in our servers, which is available for your account. In this documentation we will be referencing it as a Home folder.

Important: if you install Integrator on premise, you can access files on any available network or local storage device.

Parameters:

Read more about server storage.

Read how to work with local files.

Key-Value Storage (Redis)

At this time the only supported key-value storage is Redis. Integrator can read data by the unique or wildcard key and write data using unique key.

To connect to the Redis you need to specify the following parameters:

HTTP Connector

In Integrator you can create a connector to practically any HTTP based web service. It can be REST or SOAP API, well known web service, or social web site. It is typically assumed that the web service is REST-based, but you can also connect to the SOAP or proprietary web services. Note: Integrator supports GET, POST, PUT and DELETE methods.

Start creating a connector to the web service by selecting HTTP Connection Type from the gallery:

Connection parameters

To connect to the Web Service you need to specify the following parameters:

https://011-VPN-041.mktorest.com/rest/v1/lead/36938.json?access_token={access_token}

Note: start of the parameters used for authentication. If authentication is not required you can skip this section.

https://www.googleapis.com/oauth2/v3/token?parameter_grant_type=refresh_token&parameter_refresh_token={refresh_token}  
{"login":"{user}","password":"{password}"}

Read more about different types of authentication when connecting to the web services.

Note: end of the parameters used for authentication

Google Analytics

Integrator can extract data from the Google Analytics API. Read more about Google Analytics connector in Integrator.

To work with Google Analytics you will need to create a connection.

Google Analytics Connection

When creating a connection, define the following properties:

Inbound and Outbound Email

Integrator supports Inbound Email connections for reading data and Outbound for writing (also just sending emails).

Start creating Email connection by typing in mail is the filter field in the "Select Connection Type" gallery:

Connection parameters

To read data from the mail server you need to specify the following parameters for the Inbound Email connection:

To send a message to the email address you need to specify the following parameters for the Outbound Email connection:

HL7 MLLP Sender

This type of connection used to send HL7 messaged over MLLP protocol. The MLLP is a Minimal Lower Layer Protocol, which is a standard for receiving and transmitting HL7 messages via TCP/IP.

Start creating HL7 MLLP Sender connection by typing in hl7 in the filter field in the "Select Connection Type" gallery. Continue by selecting HL7 MLLP Sender connection:

HL7 MLLP Sender

To send HL7 message over MLLP connection you need to specify the following parameters:

HL7 HTTP Sender

This type of connection used to send HL7 messaged over HTTP protocol.

Important: HL7 HTTP sender is an optional implementation of the HTTP client for sending HL7 messages. Integrator includes generic HTTP connector, which in most cases be used instead.

Start creating HL7 HTTP Sender connection by typing in hl7 is the filter field in the "Select Connection Type" gallery. Continue by selecting HL7 HTTP Sender connection.

To send HL7 message over HTTP connection you need to specify the following parameters:

Amazon MWS Feeds API

Amazon MWS supports the management of seller listings for items to be sold on Amazon.com. Listings can be added, deleted, or modified with Amazon MWS. A variety of specialized feed formats are supported to meet a wide range of seller needs. Read more about Amazon MWS Feeds API.

To connect to Amazon Feeds API in Integrator:

Step 1. Open Connections window and click the + button.

Step 2. Type in amazon mws feed in the Search field.

Amazon MWS feed

Step 3. Select Amazon MWS feed connection and continue by defining connection parameters.

Amazon MWS feed connection properties

The following parameters are available:

Important: parameters Number of Retries and Delay between retries can be used to "fight" throttling enforced by Amazon.

Facebook

Read how to connect to Facebook.

Twitter

Read how to connect to Twitter.

Listeners

Listeners allow user to create event-driven flows. For example, you can define an HTTP endpoint, which will be listening to the incoming HTTP POST requests. Once request is received, the payload will be send to the flow as a source and transformed to any supported destination.

Thanks to the listeners, Integrator can work as an Enterprise Service Bus, serving requests from the third-party applications. Basically you can build an API, without writing a single line of code, which can be used by other applications to send payload to the Integrator.

Start creating a Listener by opening Connections page and selecting Listeners tab.

To create a new Listener, on the Listeners page click the + button. Then click the corresponding listener type in the opened box.

All saved listeners are displayed in a grid and can be quickly filtered by name and type.

Listeners can be duplicated or deleted right from the grid.

Listeners

HTTP Listener

The HTTP Listener is used to build an Integrator API, which receives a payload from the HTTP request and sends it as a source directly to the flow. If flow is scheduled, it is getting automatically executed each time the payload is received.

To create an HTTP Listener you need to specify the following parameters:

Note: the particular URL Pattern must be unique across all listeners and all customers. There is a rule: one listener - one flow. Integrator validates against this rule and provides an appropriate error message if someone is trying to create an endpoint which already exists.

request.setInput(request.getInput().replace(/ /g, '-')); request.setSuccess(false); value = 'Expected response';

In this example JavaScript does the following:

  1. Replaces all white-spaces in the request on - character.
  2. Sets response to "error" (the 500 HTTP code will be returned back to the caller).
  3. Set the response body to Expected response.

Important: all Integrator API endpoints, including created using HTTP Listeners, are protected and require a valid JWT security token in the Authorization header. Read more how to authenticate API requests.

HL7 MLLP Listener

The MLLP listener implements the Minimal Lower Layer Protocol (MLLP) which is a standard for receiving and transmitting HL7 messages via TCP/IP.

The MLLP listener is used to build an Integrator API which receives a payload from the the third party applications, using MLLP protocol. Once payload is received, the MLLP listener sends back the acknowledgment and redirects payload to the designated flow. If flow is scheduled, it is getting automatically executed each time the payload is received.

To create an HL7 MLLP Listener you need to specify the following parameters:

Formats

When data objects are involved, for example when working with web services, you need to describe all required formats used for data exchange. You can create a format on the Formats page.

Important: when defining a data exchange format you are not defining fields and validation rules (which is a part of creating a flow). Instead, you are defining a format metadata, such as: what delimiter is used for CSV or what name space is used for XML.

Step 1 Open Connections page and select Formats tab.

Step 2 To create a new format, on the Formats page click the + button. Then click the corresponding format type in the opened box.

Formats

To quickly find the corresponding type you can enter a part of the type name in the search box. After this only the types, having the entered string in their names, will be displayed. To filter formats by a category, you can click the corresponding format category from the list to the left of format types.

Step 3 After format type has been selected, continue by entering format parameters.

Format parameters

Step 4 After format has been created you can save it by clicking the Save button.

All saved formats are displayed in a grid on the Formats page and can be quickly filtered by name and type. Formats can be duplicated or deleted right from the grid.

All Formats

CSV

CSV (or coma separated values) is a one of the most commonly used data exchange formats. In Integrator you can actually define what character is used as a separator between values and lines, as well as other parameters:

Fixed Length Text

In fixed-length text files each field starts and ends at the same place in every record. Otherwise they are the same as CSV. Below is a list of properties for the Fixed Length Text format:

JSON

JSON is a data exchange format widely used as a payload and response in the REST-based web services. Integrator can parse and create practically any JSON document. Below are the available parameters for the JSON format:

JSON Data Set

JSON Data Set is a JSON document, which includes metadata, such as column names and types, as well as data set name. Since it has a normalized and well known structure it can be used to return data in the predictable format, for example when building REST APIs in the Integrator.

Example of the JSON data set:

{
    "name": "users",
    "metadata": [{
        "name": "firstName",
        "type": "12",
        "native_type": "VARCHAR",
        "nullable": "true"
    }, {
        "name": "lastName",
        "type": "12",
        "native_type": "VARCHAR",
        "nullable": "true"
    }, {
        "name": "test",
        "type": "2003",
        "native_type": "ARRAY",
        "nullable": "true"
    }, {
        "name": "age",
        "type": "2",
        "native_type": "NUMBER",
        "nullable": "true"
    }, {
        "name": "streetAddress",
        "type": "12",
        "native_type": "VARCHAR",
        "nullable": "true"
    }, {
        "name": "city",
        "type": "12",
        "native_type": "VARCHAR",
        "nullable": "true"
    }, {
        "name": "state",
        "type": "12",
        "native_type": "VARCHAR",
        "nullable": "true"
    }, {
        "name": "postalCode",
        "type": "12",
        "native_type": "VARCHAR",
        "nullable": "true"
    }, {
        "name": "phoneNumbers",
        "type": "2003",
        "native_type": "ARRAY",
        "nullable": "true"
    }],
    "data": [{
        "Duke",
        "Java",
        "TEST": {
            "name": "TEST",
            "metadata": [{
                "name": "who",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }, {
                "name": "why",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }],
            "data": [{
                "WHO": "duke",
                "WHY": "because"
            }]
        },
        18",
        100 Internet Dr",
        "JavaTown",
        "JA",
        "12345",
        "PHONENUMBERS": {
            "name": "PHONENUMBERS",
            "metadata": [{
                "name": "phoneNumbers_type",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }, {
                "name": "phoneNumbers",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }],
            "data": [{
                "PHONENUMBERS_TYPE": "Mobile",
                "PHONENUMBERS": "111-111-1111"
            }, {
                "PHONENUMBERS_TYPE": "Home",
                "PHONENUMBERS": "222-222-2222"
            }]
        }
    }, {
        "Joe",
        "Doe",
        "TEST": {
            "name": "TEST",
            "metadata": [{
                "name": "who",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }, {
                "name": "why",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }],
            "data": [{
                "joe",
                "he is good"
            }]
        },
        "32",
        "100 Main Str",
        "Pittsburgh",
        "PA",
        "15217",
        "PHONENUMBERS": {
            "name": "PHONENUMBERS",
            "metadata": [{
                "name": "phoneNumbers_type",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }, {
                "name": "phoneNumbers",
                "type": "12",
                "native_type": "VARCHAR",
                "nullable": "true"
            }],
            "data": [{
                "Fax",
                "333-111-1111"
            }, {
                "Test",
                "PHONENUMBERS": "444-222-2222"
            }, {
                "Home",
                "555-111-1111"
            }]
        }
    }]
}

XML

Integrator can automatically parse and create practically any XML document (support for XML attributes is currently limited). Below are the available parameters:

<?xml version="1.0" encoding="UTF-8"?>

 <Patient>
    the rest of the xml document
 </Patient>
 <Patient xmlns="http://www.meditlive.com/datacontract" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    the rest of the xml document
 </Patient>

Example (no Row Tag):

<Patient>
   <Patient>
   </Patient>
   <Patient>
   </Patient>
   <Patient>
   </Patient>
<Patient>

Example (Row Tag set to ROW):

<Patient>
   <ROW>
   </ROW>
   <ROW>
   </ROW>
   <ROW>
   </ROW>
<Patient>

Example (no Null Attribute):

<MiddleName />

Example (Null Attribute set to i:nil="true"):

<MiddleName i:nil="true"/>

Example (no Value Attribute):

<FirstName>Joe</FirstName>

Example (Value Attribute set to value):

<FirstName value="Joe"/>

XML Data Set

XML Data Set is an XML document, which includes metadata, such as column names and types, as well a data set name. Since it has a normalized and well known structure it can be used to return data in the predictable format, for example when building REST APIs in the Integrator.

Example of the XML data set:

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <name>EMPLOYEE_WEBROWSET</name>
    <metadata>
        <col name="EMPNO" native_type="CHAR(6)" nullable="false" type="1"/>
        <col name="FIRSTNME" native_type="VARCHAR(12)" nullable="false" type="12"/>
        <col name="MIDINIT" native_type="CHAR(1)" nullable="true" type="1"/>
        <col name="LASTNAME" native_type="VARCHAR(15)" nullable="false" type="12"/>
        <col name="WORKDEPT" native_type="CHAR(3)" nullable="true" type="1"/>
        <col name="PHONENO" native_type="CHAR(4)" nullable="true" type="1"/>
        <col name="HIREDATE" native_type="TIMESTAMP" nullable="true" type="93"/>
        <col name="JOB" native_type="CHAR(8)" nullable="true" type="1"/>
        <col name="EDLEVEL" native_type="SMALLINT(5)" nullable="false" type="5"/>
        <col name="SEX" native_type="CHAR(1)" nullable="true" type="1"/>
        <col name="BIRTHDATE" native_type="DATE" nullable="true" type="91"/>
        <col name="SALARY" native_type="NUMERIC(9,2)" nullable="true" type="2"/>
        <col name="BONUS" native_type="NUMERIC(9,2)" nullable="true" type="2"/>
        <col name="COMM" native_type="NUMERIC(9,2)" nullable="true" type="2"/>
        <col name="START" native_type="TIME" nullable="true" type="92"/>
    </metadata>
    <data>
        <row>
            <value>000010</value>
            <value>CHRISTINE</value>
            <value>I</value>
            <value>HAAS</value>
            <value>A00</value>
            <value>3978</value>
            <value>01011995</value>
            <value>PRES    </value>
            <value>18</value>
            <value>F</value>
            <value>08241963</value>
            <value>152750.00</value>
            <value>1000.00</value>
            <value>4220.00</value>
            <value>09:00</value>
        </row>
        <row>
            <value>000020</value>
            <value>MICHAEL</value>
            <value>L</value>
            <value>THOMPSON</value>
            <value>B01</value>
            <value>3476</value>
            <value>10102003</value>
            <value>MANAGER </value>
            <value>18</value>
            <value>M</value>
            <value>02021978</value>
            <value>94250.00</value>
            <value>800.00</value>
            <value>3300.00</value>
            <value>09:30</value>            
        </row>
    </data>
</dataset>

Excel

Integrator can read and write Microsoft Excel documents in the XLS and XLSX formats. Both formats share the same properties:

HL7 2.x

HL7 is a messaging specification for healthcare information systems. Read more at https://www.hl7.org. Integrator can read and write HL7 messages in the 2.x format (support for version 2.7 is currently limited). Below are the available properties for the HL7 2.x messages:

/* this code replaces all spaces in a message on underscore character */
message = message.replace(" ", "_");

HL7 FHIR

HL7 FHIR is a new version of the data exchange format for healthcare information systems. Read more at https://www.hl7.org/fhir/. Integrator can read and write HL7 messages in all currently available FHIR versions and encodings (XML and JSON). Below are the available properties for the HL7 FHIR messages:

/* this code replaces all spaces in a message on underscore character */
message = message.replace(" ", "_");

PDF

Integrator can write PDF documents but cannot read. Below are the available properties for the PDF format:

HTML

Integrator can write HTML documents but cannot read. Below are the available properties for the HTML format:

Data Integration Flows

Integrator supports the following data integration scenarios:

In Integrator, data integration scenarios are defined as flows. Flows can be run manually or scheduled for automatic execution. You can manage flows on the Flow Builder page: run, create, edit, clone, import, export or delete flows. The Flow Statistics page displays detailed information about the flow and its run history.

Creating and Managing Flows

In most cases, before you can start creating flows, you need to create all required connections and formats.

Step 1 To create a new flow, on the Flow Builder page click the + button. Then click the corresponding flow type in the opened box.

Flows

To quickly find the corresponding type you can enter a part of the type name in the search box. After this only the types, having the entered string in their names, will be displayed. To filter flows by a category, you can click the corresponding flow category from the list to the left of flow types.

Step 2 After a flow type has been selected continue by defining flow's transformations. To add a new transformation click the + button.

Transformations can be modified, deleted, and re-arranged using drag and drop.

Most of the transformations require FROM and TO connections and formats, as well as names of the FROM and TO objects. Objects could be file names, database tables, etc.

Flow transformations

Step 3 For each transformation you will need to define mapping and parameters.

Start defining mapping by clicking the MAPPING button in the grid which contains transformations.

Continue in the opened box by creating a per-field mapping, either automatically (by clicking the Create Mapping button), or manually by adding field one by one using the + button. You can modify mapping at any time. You can also re-arrange fields using drag and drop.

Transformation mapping

Step 4 After flow has been created you can save it by clicking the Save button.

All saved flows are displayed in a grid on the Flow Builder page and can be quickly filtered by name and type. Flows can be duplicated or deleted right from the grid.

All Flows

Export and Import Flows

In Integrator you can export any flow to the file saved locally, and later import it from the file. When you export the flow Integrator also exports all related connections and formats. Export/Import is especially useful when you wish to migrate flows from one instance of the Integrator to another, or copy flows between different accounts.

Transformations

Typical transformation includes a source (FROM), and a destination (TO). Source and destination can be any data source supported by the Integrator: tables in the relational database, sql statements, files, web services, API endpoints, social media web sites.

It is required to select a connection for the source and destination. Format is only required for the transformations with data objects, such as web services, flat and structured files.

The other required attributes are the source name and the destination name.

One flow can include multiple transformations. Transformations are typically executed in order, but can also be run out of order, if parallel execution is enabled.

Transformations can be re-ordered, disabled and deleted. Disabled transformation will not be executed.

All Flows

Most of the transformations include per-field mapping and additional parameters.

Mapping

In Integrator you can load data from tables in the relational database, sql statements, files, web services and social media web sites. In this topic we will refer to them as "source". The source object fields/columns will be referred as "source fields".

Data can be loaded into the tables in the relational database, files, web services, social media web sites. In this topic they will be referred as "destination", and their fields/columns will be referred as "destination fields".

On the mapping page of the flow editor you can map the source fields to the destination fields.

Important: if there is no mapping, the destination fields will be exactly the same as the source fields.

Mapping

Control elements:

The Fields grid (left to right):

{"Last Name"} + ',' + {"First Name"};

Parameters

In Integrator you can customize your FROM-TO transformation by entering parameters and adding high level transformations such as filter, validation, etc.

To open the Parameters section, while still in a MAPPING box, select the Parameters tab. The actual list of the available parameters and high level transformations is dynamic and depends on the type of the flow and source and destination connections.

Parameters

The following optional parameters are available for the most of the transformations:

This is a free-form JavaScript transformation, so there are multiple available techniques.

This transformation returns an object called dataSet and it must have the same reference as a source data set. For example you can not do the following:

var myDataSet = new DataSet();

....

dataSet = myDataSet;             

The following objects are available by name in the JavaScript code:

When this transformation is executed for each row (in opposite to the entire data set) the following objects are available:

The entire Toolsverse ETL Framework is available from the JavaScript. Read more about classes and interfaces of the ETL Framework.

To work with data sets you will need to import com.toolsverse.etl.common package into your JavaScript code. Other packages are available as well.

importPackage(com.toolsverse.etl.common);

Example of the code, which modifies fields in the current row:

if (dataSet.getFieldValue(currentRow, 'SALARY') > 50000) {
   dataSet.setFieldValue(currentRow, 'SALARY', 60000);
   dataSet.setFieldValue(currentRow, 'BONUS', 0);
}

Example of code, which transforms the entire data set:

importPackage(com.toolsverse.etl.common);

var stageDataSet = new DataSet();

var rows = dataSet.getRecordCount();

for (row = 0; row < rows; row++) {
    var record = dataSet.getRecord(row);

    stageDataSet.addField("FirstName", dataSet.getFieldValue(record, "firstName"));
    stageDataSet.addField("LastName", dataSet.getFieldValue(record, "lastName"));

    stageDataSet.addField("Age", dataSet.getFieldValue(record, "age"));
    stageDataSet.addField("City", dataSet.getFieldValue(record, "city"));
    stageDataSet.addField("State", dataSet.getFieldValue(record, "state"));
    stageDataSet.addField("ZipCode", dataSet.getFieldValue(record, "postalCode"));
    stageDataSet.addField("Addr1", dataSet.getFieldValue(record, "streetAddress"));
 }  

dataSet.setFields(stageDataSet.getFields());

dataSet.setData(stageDataSet.getData());

The source is a relational database

When the source is a relational database, the Integrator extracts data from the database table, view, synonym or executes user defined SQL query.

The user has 2 options:

Database is a source

When source is a relational database the following parameters and high level transformations are available:

You can conditionally fail entire flow:

if (dataSet.getFieldValue(currentRow, 'SUCCESS').toString() == 'error') {
   value = TaskResult.HALT;
} else {
   value = TaskResult.CONTINUE; 
}

or reject the whole dataset

if (dataSet.getFieldValue(currentRow, 'SUCCESS').toString() == 'error') {
   value = TaskResult.STOP;
} else {
   value = TaskResult.CONTINUE; 
}

or just reject the current row

if (dataSet.getFieldValue(currentRow, 'SUCCESS').toString() == 'error') {
   value = TaskResult.REJECT;
} else {
   value = TaskResult.CONTINUE; 
}

The destination is a relational database

When loading data into database Integrator either automatically generates and executes SQL DML statement (INSERT, UPDATE, DELETE or MERGE) or executes user-created SQL.

When destination is a relational database, the user has two options:

Destination query

The destination query can be more than just one SQL statement. Use ; to separate SQL statements.

In the example below, 2 queries are getting executed. Fields in { and } brackets are getting substituted on actual field values from the source.

Source query:

SELECT locust_staging.id,
       locust_update_id.hotlineeventid,
       (SELECT TOP 1 speciesid
        FROM   species
        WHERE  sp.options = species.DESCRIPTION)                                                                               AS species,
       recorded_time                                                                                                           AS calldatetime,
       CASE
         WHEN date_completed IS NULL
         THEN 2
         ELSE 1
       END                                                                                                                     AS eventstatusid,
       (SELECT TOP 1 officerid
        FROM   officers
        WHERE  ( ISNULL(officers.givenname,'') + ISNULL(officers.surname,'') ) IN (SELECT ISNULL(NAME,'') + ISNULL(surname,'')
                                                                                   FROM   staging_users
                                                                                   WHERE  current_user_id = staging_users.id)) AS officertakingcallid,
       GETDATE()                                                                                                               AS modifieddate,
       'ddc2'                                                                                                                  AS modifiedby
FROM   locust_staging
       INNER JOIN locust_update_id
       ON locust_staging.id = locust_update_id.id
       INNER JOIN (SELECT options,(ROW_NUMBER() OVER (ORDER BY (SELECT 100))) - 1 AS enum FROM staging_species) sp
       ON locust_staging.species = sp.enum

Destination query:

update hotlineevent
set calldatetime = {calldatetime}, 
    officertakingcallid = {officertakingcallid},
    modifieddate = {modifieddate},
    modifiedby = {modifiedby}
where hotlineeventid = {hotlineeventid};             

update locust_staging
set HotlineEventId =  {hotlineeventid}
where id = {id};

When destination is a relational database, the following parameters and high level transformations are available:

{Types}.equals('U') ? 'update' : 'insert';

The source is a file or API endpoint

When the source is a file or API endpoint it can be flat or structured file, response from the web service or social web site.

You define a source by selecting a source (FROM) connection and a source (FROM) format. Then you select or enter a source (FROM) object name:

Source is a data object

Important: just like when the source is a relational database, user has an option to enter an SQL query, which will be executed after data has been read from the source. For example, if source is a CSV file, user can write a query, which aggregates data in a file and creates a summary view. This view will be used to drive the destination.

Read more about executing SQL queries on any data set.

Source SQL

Important: if you source data object is a file, it is possible to work with wildcard files names. For example, the third party application is creating files with a name order_timestamp.csv in the outbound folder (order_010120171123.csv). The timestamp part of the file name is dynamic and is getting changed for each file created in the outbound folder. In Integrator it is possible to configure a source (FROM) connection so it will read the files by the wildcard name, for example, order_*.csv and will apply one of the availbale algorithms to get an actual file name, for example order_010120171123.csv.

When creating a file-based or cloud storage connection you simply:

  1. Set Files attribute to the wildcard, for example order_*.csv.
  2. Set Enable Wildcard File Name attribute to the one of the available algorithms. For example, if Files = order_*.csv and the oldest is selected, the Integrator will be always selecting the oldest file in the folder which matches the wildcard file name order_.*csv.

When source is a data object the following parameters and high level transformations are available:

For the filter you can either use a SQL-like expression:

   "Status" = 'success' or "code" = 0;

or just plain JavaScript:

   "Status" = 'success' || "code" = 0;

You can conditionally fail entire flow:

if (dataSet.getFieldValue(currentRow, 'SUCCESS').toString() == 'error') {
   value = TaskResult.HALT;
} else {
   value = TaskResult.CONTINUE; 
}

or reject the whole dataset

if (dataSet.getFieldValue(currentRow, 'SUCCESS').toString() == 'error') {
   value = TaskResult.STOP;
} else {
   value = TaskResult.CONTINUE; 
}

or just reject the current row

if (dataSet.getFieldValue(currentRow, 'SUCCESS').toString() == 'error') {
   value = TaskResult.REJECT;
} else {
   value = TaskResult.CONTINUE; 
}
lastname,firstname desc

The destination is a file or API endpoint

When the destination is a file or API endpoint it can be flat or structured file, payload for the API endpoint or social web site.

You define a destination by selecting a destination (TO) connection and a destination (TO) format. Then you select or enter a destination (TO) object name:

Important: when destination connection is a file storage - the new file is getting created each time the transformation is executed. If file already exist it will be overwritten.

Important: if you destination data object is a file, it is possible to create file names dynamically, by appending one of the the pre-defined suffixes. When creating a destination (TO) connection you simply select a value for the Add Suffix When Creating Files attribute. For example, if you select uuid as a suffix and original file name is order.csv, the Integrator will be creating files with the name dest_uuid.csv, where uuid is a globally unique identifier such as 21EC2020-3AEA-4069-A2DD-08002B30309D.

Destination is a data object

When destination is a data object the following parameters and high level transformations are available:

Web Services

In integrator you can connect to practically any web service as long as it is exposed as an HTTP endpoint.

There is no difference between working with web services and working with files stored on a server or on a cloud.

Step 1 Create HTTP connection that points to the API endpoint.

Step 2 Create format, which will be used as a payload for the request or returned as a part of the response. For REST based web service it is likely going to be a JSON and for SOAP - XML.

Step 3 Start creating a new flow in the Flow Builder page by clicking the + button and typing in web service.

Web Services Flow Types

Step 4 Select the flow type which most closely represents your use case.

Step 5 Continue by defining transformations, mapping and parameters.

Sending payload to HTTP endpoint

If there is no source to destination transformation, and you just want to send a payload to the HTTP endpoint, use flow type Send payload to HTTP connection.

Step 1 Create HTTP connection that points to the API endpoint.

Step 2 When creating connection, define Payload or skip to the next step.

HTTP payload

Step 3 Start creating a new flow in the Flow Builder page by clicking the + button and typing in http.

Send payload to HTTP endpoint

Step 4 Select the flow and enter optional payload. Note: if you already entered payload in the connection created in step 2 you don't have to enter payload here.

Important: you can use {tokens} as a part of the payload. Tokens will be automatically substituted at runtime.

Example:

{
  "HostName": "{host}",
  "DomainName": "{domain}",
  "FullyQualifiedDomainName": "{full_domain}",
  "Name": "{name}",
  "Description": "{description}"
}  

There are two options for setting tokens:

Option 1. Setting global values using JavaScript.

Option 2. Executing flow in SQL loop.

Working with Google Analytics

Integrator includes a native connector to the Google Analytics reporting API.

Using Google Analytics connector you can define request parameters, such as start and end dates, dimensions and metrics to include, etc. Then, you can build an Integrator flow, which exports results of the executed request to any of the supported destinations, such as file, database or another web service.

Examples of the Google Analytics flows can be found under the Google Analytics case studies.

Step 1 Create Google Analytics Connection.

Google Analytics Connection

When creating a connection, define the following properties:

Step 2 Authorize your own or the default Service Account Email to be able to access Google Analytics data for the property defined by Vied ID.

Step 3 Start creating a flow by opening Flow Builder window, clicking the + button and typing in Google Analytics in the search box:

Google Analytics Flows

Step 4 Continue by defining a transformation(s) where source (FROM) is a Google Analytics Connection created in a step 1, and a destination (TO) is a ether file, database or a web service.

Google Analytics to CSV flows

Step 5 Add mapping and parameters if needed.

Submitting data to Amazon MWS Feeds API

Step 1. Create Amazon MWS Feeds connection

Step 2. Create flow to submit data to Amazon MWS Feeds API.

Working with HL7 messages

In Integrator it is possible to receive, parse and send HL7 messages version 2.x and FHIR.

In most cases using of the JavaScript is required.

The HL7 2.x connector is based on the HAPI framework. The entire HAPI API is available from the JavaScript.

The HL7 FIHR connector is based on the the HAPI FIHR framework. As with HAPI 2.x the entire HAPI FIHR API is available from the JavaScript.

Examples of the HL7 flows can be found under the HL7 case studies.

Step 1 Create source (FROM) and destination (TO) connection.

At this time the HL7 messages can be send and received using:

Step 2 Create source (FROM) and destination (TO) format.

The format could be:

Step 3 Start creating a new flow in the Flow Builder page by clicking the Add Flow button and typing in "hl7".

HL7 Flow Types

Step 4 Select the flow type which most closely represents your use case.

Step 5 Continue by defining transformations, mapping and parameters.

The source is HL7 message

When creating a flow, when the source is HL7 message, you select one of the flow types which starts from "Read HL7 message".

Typical use cases:

When the source is HL7 message, the Integrator automatically parses it and creates dataSet.getActualData() object available from the JavaScript. The object contains parsed HL7 message with human readable attributes, representing segments and fields.

Read more about HL7 2.x domain model here. Look at Documentation->v2.x sidebar.

Read more about HL7 FIHR domain model here.

The JavaScript must be entered in the Preprocessing field in the Parameters page of the transformation:

HL7 Code

Example below demonstrates how to transform a source HL7 message into the destination data set:

// importing HL7 2.4 domain model
importPackage(Packages.ca.uhn.hl7v2.model.v24.message);
importPackage(Packages.ca.uhn.hl7v2.model.v24.segment);

// getting parsed message from the source
var message = dataSet.getActualData();
var pid = message.getPID(); 

// initializing the destination data set (required if destination is not HL7)
dataSet.init();

// adding fields and values to the destination data set from the HL7 message
dataSet.addField("Patient.PatientID", 
   pid.getPatientIdentifierList(0).getID().getValue());

dataSet.addField("Patient.TestNull",null);


dataSet.addField("Patient.Name.FirstName", 
   pid.getPatientName(0).getGivenName().getValue());

dataSet.addField("Patient.Name.LastName", 
   pid.getPatientName(0).getFamilyName().getFn1_Surname().getValue());

Code below is actually used to access segments and fields of the message:

var message = dataSet.getActualData();
var pid = message.getPID(); 

pid.getPatientIdentifierList(0).getID().getValue();
pid.getPatientName(0).getGivenName().getValue();
pid.getPatientName(0).getFamilyName().getFn1_Surname().getValue();

Also, as you can see, in order to work with parsed HL7 message the following two lines where added to the JavaScript code:

importPackage(Packages.ca.uhn.hl7v2.model.v24.message);
importPackage(Packages.ca.uhn.hl7v2.model.v24.segment);

They represent a domain model for the HL7 2.4.

Other versions are available as well. Look at Documentation->v2.x sidebar.

Creating acknowledgment for the source HL7 message

When HL7 message is received, in most cases it is required to send an acknowledgment.

Creating acknowledgment for the source HL7 message and sending it to any destination is as easy as:

Step 1 Create a transformation where source is a source HL7 message and destination is also HL7 message.

HL7 Acknowledgment

Step 2 In a Preprocessing field enter the following line:

dataSet.setActualData(dataSet.getActualData().generateACK());

HL7 Acknowledgment Code

The destination is HL7 message

When creating a flow, when the destination is HL7 message, you select one of the flow types which contains "create HL7 message".

Typical use cases:

To create a flow when the destination is an HL7 message you follow the same steps as when the source is an HL7 message.

The different is that, this time, the goal is to set dataSet.setActualData(message), where message is an object, representing HL7 message in memory.

Once again we are looking at HL7 2.x and FIHR domain models:

Below is an example of creating HL7 message from the source data set:

// importing HL7 2.4 domain model
importPackage(Packages.ca.uhn.hl7v2.model.v24.message);
importPackage(Packages.ca.uhn.hl7v2.model.v24.segment);

// creating and initializing the HL7 message
var message = new ADT_A01();
message.initQuickstart("ADT", "A01", "P");

// populating the MSH Segment
var mshSegment = message.getMSH();
mshSegment.getSendingApplication().getNamespaceID().setValue("Integrator");
mshSegment.getSequenceNumber().setValue("100");

// populating the PID Segment
var pid = message.getPID(); 
pid.getPatientName(0).getFamilyName().getSurname().
   setValue(dataSet.getFieldValue(currentRow, "Patient.Name.LastName", "."));
pid.getPatientName(0).getGivenName().
   setValue(dataSet.getFieldValue(currentRow, "Patient.Name.FirstName", "."));
pid.getPatientIdentifierList(0).getID().
   setValue(dataSet.getFieldValue(currentRow, "Patient.PatientID", "."));

// setting message as an actual data from the data set 
dataSet.setActualData(message);

The source and destination are HL7 messages

This is the most commonly used flow type. The flow receives an HL7 message from the source connection, sends acknowledgment to the sender, creates a new HL7 message, and sends it to the destination.

To create HL7->HL7 flow, you follow the same steps as before. The only difference is that you select Read HL7 message, create different HL7 message flow type from the list of the available HL7 flows:

HL7 to HL7

The actual preprocessing JavaScript code will look like a combination of the code when HL7 is a source and HL7 is a destination.

Event driven flows

The Integrator can be used as an Enterprise Service Bus, serving requests from the third-party applications. Basically you can build an API, without writing a single line of code, which can be used by other applications to send payload to the Integrator.

Typically, the third-party applications will be using HTTP to send a payload to the Integrator but other listeners are available as well.

To create a flow, which is triggered by the payload sent to the Integrator:

Step 1 Create an API user, which will be used to run the flow.

Step 2 Create a listener. Most likely you will be creating an HTTP listener.

Step 3 Create a format for the payload. Most likely you will be using JSON, XML, HL7 2.x or HL7 FIHR as a payload.

Step 4 Start creating a flow by selecting a flow type when the source is a file

Source is a File

Step 5 Continue by creating a transformation when the source connection (FROM) is a listener.

Listener is a source

Important: one listener can be used in only one flow, only one listener can be used in a single flow.

Step 6 Add mapping and parameters just like you would usually do.

Step 7 Since flow with a listener cannot be manually executed, it must be scheduled.

Important: the scheduled event-driven flows are executed when payload arrives, in opposite to the regular flows, executed at the specific time intervals.

Testing event driven flows in Integrator

Typically, the third party application will be sending a payload to the Integrator which will trigger an event driven flow.

To test a flow, without leaving the Integrator, you will need to create another flow: a sender.

Lets assume that you have an example payload somewhere in a file based or cloud storage. Lets also assume that you have already created and scheduled an event driver flow, and created an API user.

Step 1. Create a source connection and a format for the payload.

Step 2. Create a destination connection which sends a payload to the listener you have created when creating an event driven flow.

For example, lets assume that:

  1. You are running an Integrator on http://localhost:8080.
  2. The HTTP listener is used in the event driven flow.
  3. The URL Pattern for the listener configured as /hl7/patient.

If all of the above is true, you will be creating a destination HTTP connection for the sender flow with the following parameters:

Step 3. Create a transformation where the source (FROM) is a source connection and format you have created in step 1, destination connection is a connection you have created in step 2, and destination format = source format.

Sender

Read more about integrations and APIs.

Copy, move, rename, delete and zip/unzip files

Integrator can work with files directly. The following file-based operations are supported:

To create a flow which works with files:

Step 1. Start creating a flow by clicking the Add flow button on the Flow Builder page.

Step 2. In the opened box select Files. The following file based flows are available

File flows

Step 3. Continue by entering transformations and flow parameters.

File management

Create a File management flow when you want to copy, move, rename or delete the files or check number of files in the folder or create a folder(s).

Step 1. Create a source (FROM) and a destination (TO) connections, which can be one of the following:

Step 2. Select a File Management flow type from the list.

Step 3. Create a transformation where the source (FROM) is a location with a source files and a destination (TO) - is a location where files should be created (can be the same for rename operation).

Move files

Step 4. Select or enter the source and the destination file names in the Mapping box. Both can be wildcard names, such as *.csv.

Move file names

Step 5. Continue by specifying transformation parameters:

Important: when selecting FROM and TO use the following rules:

Copy

Choose source (FROM) and destination (TO) connections. Enter file name, or a wildcard file name, such as *.csv, in the source (FROM) field. Files from the source location (FROM connection) will be copied to the destination location (TO connection).

Move

Choose source (FROM) and destination (TO) connections (they can be the same). Enter file name, or a wildcard file name, such as *.csv, in the source (FROM) field. Files from the source location (FROM connection) will be moved to the destination location (TO connection).

Rename

Choose source (FROM) and destination (TO) connections (they can be the same). Enter file name, or a wildcard file name, such as *.csv, in the source (FROM) field. Enter new file name, or a wildcard file name, such as dest.*, in the destination (TO) field. Files from the source location (FROM connection) will be moved to the destination location (TO connection) and renamed in the process.

Delete

Choose source (FROM) connection. Enter file name, or a wildcard file name, such as *.csv, in the source (FROM) field. Files in the source location (FROM connection), which match the string entered in the FROM field, will be deleted.

Create Folder(s)

Choose source (FROM) connection. Enter name of the folder to create in the FROM field. If folder doesn't exist it will be created under the url/directory of the FROM connection.

Check Number of Files

Choose source (FROM) connection. Enter file name, or a wildcard file name, such as *.csv, in the source (FROM) field. System will calculate number of files which names are matching the FROM field, will compare it to the entered number of files, and will generate an exception if they are not equal.

Important: the File Management flow allow you to create multiple transformations, such as copy, move, rename, etc. within a single flow. You can also choose to use specific flow types, such as:

They contain less parameters and can be chained together using nested flow.

Zip/Unzip files

Create a Zip/Unzip flow when you want to zip files in a folder or unzip a file.

Step 1. Create a source (FROM) and a destination (TO) connections, which can be one of the following:

Step 2. Select a Zip/Unzip flow type from the list.

Step 3. Create a transformation where the source (FROM) is a location with a source files and a destination (TO) - is a location where files should be created.

Step 4.1. For zip operation enter files to zip (wildcard file names, such as *.csv are allowed) (FROM) and name of the zip file (TO).

zip

Step 4.2. For unzip operation enter name of the zip file (FROM) and the folder where the Integrator should unzip the files (TO).

Step 5. Continue by specifying transformation parameters:

Important: when selecting FROM and TO use the following rules:

Zip

Choose source (FROM) connection for the location of the files to ZIP, and destination (TO) connection for the location of the ZIP file. Enter file name or a wildcard file name, such as *.csv, in the FROM field for the files to ZIP. Enter name of the ZIP file in the TO field.

Unzip

Choose source (FROM) connection for the location of the ZIP file, and destination (TO) connection for the location where files from the ZIP will be copied. Enter ZIP file name in the FROM field.

Important: the Zip/Unzip flow allow you create multiple transformations, such as zip and unzip, etc. within a single flow. You can also choose to use specific flows, such as:

Merge CSV files

Use this flow type when you want to merge multiple CSV files into the single file.

Important: this flow can merge two or more CSV files with the different structure. The final CSV file will include a super set of all fields from all source files.

Step 1. Create FROM and TO connections (it is recommended to use the same connection for both).

Note: At this time only Server Storage connection type is supported. FTP, SFTP and S3 are not supported.

Step 2. Create FROM and TO formats, which must be CSV.

Step 3. Select Merge CSV files flow type from the list.

Merge CSV files

Step 4. Create transformation where the source connection (FROM) points to the files to merge, and the destination connection (TO) - to the location where "merged" file should be created. The FROM and TO formats must be created in the step 2.

Step 5. Enter a wildcard file name, such as *.csv, in FROM and the name of the destination file in the TO field.

Step 6. Continue by specifying transformation parameters:

Executing any JavaScript

In Integrator it is possible to execute any JavaScript as a separate flow.

Since JavaScript code in the Integrator has full access to the Toolsverse ETL Framework, it is possible to create and execute very complex scripts, which work with files and framework objects outside of the Integrator sandbox.

Step 1 Start creating a flow by typing javascript in the Select Flow Type box:

Javascript flow

Step 2 Continue by entering a JavaScript in the Parameters tab

Javascript flow parameters

Step 3 Enter other parameters if needed (optional):

Rules when working with JavaScript

The following objects are available by name in the JavaScript code:

The entire Toolsverse ETL Framework is available from the JavaScript. Read more about classes and interfaces of the ETL Framework.

To work with elements of the framework you need to add importPackage(package.name) to the JavaScript code.

importPackage(com.toolsverse.etl.common);

Executing any SQL

In Integrator it is possible to execute any SQL statement (including DDL) or a list of the ; separated SQL statements, outside of the regular source to destination transformation.

Step 1 Start creating a SQL flow by creating a database connection.

Step 2 Continue by opening the Flow Builder window, clicking the Add flow button and typing execute sql in the Select Flow Type box:

sql flow

Step 3 Select a destination connection created in step 1.

Step 4 Select Parameters tab end enter SQL to execute and other parameters:

sql parameter

Available parameters:

Sending emails

In Integrator it possible to send an email notification when flow is been automatically executed by the scheduler.

It is also possible to configure an outbound email connection and use it as a destination in any flow which supports transmitting data objects via email.

Last, but not least, it is also possible to send email as a part of the separate flow.

Step 1 Start creating an Email flow by creating an outbound email connection.

Step 2 Continue by opening the Flow Builder window, clicking the Add flow button and typing email in the Select Flow Type box:

email flow

Step 3 Select a destination connection created in step 1.

Step 4 Select Parameters tab end enter email parameters:

Working with Facebook

Read how to pull data from Facebook and post to Facebook.

Working with Twitter

Read how to pull data from Twitter and post to Twitter.

Nested flows with conditions and loops

In Integrator it is possible to create flows of the different types:

There is an easy way to combine flows of the different type into the single nested flow, and execute inner flows one by one in a pipeline, or in parallel. It is also possible to execute inner flows conditionally or in a loop.

Using this technique you can create very complex data integration scenarios, when one flow feeds another, or when flows are executed multiple times based on a condition, or when flows are not executed at all if conditions are not met.

Last but not least, when executing inner flows in parallel you can archive a very high level of performance.

Assuming that you have already created flows that you want to combine into the single nested flow:

Step 1 Start creating nested flow be opening Flow Builder window, clicking the Add flow button and typing Nested flow in the "Select Flow Type" box:

nested flow

Step 2 Continue by adding flows to the nested flow by clicking the Add flow button.

inner flows

Step 3 For each inner flow you can specify parameters by clicking the pen button. Available parameters:

Rules when working with JavaScript for Condition and Loop

The Condition JavaScript must set the value variable in the last line to true or false:

value = condition;

The Loop is running while the Loop JavaScript returns not null value in the last line.

value = condition ? true : null;

The following objects are available by name in the JavaScript code:

Additionally, the following objects are available for the Loop JavaScript:

value = evals <= 40 ? evals : null;

The entire Toolsverse ETL Framework is available from the JavaScript. Read more about classes and interfaces of the ETL Framework.

To work with the elements of the framework you need to add importPackage(package.name) to the JavaScript code.

importPackage(com.toolsverse.etl.common);

Very complex data integration flows

You can create almost any data integration flow in the Integrator. However, there are currently limits on what you can do.

For example, the ETL Framework, which is an "engine" for the Integrator, supports very high level transformations, such as joins, set operations, pivoting, normalization and de-normalization. These transformations are handled as "tasks" attached to the sources and destinations. Currently you cannot easily create a flow in the Integrator which includes one of these transformations.

This is why the Integrator includes one special flow type, called Execute scenario in XML format.

Basically, you create an XML flow using either Data Explorer or ETL Framework, save it as XML file and upload file into the Integrator. Since internally integrator translates flows into the XML based ETL language it can execute any flow which is already in this format.

Read more about Data Explorer and ETL Framework here.

To create data integration flow in XML format:

Step 1 Create a flow in the XML format in Data Explorer or ETL Framework and test it. Used named connections only (in opposite to the default source and dest connections).

Step 2 Open Flow Builder window, click the Add flow button and type xml format in the "Select Flow Type" box:

xml flow

Step 3 Add all connections and formats required for the flow. Use connection names from step 1.

xml flow connections

Step 4 Select tab Parameters and copy and paste your XML flow, created in the step 1, into the Scenario field.

xml flow parameters

Manually Executing Flows

You can manually execute flow at any time by opening the Flow Builder window and clicking the Run button.

run flow

There are two conditions when you cannot manually execute the flow:

  1. Flow is already running.
  2. Flow is event driven.

Once flow has been executed (success or error) the Integrator displays a popup notification.

The status of the last execution is displayed in the Status column and includes a link to the Flow Statistics Dashboard.

Flow Statistics

The Flow Statistics dashboard displays stats for the last time flow has been executed, as well as a run history.

It is available from the Flow Builder and from the Scheduler.

flow stats

The Metrics sections displays Records Metrics, such as number of records extracted and loaded:

records metrics

As well as any applicable File Metrics:

file metrics

When flow execution fails, the Flow Statistics dashboards includes a link to the error:

link to flow error

If you click on a link the popup box with a detail information about the error will open:

flow error

In addition to the current stats the Flow Statistics dashboard includes a History of runs. Last 50 runs are available by default, with an option to get more:

flow history

Scheduling Flows

In Integrator you can schedule flows for automatic execution.

To schedule the flow open the Scheduler window

scheduled flows

and click the Schedule button:

schedule

In this window you can also:

Scheduling periodically executed flows

The majority of flow types can be scheduled to be automatically executed "on a schedule" (at the configured time).

For periodically executed events the following parameters are available:

time schedule

Examples of scheduling using cron pattern

Every day, every 4 hours

  1. Select time period
  2. Select Every day
  3. Select 0,8,8,12,16,20,23 for hours and 0 for minutes

every 4 hours

Every Monday, Wednesday and Friday at 23:59

  1. Select time period
  2. Select Every week
  3. Select Monday, Wednesday and Friday for days and 23:59 for hours and minutes

every other day

Every day at 1:00

  1. Select time period
  2. Select Month
  3. Select Every day of the month
  4. Select 1:0 for hours and minutes

every day

Scheduling event-driven flows

When scheduled, the event driven flows are executed "on event", typically when the payload arrives. For example when a third party application calls the Integrator HTTP endpoint linked to the HTTP listener.

For the scheduled event-driven flows the following parameters are available:

Important: after event-driven flow has been scheduled you will need to wait one minute before you can start using it. Reason: listeners need to be activate and it usually takes ~ 1 minute.

Messages

When inbound messages are getting processed by the listeners, the Integrator stores them all in the database in the original raw format.

For example, when a third part application calls the Integrator endpoint, and sends a payload in the JSON format, the payload is getting stored in its original form in the database.

Lets call a payload, received by the Integrator, a message.

Messages can be queried, displayed and viewed in the Messages window:

messages

To view the message in a raw format click the icon on the left in the grid.

raw message

Statistics and Audit-Trail

For the security and audit purposes the Integrator records and keeps all the important events in the database.

To view daily and monthly statistics open the Statistics window:

statistics

To query and view the audit trail events, click the Audit Trail tab in the Statistics window. Important: only admin users can see the audit trail events.

audit trails

Managing Users

If you are an admin you can create and manage the Integrator users.

To create and manage users open the Users Management window.

users and roles

The users' profile:

user profile

Important: when creating a new user, the password is getting generated automatically and is getting sent to the email in user's profile, so make sure you are using a real email.

When creating a user the following roles are available:

API User

API User is a special type of user which sees nothing and used for authenticating API calls to the Integrator's API endpoints, such as HTTP Listener API.

To create an API user:

Important: please use a real email when creating an API user.

Once API user is created the email in the profile will receive a notification with a temporary password.

Please login to the Integrator with API user credentials and reset the password.

User Profile

Reseting Password

To reset a password:

Displaying user profile

profile

Changing User Preferences

preferences

Available options:

Switching tenants on the fly

In the super-admin mode, user can see all flows, connections, formats and schedules, available for all users. In Integrator it is possible to login into the particular tenant on behalf of the super-admin user.

Important: this option is available only for the super admin user, in the environment with tenants. Please contact Etlworks or your system administrator for more details.

To switch the tenant:

tenant

After the switch, super-admin user will be logged into the Integrator as a tenant administrator. The tenant's name will be added to the Tenant link in the top menu.

selected tenant

Important: to switch back to the super-admin mode:

clear tenant selection

Configuring Integrator

Super Admin users can configure the Integrator.

To configure the Integrator open the Configuration window.

configuration

The following options are available:

Working behind corporate firewall

When Integrator installed on a cloud it can access databases, web services and APIs, as well as data in file and cloud based storages, as long as they are accessible from the Internet.

It is not always the case, especially for corporate databases.

Q. My database is in a corporate network, behind the firewall. how can integrator access it from the cloud?

A. We install and configure a zero-maintenance background service called Integration Agent right in your network, which talks to your corporate database and to the Integrator on the cloud.

Q. How does the Integration Agent communicate with a databases and APIs in a corporate network?

A. Integration Agent works behind the corporate firewall, so it communicates with databases and APIs directly. It uses JDBC to talk to the databases and HTTP to get and push data to REST and SOAP APIs.

Q. How does the Integration Agent, working behind the corporate firewall, communicate with Integrator on a cloud?

A. Integration Agent can push data to the secure cloud file storage, such as Amazon S3, which is accessible by Integrator, or it can push data directly to the Integrator REST API. For more details see event driven flows.

Q. Is it possible to load data into my corporate database or API using the integration agent?

A. The Integration Agent can load data into the database or API just as well as extract it.

You can read more about Integration Agent here.