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.

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. Database is available from the Internet.
  2. IP address for the host integrator.etlworks.com is white-listed.

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

Tip: If your database is not on a list above, 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 and specify connection parameters.

To connect to the relational database you need to specify the following parameters:

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

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 (Amazon S3 and Google Cloud)

Integrator supports the following cloud storages:

Important: you must have an Amazon Web Services (AWS) or Google Cloud account in order to access data stored in a cloud storage. 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.

To connect to the Cloud Storage you need to specify the following parameters:

File Storages (file, FTP, SFTP)

Integrator supports the following file storages:

Read more about local file storage.

To connect to the File Storage you need to specify the following parameters:

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:

Web Services

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.

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 reads data from the source table, view, synonym or 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 the destination is a relational database, the Integrator reads data from the source and INSERTS (default), UPDATES, DELETES or MERGES records in the destination object (table, view, synonym).

Just like when the source is a relational database, 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 data object (file, API endpoint, etc.)

By definition, when the source is a data object it could be anything, except relational database: flat or structured file, response from the web service, 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 data object (file, API endpoint, etc.)

Just like when the source is a data object, when the destination is a data object, it could be anything, except relational database: flat or structured file, input of the web service, 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:

Working with nested data objects

The input and output of the different web services and APIs can range from simple and flat to highly nested and complex.

Example of the nested data object:

[
  {
    "firstName":"Duke",
    "lastName":"Java",
    "test":[
      {
        "who":"duke",
        "why":"because"
      }
    ],
    "age":18,
    "streetAddress":"100 Internet Dr",
    "city":"JavaTown",
    "state":"JA",
    "postalCode":"12345",
    "phoneNumbers":[
      {
        "type":"Mobile",
        "phoneNumber":"111-111-1111"
      },
      {
        "type":"Home",
        "phoneNumber":"222-222-2222"
      }
    ]
  }
]

In the relational databases and flat files (such as CSV, Excel, etc.) the data represented as data sets, with columns and rows:

EMPNO   FIRSTNME    MIDINIT LASTNAME    WORKDEPT    PHONENO
10      CHRISTINE   I       HAAS        A00         3978
20      MICHAEL     L       THOMPSON    B01         3476
30      SALLY       A       KWAN        C01         4738
50      JOHN        B       GEYER       E01         6789
60      IRVING      F       STERN       D11         6423
70      EVA         D       PULASKI     D21         7831

Now, lets say that PHONENO column is an array of phones such as below:

"PHONENO":[
  {
    "type":"Mobile",
    "phoneNumber":"111-111-1111"
  },
  {
    "type":"Home",
    "phoneNumber":"222-222-2222"
  }
]

As you can see it can be easily represented as a data set:

type    phoneNumber 
mobile  111-111-1111
home    222-222-2222      

Now, if we can say that PHNONENO column has an ARRAY data type and each PHONENO value in a row is a data set, we got ourselves a way to encode nested data objects.

This is exactly how nested data objects are represented in the Integrator.

There is no limit on "nestedness" in the Integrator, so each column anywhere in the nested object can be a nested object itself.

In the Integrator, the inner data sets (PHONENO in the example above) are also called dimensions. The outer data set (a root nested object) is also called a driving data set.

Integrator can read and write nested data objects, encoded in all supported data exchange formats such as JSON, XML, HL7, etc.

Important: by default Integrator is trying to use streaming, which is another way of saying "please keep as little data in memory as possible". Generally speaking, streaming is faster and has very low memory footprint. Unfortunately, streaming is not always possible, specifically when dealing with complex nested data objects, typically returned by web services. In such a case it is always a good idea to disable the streaming.

  1. Create a transformation.
  2. Select Parameters tab.
  3. Uncheck Stream Data.

Do not stream

Let's go through the different usage examples:

The Source (FROM) is a nested data object.

As was already mentioned, the output from the most of the APIs and web services is nested. For example, when ORDER is returned from the Salesforce API, it contains information about order itself, as well as all items included in the order.

When Integrator reads a nested data object it parses it and represents fields in the three-like structure:

