Overview

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

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

Glossary

How to start

With Etlworks Integrator, you can create data integration flows and then run them manually or schedule them 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 describe the input and output data objects. This step is not required when working with relational databases.

Step 3 Create a Flow by selecting a predefined flow type from the gallery, input and output connections, and formats, thus defining the mapping between the 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 the Flow to be executed periodically or to be triggered by events.

User Interface

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

user interface

  1. The Left navigation bar contains menu items, such as Connections, Form Builder, etc.
  2. The Grid displays Connections, Formats, Listeners, Flows, Schedules, etc.
  3. The Top navigation bar contains User Preferences, About, Help and Log Out.
  4. Filters and Tags.
  5. Tabs.
  6. There are Grid control elements, such as Edit, Delete, Duplicate, etc.

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

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

Using Filters

To quickly find Integrator's resources, such as Connections, Flows, Formats, Schedules, etc. you can enter all or part of the name into the Filter box. After this, only the resources having that 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 section 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 a resource

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

add tag

How to remove the Tag from a resource

When editing a resource, such as a Connection, Format, Listener, Flow or Schedule, and wish to remove a tag, click the X button in front of the 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 the same button again.

How to display tags only

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

Expandable tags

Any grid which contains tags will then 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 more tags from the Tags drop-down.

filter tags

Sticky Filters

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

To enable sticky filters, click the Welcome link at 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 at the top of the screen and select Hide flow execution notifications.

disable notifications

Code Editor

Code editor is available anywhere in Etlworks Integrator where user can enter SQL, JavaScript or XLST code.

To access code editor click the Open in Editor button on top of the text field.

open in code editor

By default code editor opens in the pop-up window.

code editor

It then can be extended to the full screen by clicking the Enter full screen button and collapsed back to the pop-up window by clicking the Exit full screen button.

code editor

Code Editor Commands

# Shortcut Command
1 Ctrl+F Search
2 Ctrl+Shift+F Search & Replace
3 Click the Line:Column Go to Line
4 Click the language selector Select color highlighter
5 Click the Enter or Exit Full Screen Enter or exit full screen mode

code editor commands

Connection, Format, and Flow Selector

When configuring flows, the user can select Connections, Formats, Listeners and Flows in the fuzzy selector, which is basically a pop-up window to quickly look up the desired flow element.

fuzzy filter

To select an object, click on the connection, format, listener or flow; or press the Enter button on the keyboard.

To filter by name, simply type the name or part of the name into the Filter by name field.

To filter by tag(s), toggle one or more tag buttons.

To view or edit a connection, format or listener within the selector, click the view/edit button.

To close a pop-up window, press the ESC button on the keyboard or mouse-click anywhere outside of the pop-up window.

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, go to the Connections page (left menu) and click the + button. Then click the corresponding connection type in the opened box.

Connections

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

Step 2 After the connection type has been selected, continue by entering the 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 a connection has been created, you can test it by clicking the Test Connection button. Important: the Test Connection button is not visible for some types of connections, such as for HTTP.

Step 4 After a 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 from within the grid.

All Connections

Relational Databases

Important: if you are using Integrator as a cloud service, while creating a 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 app.etlworks.com is white-listed for your database server.

Etlworks Integrator works equally well with all relational and NoSQL databases, as long as there is a JDBC driver for the database, and the database can be accessed from the server which is running Integrator.

The following connectors are available out-of-the-box:

To create a database connection, open the Connections window, click the + button, and select Database in the left navigation bar. Select the database from the gallery. Enter the database connection parameters.

Databases

The database connection parameters.

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

Tip: If needed, you can use the tokens {user} and {password} to pass non-standard credentials for the username and password. For example, to connect to the Google BigQuery you will need to provide a service account email and path to a secret key file. Example: OAuthServiceAcctEmail={user};OAuthPvtKeyPath={password}.

Tip: the URL can include advanced options, such as enabling SSL, etc. Please visit the database vendor web site for more information.

Postgres

Connecting to a database if it is not on the list

Tip: If your database is not on the list of 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 your own premises, simply drop the JDBC driver into the application server lib folder and restart the server.

Step 2 Select the Generic JDBC connection type.

generic JDBC

Step 3 Specify the connection parameters.

JDBC

A temporary (staging) database

