In the Etlworks 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.
The Etlworks Integrator can automatically read field names from the source and destination objects. It then populates the
DESTINATION drop-downs. You can manually enter the field name if it is not available in the drop-down.
It is also possible to define a template for the
DESTINATION so that the Etlworks Integrator won't have to connect to the actual data source.
If a field name is not available in the drop-down, click
Clear Cache at the bottom of the screen. The Etlworks 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
If there is no Mapping, the
DESTINATIONfields will be exactly the same as the
SOURCEfield is not part of the Mapping, it will be automatically added to the
DESTINATION(the default mapping).
SOURCEfield must be manually excluded to not appear in the destination.
Select field from the list
Use a metadata selector to select a field from the available fields for the specific source or destination Connection.
Exclude a field
A field must be manually excluded to not appear in the destination.
Select or enter the
SOURCE field, click the
Exclude check box to the right.
By default, a
DESTINATION field gets its value from its corresponding
Select or enter the
DESTINATION field, click the
pen icon to the right, and enter a program in the scripting language to calculate the field's value.
Use scripting languages
- How to use native SQL to calculate a field's value.
- How to use SQL to Lookup Fields' value.
Add a pair of source and destination fields
To add a new pair of
DESTINATION fields, click
+. Select or enter the
DESTINATION field names.
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 add field, enter the
DESTINATION field name, and click the
Add Field checkbox. Continue by clicking the
Exclude a field from Mapping
To exclude a field from manual Mapping, simply delete it. The system will fall back to its default Mapping.
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
Drag to reorder files in the Mapping.
If the field already exists in the source, it will retain its position. The only exclusion if the destination is the CSV file, in which case it is possible to configure the destination (
TO) CSV Format to honor the order of fields in the Mapping.
The Etlworks 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 overrides the current Mapping.
You can remove all manual Mapping by clicking
Work with nested datasets
The output from most 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 the Etlworks Integrator reads a nested source data object, it parses it and represents the fields in a tree-like structure:
As you can see in the figure above, the
dimensions (inner data sets) are
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.
At this time, the mapping editor supports nested data objects only for the source. The Etlworks Integrator itself includes multiple techniques for creating nested destination objects.
Read more about working with complex nested datasets.