Nested data object

As you can see, on a figure above, the dimensions (inner data sets) are the test and the phoneNumbers.

The initial mapping was created by clicking on the Create Mapping button.

Using SQL with nested source data object.

Read more how to extract data from the nested source data object using SQL.

The Source (FROM) is a nested XML.

When the Source (FROM) is a nested XML you can use XSL style-sheet to transform it to any desired format.

Read more about using XSL stylesheets to transform XML

The Source (FROM) is a nested data object and the Destination (TO) is a flat data object.

This is a most common use case. You take a nested output from the web service or API, you transform it to the flat data structure and you store it in the relational database or flat file.

In Integrator the "nested to flat" mapping can be done complete using drag and drop, no coding involved.

Step 1 As usual, you create a transformation by defying a source (FROM) and a destination (TO). The source is a nested data object (for example users.json) and the destination is a flat file (for example users.csv).

Step 2 You define a mapping by clicking the Create Mapping button. The Integrator reads the source nested data object and creates a mapping automatically. Important: if source object is not available when you create a flow, you can "design" it yourself using a combination of the Add field mapping, Mark this field as a child and the Move child field one level up buttons.

Step 3 You map fields in the source (FROM) to the fields in the destination (TO). It is recommend to exclude the parent field but keep the child fields. In the example above the parent fields test and phoneNumbers are excluded. As the a result, the following flat data set will be created:

firstName   lastName    who    why    age   streetAddress   city   state   postalCode   phoneNumber_type   phoneNumber

You may ask, what will happen if there are multiple phone numbers for the single user. If we will just map fields as in the example above, the Integrator will only get a first phone number, and will skip all others.

firstName   lastName    who    why      age  ....  phoneNumber_type   phoneNumber
Duke        Java        duke   because  18   ....  Mobile             111-111-1111

So, what do you if you want to keep all the data from the source nested object?

The Source (FROM) is a nested data object and the Destination (TO) is a flat data object that contains all the fields from the driving data set and all the fields from the dimension (inner data set).

The Integrator supports technique called "denormalization" or extracting dimension.

When the source (FROM) data set is a nested object, the following transformation parameters are available:

So, if we keep reusing "users" example above, to extract all phone numbers, together with user data, we will do the following:

Step 1 Skip the mapping all together.

Step 2 In the Parameters tab enter:

As a result we will get the following flat data set:

firstName   lastName    age  ....  phoneNumber_type   phoneNumber
Duke        Java        18   ....  Mobile             111-111-1111
Duke        Java        18   ....  Home               222-222-2222

This technique works perfectly well if you have just one dimension (inner data set). What to do if there are multiple dimensions?

The Source (FROM) is a nested data object and the Destination (TO) is the multiple flat data objects.

If you want to keep all the data from the source nested object, including data in all dimensions, you need to "normalize" the nested data object across multiple linked flat data objects. Just like relational databases do.

If you would be designing a database for the users example above you would be typically creating the following tables:

Users

This is exactly what Integrator can do for you automatically.

Step 1 You start by adding a new flow and typing in nested in "Select Flow Type" box.

Step 2 You choose either "Extract nesting data set and create staging files" or "Extract nested dataset and create staging tables" depending on what is your destination.

Step 3 You can still do a per-field mapping if needed.

Step 4.1 When destination is a database you can specify the following parameters:

Step 4.2 When destination is a file you can specify the following parameters:

The Integrator uses the following rules when it creates and populates staging tables and files:

The destination (TO) is a nested data object.

All techniques above work if the destination (TO) is a flat object or multiple flat objects. It covers 99% of the cases when we are getting data from the APIs or web services.

What if we need to push data to the APIs or web services? Or just create a nested XML or JSON document?

If your destination object (TO) is exactly the same as your source object (FROM), in terms of the structure - we are done. If there is no mapping and you are just converting from one format to another (for example from the XML to JSON), you just specify your source and destination connections and formats and let the Integrator do the magic.