With our Integrator, it is possible to create a connection to a temporary, in-memory database and use it as a staging area for the data. The SQLite engine is used for temporary databases. The objects in a temporary database, such as tables, views, etc., are only visible to the database connection that originally created them and are automatically deleted when the connection that created them closes.

To create a connection to a temporary database:

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

Temporary Database

Step 2 Enter the required parameters.

Temporary Database parameters

Cloud Storage

To create connections to cloud storage options:

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

Cloud storage

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

Our Integrator supports the following cloud storage options:

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

Amazon S3 and Google Storage

Important: to connect to Google Cloud Storage you will need to enable the Interoperability API. To enable it: go to Google Cloud console->Storage->Settings->Interoperability->Enable and create an access key pair, which includes an Access Key ID and Secret Access Key.

Parameters:

Microsoft Azure Storage

Parameters:

File Storage

To create a connection to file storage:

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

File storages

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

Integrator supports the following file storage options:

FTP

(no encryption)

Parameters:

FTPS

(FTP with TLS/SSL encryption)

Parameters:

SFTP

Parameters:

WebDAV

Parameters:

Home Folder

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

The home folder and all subfolders under this can be accessed using the Server Storage connection type. The home folder can be referenced by using the token {app.data}.

Server Storage

Important: if you install Integrator on your premises, you can access files on any available network or local storage device. Otherwise, you can only access files in the Home folder and all subfolders of the Home folder.

Parameters:

Read more about server storage.

Read how to work with local files.

Redis

Redis is a key-value type of storage. Read more about Redis. Etlworks Integrator can read data using a unique or wildcard key and write data using a unique key.

Create a Redis connection in the Connections window by clicking the + button, typing in redis, and selecting the Redis connection type.

Redis

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

Working with Redis is very similar to working with files. Redis is a key-value storage where the Redis key functions as if it were a file name, while the Redis key-value would be associated with the file's content.

MongoDB

MongoDB is a document database with the scalability and flexibility that you want and the querying and indexing that you need. Read more about MongoDB. Etlworks Integrator can read and write data stored in MongoDB.

Create a MongoDB connection in the Connections window by clicking the + button, typing in mongo, and selecting MongoDB as the connection type.

MongoDB

To connect to a MongoDB you need to specify the following parameters:

MongoDB connection

Working with MongoDB is similar to working with files. The MongoDB document id is a file name, and the associated with the document id document is the file content.

Read how to work with MongoDB in Etlworks Integrator.

HTTP Connector

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

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

Connection parameters

To connect to a 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: below is the beginning of a section about 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: this is the end of the section on parameters used for authentication.

Response File Name - the name of the file to dump the response from the request. If empty, the system will not create a response file.

Read how to debug HTTP requests.

Google Sheets

Etlworks Integrator can read and write data in Google Sheets.

To work with Google Sheets you will need to create a connection. In the Connections window click the + button and type in google sheets in the search field. Select the Google Sheets connection from the gallery.

Google Sheets Connection

Prior to creating a connection make sure you understand what a Google Service Account is. You will use either a default service account or you will create a new service account.

When creating a connection, define the following properties:

Spreadsheet ID - a unique ID for the spreadsheet. For example, if the URL used to access a spreadsheet looks like the following https://docs.google.com/spreadsheets/d/1Nqi6B1nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74AB/edit?ts=5aae75ea#gid=1051122191 the Spreadsheet ID is going to be 1Nqi6B1nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74AB.

Spreadsheet ID

Worksheet Name or 1-based index - the name of the worksheet or 1-based index of the worksheet.

Range - an optional range for the data, for example, A1:E10.

Service Account Email - the spreadsheet must be shared with the service account email. The default service account email is etl-framework@api-project-201080624425.iam.gserviceaccount.com.

Important: to share a spreadsheet with a service account email, click the SHARE button, add the service account email to the list and click the Send button.

Share Spreadsheet

Service Account - You can create your own service account in the Google API console, authorize it in the Google Analytics Admin and add it to this connection. If nothing is entered in this field the default account, linked to the email etl-framework@api-project-201080624425.iam.gserviceaccount.com will be used.

Column names compatible with SQL - convert column names to SQL compatible column names by removing all characters, except alphanumeric characters and spaces.

