Mapping overview

In Integrator, you can load data from database tables, 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 also be loaded into tables in a relational database, files, web services, or social media web sites. In this section, they will be referred as a destination (TO), and their fields/columns will be referred as destination fields.

The mapping editor

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

Control elements

The Fields

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

Mapping a Source to a Destination

To map a source to a destination, select or enter the name of the source object (From) and the name of the destination object (To). If either the source or the destination (or both) are linked to a live connection, Integrator can automatically populate object names by reading metadata (table names, files names, API endpoints, etc.) directly from the data source.

From to Mapping

If the object name is not available in the drop-down, click the Clear Cache button at the bottom of the screen. Integrator will reconnect to the data object and attempt to reread the metadata. You can always manually enter the object name in this field.

Source query

A source query is an SQL query which extracts data from the source object. If the source is an SQL database, simply enter the SQL query using the SQL dialect supported by that particular database. In addition to SQL databases, Integrator supports running SQL queries against almost any data object. Read how to execute SQL queries against files and API endpoints.

Destination query

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

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

Read more about destination queries.

Important: this field is available only if the destination is an SQL database.

Mapping Source fields to Destination fields

In Integrator, it is possible to map source fields to destination fields, exclude fields, add fields to the destination which do not exist in the source, and more.

Field names

Integrator can automatically read field names from the source and destination objects. It then populates the SOURCE and DESTINATION drop-downs. Manually enter the field name if it is not available in the drop-down.

It is also possible to define a template for the source or destination, so that Integrator won't have to connect to the actual data source.

If a field name is not available in the drop-down, click the Clear Cache button at the bottom of the screen. Integrator will reconnect to the data object and attempt to reread field names. You can always manually enter the object name in this field.

The default mapping

Exclude a field

A field must be manually excluded in order to not appear in the destination.

Select or enter the SOURCE field, click the Exclude check box to the right.

Exclude field

Calculated fields

By default, a destination field gets its value from its corresponding source field. The value of any field can also be calculated using JavaScript.

Select or enter the DESTINATION field, click the pen icon to the right, and enter JavaScript to calculate the field's value. Read how to use JavaScript to calculate a field's value.

Calculated field

Add a pair of source and destination fields

To add a new pair of source and destination fields, click the + button. Select or enter the SOURCE and DESTINATION field names.

Add a field

Add a field to the destination which does not exist in the source

To add a field to the destination which does not exist in the source, click the add field button, enter the DESTINATION field name and click the Add Field checkbox. Continue by clicking the pen icon and entering JavaScript to calculate the field value.

Add a new field

Exclude a field from mapping

To exclude a field from manual mapping, simple delete it. The system will fall back to its default mapping.

delete field

Change the field data type

By default, the destination field gets its data type from the source field. To change the destination data type, click the Data type button, select the "Data type" from the drop-down, or if needed, enter a "Database specific type" (for example, varchar(255)). You can also specify if the field is nullable.

Field data type

Create Mapping

Integrator can automatically create a default mapping by simply reading the fields from the source object and mapping them to the same fields in the destination. You can then manually change the source and destination field names.

Create mapping

Important: clicking Create mapping overrides the current mapping.

Remove Mapping

You can remove all manual mapping by clicking the Remove mapping button.

Remove mapping

Working with nested datasets

The output from most of the web services is nested. For example, when ORDER is returned from the API, it contains information about the order itself, as well as all items included in the order.

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

Nested data object

As you can see in the figure above, the dimensions (inner data sets) are test and phoneNumbers. Use the buttons Make this field a child of the one above and Move child field up one level to control the level of nestedness for a specific field. You can also drag and drop fields up and down.

Important: at this time, the mapping editor supports nested data objects only for the source. Integrator itself includes multiple techniques for creating nested destination objects.

Read more about working with complex nested datasets.