If there is a mapping, or if the source structure is not equal to the destination structure, you will need to use a transformation called Transformation Rules, together with enabling a flag Execute Transformation rules after extract.

Both are available in the Additional Parameters tab:

Transform data set

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

At the end of the day, 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:

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);

Last but not least, there are a lot of helper methods in the framework so, typically, you don't need to write a complicated code.

Below is an example of transforming JSON document

[
  {
    "firstName":"Duke",
    "lastName":"Java",
    "test":[
      {
        "who":"duke",
        "why":"because"
      }
    ],
    "age":18,
    "streetAddress":"100 Internet Dr",
    "city":"JavaTown",
    "state":"JA",
    "postalCode":"12345",
    "phoneNumbers":[
      {
        "phoneNumbers_type":"Mobile",
        "phoneNumbers":"111-111-1111"
      },
      {
        "phoneNumbers_type":"Home",
        "phoneNumbers":"222-222-2222"
      }
    ]
  },
  {
    "firstName":"Joe",
    "lastName":"Doe",
    "test":[
      {
        "who":"joe",
        "why":"he is good"
      }
    ],
    "age":32,
    "streetAddress":"100 Main Str",
    "city":"Pittsburgh",
    "state":"PA",
    "postalCode":"15217",
    "phoneNumbers":[
      {
        "phoneNumbers_type":"Fax",
        "phoneNumbers":"333-111-1111"
      },
      {
        "phoneNumbers_type":"Test",
        "phoneNumbers":"444-222-2222"
      },
      {
        "phoneNumbers_type":"Home",
        "phoneNumbers":"555-111-1111"
      }
    ]
  }
]

into XML (or any other format, which supports "nestedness")

<?xml version='1.0' encoding='UTF-8' ?>
<Users>
     <USER>
          <Name>
              <FirstName>Duke</FirstName>
              <LastName>Java</LastName>
          </Name>
          <Age>18</Age>
          <City>JavaTown</City>
          <State>JA</State>
          <ZipCode>12345</ZipCode>
          <Addr1>100 Internet Dr</Addr1>
          <Phone>
               <Phone>
                    <Type>Mobile</Type>
                    <Num>111-111-1111</Num>
               </Phone>
               <Phone>
                    <Type>Home</Type>
                    <Num>222-222-2222</Num>
               </Phone>
          </Phone>
     </USER>
     <USER>
          <Name>
              <FirstName>Joe</FirstName>
              <LastName>Doe</LastName>
          </Name>
          <Age>32</Age>
          <City>Pittsburgh</City>
          <State>PA</State>
          <ZipCode>15217</ZipCode>
          <Addr1>100 Main Str</Addr1>
          <Phone>
               <Phone>
                    <Type>Fax</Type>
                    <Num>333-111-1111</Num>
               </Phone>
               <Phone>
                    <Type>Test</Type>
                    <Num>444-222-2222</Num>
               </Phone>
               <Phone>
                    <Type>Home</Type>
                    <Num>555-111-1111</Num>
               </Phone>
          </Phone>
     </USER>
</Users>

Example:

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("Name.FirstName", dataSet.getFieldValue(record, "firstName"));
    stageDataSet.addField("Name.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"));

    var phones = dataSet.getFieldValue(record, "phoneNumbers");

    if (phones != null && phones.getRecordCount() > 0) {
        var phonesCount = phones.getRecordCount();

        for (phoneRow = 0; phoneRow < phonesCount; phoneRow++) {
           stageDataSet.addField("Phone.Type", 
              dataSet.getFieldValue(record, "phoneNumbers.phoneNumbers_type", '.', phoneRow));
           stageDataSet.addField("Phone.Num", 
              dataSet.getFieldValue(record, "phoneNumbers.phoneNumbers", '.', phoneRow));             
        }  
    }  
}  

dataSet.setFields(stageDataSet.getFields());

dataSet.setData(stageDataSet.getData());

Working with Web Services

In integrator you can connect to practically any API or 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 locally 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 Add Flow 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.

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.

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.

Working with 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 Local File 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 ; separated SQL statements, outside of regular source->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:

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

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.

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.