Skip empty rows - skip rows with no data.

Treat 'null' as null - if this option is enabled, Etlworks Integrator will parse string values equal to 'null' as actual nulls (no value).

Date and Time Format - a format for timestamps (date+time).

Date Format - a format for date (date only, no time).

Time Format - a format for time (time only, no date).

Google Analytics

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

To work with Google Analytics you will need to create a connection. In the Connections window click the + button and type in google analytics in the search field. Select the Google Analytics connection from the gallery.

Google Analytics Connection

When creating a connection, define the following properties:

Inbound and Outbound Email

Integrator can read emails and email attachments using the POP3 and IMAP protocols, and send emails using SMTP. Emails and email attachments can be used as a part of the source-to-destination transformation, just like databases, files, web services, and social web sites.

Read how to connect to inbound and outbound email servers.

HL7 MLLP Sender

This type of connection is used to send HL7 messages over the 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 the HL7 MLLP Sender connection by typing hl7 into the filter field in the "Select Connection Type" gallery. Continue by selecting the HL7 MLLP Sender connection:

HL7 MLLP Sender

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

HL7 HTTP Sender

This type of connection is used to send HL7 messages over the HTTP protocol.

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

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

To send HL7 message over an 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 the Amazon Feeds API in Integrator:

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

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

Amazon MWS feed

Step 3. Select the Amazon MWS feed connection and continue by defining the 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 overcome the throttling enforced by Amazon.

Facebook

Read how to connect to Facebook.

Twitter

Read how to connect to Twitter.

Listeners

Listeners allow a user to create event-driven flows. For example, you can define an HTTP endpoint, which will be listening to the incoming HTTP requests. Once a request is received, the payload will be sent 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 third-party applications. Basically, you can build an API without writing a single line of code, which can then be used by other applications to send a payload to Integrator.

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

To create a new Listener, go to the Listeners page and 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 directly 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 direct source for the flow. If a flow is scheduled, it will be automatically executed each time the payload is received.

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

Note: the 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.

Learn more how to define URL pattern.

Read more about authenticating API requests.

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 with the dash - character.
  2. Sets the response to "error" (the 500 HTTP code will be returned to the caller).
  3. Sets the response body to Expected response.

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

HTTP listener

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 third-party applications, using the MLLP protocol. Once the payload is received, the MLLP listener sends back the acknowledgment and redirects the payload to the designated flow. If the flow is scheduled, it will be 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 included in creating a flow). Instead, you are defining a format metadata, such as: what delimiter is used for CSV or what namespace is used for XML.

Step 1 Open the Connections page and select the 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 desired format type, you can enter all or a part of the type name in the search box. After this, only the format types which have 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 the format type has been selected, continue by entering the format parameters.

Format parameters

Step 4 After the 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 from within the grid.

All Formats

CSV

CSV (or comma-separated values) is 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 REST-based web services. Integrator can parse and create practically any JSON document. Below are the available parameters for the JSON format:

JSON dataset

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

Example of the JSON dataset:

{
    "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. 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>

For example (with no Row Tag):

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

Another example (with the Row Tag set to ROW):

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

For example (with no Null Attribute):

<MiddleName />

Another example (with the Null Attribute set to i:nil="true"):

<MiddleName i:nil="true"/>

For example (with no Value Attribute):

<FirstName>Joe</FirstName>

Another example (with the Value Attribute set to value):

<FirstName value="Joe"/>

XML dataset

XML dataset is an XML document, which includes metadata, such as column names and types, as well a dataset 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 Integrator.

Example of the XML dataset:

<?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 all 2.x formats. 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 the underscore character */
message = message.replace(" ", "_");

PDF

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

HTML

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

Data Integration Flows

Integrator supports the following data integration scenarios:

In Eltworks 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 will display 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, go to the Flow Builder page and click the + button. Then click the corresponding flow type in the opened box.

Flows

To quickly find the correct type, you can enter all or a part of the name in the search box. After this, only the types which include that string in their names will be displayed. To filter flows by 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 the flow's transformations. To add a new transformation, click the + button.

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

Most of the transformations require FROM and TO connections and formats, as well as the 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 the mapping and parameters.

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

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

Transformation mapping

Step 4 After the 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 directly 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 that same 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 Integrator to another, or copy flows between different accounts.

Transformations

A typical transformation includes a source (FROM), and a destination (TO). The source and destination can be any data source supported by Integrator: tables in a relational database, SQL statements, files, web services, API endpoints, or social media web sites.

You are required to select a connection for the source and destination. Format is only required for transformations with data objects, such as web services, or 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 reordered, disabled and deleted. Disabled transformations will not be executed.

All Flows

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

The Transformation name

Each source-to-destination transformation has either an automatically-generated or user-assigned name. In most cases, the name is generated automatically using the following algorithm: FROM + " TO " + TO + INDEX. For example, if the FROM (source) is employee.json and the TO (destination) is employee.csv, and the index of the transformation is 1, the transformation name will be employee.json TO employee.csv 1.

If you override the transformation name under the mapping->parameters this name will be used instead:

transformation name

High-level transformations

High-level transformations are typically performed on the entire dataset, right after the extract or before the load.

Additional transformations

Complex transformations

Testing Transformations

Any source-to-destination transformation can be tested without executing the whole flow.

Step 1. Configure the transformation.

Step 2. Click the Mapping button.

complex transformation

Step 3. Click the Test Transformation button.

test transformation

Step 4. The result will be displayed in the Test Results tab.

test results

The following rules are applied:

  1. If the destination in the FROM-TO transformation is a database, or a document in a binary format, such as Excel, the result will be displayed as a JSON dataset.
  2. If the destination is a document in one of the text formats, such as XML, JSON, CSV, etc., the result will be displayed using the original destination format.
  3. Etlworks Integrator extracts only the first 100 rows when sampling the dataset, in order to test the transformation.
  4. The Test Transformation button is not available for some transformations.

Mapping

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

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

On the mapping page of the Flow Editor you can map the source to the destination and, consequently, the source fields to the destination fields.

Mapping overview

Mapping a Source to a Destination

Mapping Source fields to Destination fields

Parameters

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

To open the Parameters section while still on a MAPPING screen, select one of the following tabs: Parameters, Change Replication, Additional Transformations, On Exception, or Flow Control. The actual list of the available parameters and high-level transformations is dynamic and depends on the type of flow, and source and destination connections.

Parameters

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

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

This transformation returns an object called dataSet and it must use the same references as the source dataset. 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 (as opposed to the entire dataset), the following objects are available:

The classes and methods of the Toolsverse ETL Framework are available from JavaScript code. Read more about classes and interfaces of the ETL Framework.

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

importPackage(com.toolsverse.etl.common);

Example of 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 dataset:

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, Integrator extracts data from the database table, view, or synonym or executes a user-defined SQL query.

The user has two options:

Database is a source

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

You can conditionally fail the 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 a database, Integrator either automatically generates and executes the SQL DML statement (INSERT, UPDATE, DELETE or MERGE) or executes user-created SQL statements.

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

  1. Use the To field to select or enter the database object name (table, view, synonym).
  2. Or enter the actual INSERT, UPDATE, or DELETE SQL statement in the Destination query field.

Destination query

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

In the example below, 2 queries will be executed. Fields within the { and } brackets will be replaced with the actual field values given by 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 the 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 a flat or structured file, a response from the web service or a social web site.

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

The source is a data object

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

Read more about executing SQL queries on any dataset.

The source is SQL

Important: if your source data object is a file, it is possible to work with wildcard file names. For example, if a third-party application is creating files with the name order_timestamp.csv in an outbound folder (order_010120171123.csv). The timestamp part of the file name is dynamic and will be changed for each file created in the outbound folder. In Integrator, it is possible to configure a source (FROM) connection so that it will read the files by the wildcard name, for example, order_*.csv and will apply one of the available 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 the Files attribute to the wildcard, for example, order_*.csv.
  2. Set the Enable Wildcard File Name attribute to one of the available algorithms. For example, if Files = order_*.csv and the oldest is selected, Integrator will always select the oldest file in the folder which matches the wildcard file name order_*.csv.

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

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

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

or just plain JavaScript:

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

You can conditionally fail the 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 a flat or structured file, a payload for the API endpoint or a 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 the destination connection is in file storage - the new file will be created each time the transformation is executed. If the file already exists, it will be overwritten.

The destination is a data object

The following parameters and high-level transformations are available:

Destination File Name

Default rules

Destination file name is the same as the source file name

Calculate destination file name

Creating files with unique names

Destination file name as a variable

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 in a cloud.

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

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

Step 3 Start creating a new flow on 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 the 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 an HTTP connection that points to the API endpoint.

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

The HTTP payload

Important: to submit a payload asynchronously, set the property Maximum Asynchronous HTTP Calls to a value greater than zero (zero is the default).

Asynchronous HTTP

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

Send payload to the HTTP endpoint

Step 4 Select the flow and enter an 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 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 variables using JavaScript.

Option 2. Executing flow in an SQL loop.

Working with Google Sheets

Step 1 Create a connection to Google Sheets.

Step 2 To read data from Google Sheets, use one of the flow type which start with extract data from well-known API.

Extract data from a well-known API

Step 3 To write data into Google Sheets, use one of the flow types where the well-known API is a destination.

Send to a well-known API

Working with Google Analytics

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

Using the Google Analytics connector, you can define the 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 a file, database or another web service.

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

Step 1 Create a Google Analytics Connection.

A 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 the Google Analytics data for the property defined by View ID.

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

Google Analytics Flows

Step 4 Continue by defining a transformation(s) where the source (FROM) is a Google Analytics Connection created in step 1, and the destination (TO) is either a file, database or 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 an Amazon MWS Feeds connection

Step 2. Create the flow to submit data to the 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, the use of the JavaScript is required.

The HL7 2.x connector is based on the HAPI The entire HAPI API can be called with JavaScript code.

The HL7 FIHR connector is based on the the HAPI FIHR framework. As with HAPI 2.x the entire HAPI FIHR API can be called with JavaScript code.

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

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

The HL7 messages can be sent and received using an:

Step 2 Choose the source (FROM) and destination (TO) format.

The format could be:

Step 3 Start creating a new flow on 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 the transformations, mapping and parameters.

The source is an HL7 message

When creating a flow and the source is an HL7 message, select one of the flow types which starts with "Read HL7 message".

Typical use cases include:

When the source is an HL7 message, Integrator automatically parses it and creates a dataSet.getActualData() object, which can be called with JavaScript code. The object contains a parsed HL7 message with human-readable attributes, representing segments and fields.

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

Read more about the HL7 FIHR domain model here.

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

HL7 Code

The example below demonstrates how to transform a source HL7 message into a destination dataset:

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

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

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

// adding fields and values to the destination dataset 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());

The 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 a parsed HL7 message, the following two lines were added to the JavaScript code:

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

They represent the domain model for HL7 2.4.

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

Creating an acknowledgment for the source HL7 message

When the HL7 message is received, in most cases it is required that you send an acknowledgment.

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

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

An HL7 Acknowledgment

Step 2 In the Preprocessing field, enter the following line:

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

An HL7 Acknowledgment Code

The destination is an HL7 message

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

Typical use cases include:

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

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

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

Below is an example of creating an HL7 message from a source dataset:

// importing the 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 the message as actual data from the dataset 
dataSet.setActualData(message);

The source and destination are HL7 messages

This is the most commonly used HL7 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 an HL7->HL7 flow, follow the same steps as before. The only difference is that you select the Read HL7 message, create different HL7 message flow type from the list of 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

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

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

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

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

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

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

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

The source is a File

Step 5 Continue by creating a transformation where 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 a flow with a listener cannot be manually executed, it must be scheduled.

Important: the scheduled event-driven flows are executed when a payload arrives, as opposed to the regular flows, which are executed at specific time intervals.

Testing event-driven flows in Integrator

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

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

Let's assume that you have an example payload somewhere in a file-based or cloud storage. Let's also assume that you have already created and scheduled an event-driven 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 previously created for the event-driven flow.

For example, let's assume that:

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

If all of the above is true, create a destination HTTP connection for the sender flow with the following parameters:

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

The 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 files, check number of files in the folder, or create a folder(s).

Step 1. Create the source (FROM) and 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 source files and the destination (TO) - is a location where files should be created (which can be the same method used for the rename operation).

Move files

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

Move file names

Step 5. Continue by specifying the transformation parameters:

Important: when selecting FROM and TO:

For Copy actions: Choose the source (FROM) and destination (TO) connections. Enter the file name, or a wildcard file name, such as *.csv, into the source (FROM) field. The files from the source location (FROM connection) will be copied to the destination location (TO connection).

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

For Rename actions: Choose the source (FROM) and destination (TO) connections (they can be the same). Enter the file name, or a wildcard file name, such as *.csv, into the source (FROM) field. Enter new file name, or a wildcard file name, such as dest.*, into 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.

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

For Create Folder(s) actions: Choose the source (FROM) connection. Enter the name of the folder to be created, into the FROM field. If that folder doesn't exist, it will be created under the URL/directory of the FROM connection.

For Check Number of Files actions: Choose the source (FROM) connection. Enter the file name, or a wildcard file name, such as *.csv, into the source (FROM) field. The system will calculate the number of files whose names match the FROM field, compare it to the entered number of files, and generate an exception if they are not equal.

Important: the File Management flow allows 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:

Copy Files

Read how to copy files between file storage locations.

Move Files

Read how to move files between file storage locations.

Rename Files

Read how to rename files.

Delete Files

Read how to delete files.

Create a Folder

Read how to create a folder.

Check Number of Files in a Folder

Read how to check the number of Files in a Folder.

Zip/Unzip files

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

Step 1. Create the source (FROM) and destination (TO) connections, using Server storage connection type.

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

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

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

zipping files

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

Step 5. Continue by specifying the transformation parameters:

Important: when selecting FROM and TO:

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

For Unzip actions: Choose the source (FROM) connection for the location of the ZIPPED file, and the destination (TO) connection for the location where the files from the ZIPPED file will be extracted to. Enter the ZIPPED file name into the FROM field.

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

Zip Files

Read how to zip files.

Unzip Files

Read how to unzip files.

Merge CSV files

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

Important: this flow can merge two or more CSV files with a 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 that you use the same connection for both).

Note: At this time only the Server Storage connection type is supported.

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

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

Merge CSV files

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

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

Step 6. Continue by specifying the transformation parameters:

Merge XML files

Use this flow type when you want to merge multiple XML files into a single file.

Step 1. Create FROM and TO connections.

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

Merge XML files

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

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

Step 6. Continue by specifying the transformation parameters:

Executing any JavaScript code

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

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

Read about scripting in Etlworks Integrator.

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

Javascript flow

Step 2 Continue by entering JavaScript into 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 JavaScript:

The entire Toolsverse ETL Framework can be called with JavaScript code. Read more about classes and interfaces of the ETL Framework. Look at the packages under com.toolverse.etl.

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 methods.

Step 1 Start creating an 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 the destination connection created in step 1.

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

SQL parameter

Available parameters:

An example of the anonymous block statement (TSQL):

DECLARE 
   @param1 varchar(100);
BEGIN
   set @param1 = '_xyz';

   insert into loop_test (name) 
   values (CONCAT('{name}', @param1));
END;   

Working with outbound emails

Read how to work with outbound emails.

Working with inbound emails

Read how to work with inbound emails.

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 different types:

There is an easy way to combine flows of different types into a single nested flow, then 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, where one flow is used as input for another, where flows are executed multiple times based on conditions, or where flows are not executed at all when conditions are not met.

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

Step 1 Start creating a nested flow by opening the Flow Builder window, clicking the Add flow button and typing Nested flow into 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

Removing inner flow

remove inner flow

Disabling inner flow

disable inner flow

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

nested flow parameters

Available parameters are:

Rules when working with JavaScript, for Conditions and Loops

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

value = condition;

A Loop continues to run as long as the Loop JavaScript returns a not null value in the last line.

value = condition ? true : null;

The following objects are available by name in JavaScript code:

Additionally, the following objects are available for Loops in JavaScript:

value = evals <= 40 ? evals : null;

The entire Toolsverse ETL Framework can be called with JavaScript code. Read more about the 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);

Flow Variables

When creating a nested flow, you can add Flow Variables - key/value pairs, which can be referenced in SQL and JavaScript code as a {VARIABLE}. For example, there is a flow variable NAME, defined for the nested flow:

flow variables

A flow variable can be referenced in the Source Query as demonstrated below:

select * from connection
where lower(name) like '%{NAME}%'

using flow variables

A flow variable can also be accessed within the JavaScript.

Named Connections

Typically, a nested flow inherits all the connections from the inner flows. You can add additional named connections under the Connections tab.

named connections

You can then use the named connections in inner flows, for example in JavaScript.

using named connections

Very complex data integration flows

Integrator includes one special flow type, called Execute scenario in XML format.

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

Read more about the Data Explorer and the ETL Framework here.

To create a data integration flow in the XML format:

Step 1 Create a flow in the XML format in either Data Explorer or the ETL Framework and test it. Use named connections only (as opposed to the default source and destination connections).

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

xml flow

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

xml flow connections

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

xml flow parameters

Controlling flow execution

Handling Errors

Read how to configure flow to ignore exceptions.

Read how to configure flow to be executed only if there is an error.

Executing flow asynchronously

Some of the flows can be executed asynchronously. When running asynchronous flow, Etlworks Integrator will not wait until the flow has finished before picking the next task from the queue.

Executing flow asynchronously is available for the following flow types:

To configure the flow to be executed asynchronously, click the mapping button for that transformation, select the Flow Control tab, and enter a number greater than 0 into the Maximum Asynchronous Calls field. This field controls how many parallel threads can be used to run multiple instances of this flow. Any number greater than 0, for example ,1, will cause this flow to be executed asynchronously.

asynch flows

Executing Flows

Manually Executing Flows

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

run flow

Confirm that you want to execute a flow by clicking the Run button in the popup window.

confirm to run flow

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

  1. The flow is already running.
  2. The flow is event-driven.

Once the flow has been executed (whether with success or error), Integrator will display a pop-up notification.

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

Capture console log

Enable capturing console log to debug flows and monitor execution at real time. This option is enabled by default.

Extra Log Step

Optionally, enter a number >= 100 into the Extra Log Step field. If entered, an extra checkpoint will be added to the flow log every time that specified number of data records has been processed. While expanding the verbosity of the log it can negatively impact performance. Use Extra Log Step if you want to monitor a flow which is processing a large number of data records, typically > 1M(one million). Otherwise, keep it empty.

Note: entering Extra Log Step makes sense only if capturing console log is enabled.

Flow Log

The Flow Log is a feature that enables you to capture information about the flow while it's running in real time as well as after the flow has been executed. Flow log data is stored in the file system.

Note: capturing console log must be enabled.

To monitor a flow in real time, wait until it has started running, then open the Flow Dashboard by clicking the Running link.

flow running

In the Flow Dashboard click the View Console link.

flow log

The tail of the log view will open.

tail of the log

Use the same link to access the flow log for flows which have already been executed or canceled.

Stopping a currently running flow

To stop a currently running flow, use the Flow Builder's Stop/Cancel button.

abort flow

Important: the canceling a flow command may take up to a few minutes to complete.

Once the flow has successfully been canceled, the status will change from Running to Canceled.

canceled flow

Checking the results of a flow execution

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

When a flow execution fails, the Success link in the EXECUTED column changes to Error. For a canceled flow, the status is displayed as Canceled.

error status

Click the Error link to open the dashboard. Click the View Exception button to view the exception log. Click the View Console button to view the flow log.

link to flow error

The exception log contains detailed information about each error.

flow error

Read how to insert more information into the log.

Flow Statistics

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

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

flow stats

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

records metrics

...as well as any applicable File Metrics:

file metrics

The flow dashboard always includes a link to the flow log.

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

link to flow error

If you click on the link, a pop-up box opens up with detailed information about the error:

flow error

In addition to the current stats, the Flow Statistics dashboard includes a History of runs. The last 50 runs are available, by default, with the option for more runs.

Scheduling Flows

In Integrator, you can schedule flows for automatic execution.

To schedule a flow, open the Scheduler window

scheduled flows

...and click the Schedule button:

schedule

In this window you can also:

Scheduling periodically executed flows

Flows can be scheduled to be executed automatically at configurable time intervals. A scheduled flow is called an Event.

When scheduling events, the following parameters are available:

time schedule

Examples of scheduling, using the 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 am

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

every day

Scheduling event-driven flows

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

When scheduling event-driven flows, the following parameters are available:

Important: after an event-driven flow has been scheduled, you will need to wait one minute before you can start using it. The reason: Listeners need to be activated, which usually takes ~ 1 minute.

Working with local data

When Integrator is installed on a cloud, it can connect to databases, web services and APIs, as well as data in file- and cloud-based storage, if they are accessible from the Internet.

Internet accessibility is not always the case, especially for corporate databases.

Working with local files

Read how to upload local files into server storage.

Working with data behind a corporate firewall

Read how to work with data behind a corporate firewall.

User Profile

Changing a Password

To change a password:

Displaying the user profile

profile

Changing User Preferences

preferences

Available options:

Managing Tenants

Important: this function is only available to customers with dedicated instances. Only a SuperAdmin user has access to tenants.

Tenants are essentially sub-accounts under the main account. Each tenant has a separate list of users, flows, connections, formats and listeners. Tenants are completely isolated from each other. For example, flows created under tenant A are not visible to a user logged into an account that belongs to tenant B.

To create and manage a tenant, open the Users Management window and click Manage tenants link.

tenants

Note: settings under the tenant, such as Color Theme, can override the global configuration.

Assigning a user to the tenant

Admin and SuperAdmin users can create users under a particular tenant account.

A SuperAdmin user can reassign any user to a different tenant.

Step 1. Login as SuperAdmin.

Step 2. Open the User Management window and select the user.

Step 3. Change the tenant.

change tenant

Switching tenants on the fly

In the SuperAdmin mode, a user can see all flows, connections, formats and schedules, that are available for all users. In Integrator, it is possible to log in to a particular tenant on behalf of a SuperAdmin user.

Important: this option is available only for a SuperAdmin user who is in an environment with tenants. Please send email to support@etlworks.com or contact your system administrator for more details.

To switch the tenant:

tenant

After the switch, the SuperAdmin user will be logged into 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 SuperAdmin mode:

clear tenant selection

Managing Users

If you are an admin you can create and manage 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 will be generated automatically and will be sent to the email address in the user's profile, so make sure you are using an existing email address.

When creating a user, the following roles are available:

API User

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

To create an API user:

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

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

Please login to Integrator with an API user's credentials to change the password.

Configuration

Important: this function is only available to customers on dedicated instances. Only a SuperAdmin user has access to configuration.

To configure Integrator, open the Configuration window. Select a category in the left navigational bar.

configuration

Select the General category to configure appearance, links and the default email addresses.

You can change:

...as well as other parameters.

Configuring Email

Select Mail category to configure the SMTP server parameters for sending emails, as well as email templates.

Configuring the Network

Important: please contact Etlworks support before making any changes in this category.

Select the Network category to configure how the service works, in case there is a multi-node cluster deployment.

Configuring Explorer

Select the Explorer category to configure the maximum number of records which can be displayed in Explorer, as well as the maximum number of items that can be displayed in SQL history

Configuring Timezones

Select the Timezone category to configure the Server and Account timezones.

Miscellaneous

Messages

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

Important: this feature is disabled by default and can be enabled by selecting flag Keep Messages when scheduling event-driven flows.

For example, when a third-party application calls Integrator's endpoint, and sends a JSON payload, the payload will be stored in its original format in the database.

Let's call the payload, received by Integrator, a message.

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

messages

To view the message in its raw format, click the icon on the left side of the grid.

raw message

Statistics and Audit-Trail

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

To view daily and monthly statistics, open the Statistics window:

statistics

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

audit trails

Subscriptions and Payments

Creating an account

To create a new Etlworks account, go to the customer portal, click the Sign up here link, enter the required information, and click Create my account.

Important: for security purposes, an Etlworks account != Integrator's account. The former is managed by a third-party subscription provider Paywhirl.

Signing into an existing account

If you already have an Etlworks account, open the customer portal, enter your credentials and click Sign in to your account.

To modify profile information, click My Profile.

Adding a payment method

To add a new or change an existing payment method, log in to the customer portal, click Payment Methods, and add or modify the credit card or bank account information.

Subscribing

To subscribe to a service, log in to the customer portal, click Subscriptions, click Browse Plans, select the plan, and click Add to Cart.

Payments and Invoices

Etlworks charges you in the following cases:

To view incoming and previous invoices, log in to the customer portal, and clickDashboard.

Canceling or changing a subscription

Please contact Etlworks at sales@etlworks.com to cancel or change a subscription.