Copyright © 2017 JumpMind, Inc

Version 3.2.2

Permission to use, copy, modify, and distribute this Metl User Guide for any purpose and without fee is hereby granted in perpetuity, provided that the above copyright notice and this paragraph appear in all copies.

Preface

This user guide introduces Metl, a simple, web-based integration platform that allows for several different styles of data integration including file based Extract/Transform/Load (ETL), messaging, and remote procedure invocation via Web Services. This guide is intended for users, developers, and administrators who want to install the software, configure integrations, and manage its operation. Thank you to all the members of the open source community whose feedback and contributions helped us build better software and documentation. This version of the guide was generated on 2018-07-20.

1. Introduction

Metl is a simple, web-based integration platform that allows for several different styles of data integration including messaging, file based Extract/Transform/Load (ETL), and remote procedure invocation via Web Services. Metl was built to solve fairly simple day to day integration tasks without the need for custom coding, heavy infrastructure, or high costs. It can be deployed in the cloud or in an internal data center, and was built to allow developers to extend it to fit their needs by writing their own components that can be included and leveraged by the existing Metl infrastructure.

1.1. Overview

Metl is a web application. It can be deployed as a web application archive (war) file to an application server (i.e. Tomcat, JBoss, Websphere) or can be deployed stand-alone as a war file deployed under a bundled Jetty Servlet container. See installation section for details.

Once installed, the web application is accessed via a web browser and is used to design, deploy and manage integrations.

Integrations can be grouped or packaged into one or more projects. Projects are a means to logically group integrations together in some logical way. As an example, a project might contain all of the integrations in or out of a specific system. See projects for additional details.

Projects contain Flows, Models and Resources.

Flows are the integrations themselves. That is, flows allow the definition of data be retrieved from source systems, transformed, and written to target systems. Flows are graphical in nature and allow the developer to configure data movement.

Flows are constructed of components connected by links that describe the path of data through the flow. There are various components available, and custom components can be built and integrated in to the existing Metl infrastructure.

See flows for additional details.

Models provide structure to your data. When dealing with structured data, the data can be defined by modeling. Metl Models allow you to describe your data. Models can either be relational or hierarchical in nature. See models for additional details.

Resources represent connections to physical endpoints where data is read from or written to. The following resource types are available in Metl:

  • Database - A JDBC connection to a JDBC compliant relational database

  • Directories - Connections to file systems. Local, FTP, SFTP and SMB are all supported

  • HTTP Resource - An HTTP connection to REST or SOAP based services

  • Mail Session - An SMTP connection that can be used to send and receive email

See resources for additional details.

1.2. Use Cases

  • Flat File Data Integrations

  • RDBMS Data Integrations

  • Pub / Sub (Queue based) Data Integrations

  • API / Service Based Data Integrations

1.3. Why Metl?

  • Integrations can be configured in hours vs coding them in days or weeks

  • Metl is easy to install. Simply download the .war file and run

  • Metl is web based. No tools to install on each desktop. Deploy locally, in your data center or in the cloud

  • Metl doesn’t require heavy infrastructure. Any windows or linux box will do

  • Metl is open. No black box. Download the source. Write your own components if desired

  • Metl is proven in large integration scenarios

  • Metl is free. The entire toolset is open source under the GPL license

  • Metl is supported. JumpMind, Inc. provides professional support and services for any need

1.4. System Requirements

Metl is written in Java and requires a Java Runtime Environment (JRE) Standard Edition (SE) or Java Development Kit (JDK) Standard Edition (SE). Most major operating systems and databases are supported. The minimum operating system requirements are:

  • Java SE - Runtime Environment or Development Kit version 8 or above

  • Memory - 1 (GB) available

  • Disk - 256 (MB) available

Metl is accessed from a web console, which requires one of the following supported web browsers:

  • Google Chrome 23 or newer

  • Internet Explorer 8 or newer

  • Mozilla Firefox 17 or newer

  • Safari 6 or newer

1.5. License

METL is free software licensed under the GNU General Public License (GPL) version 3.0. See http://www.gnu.org/licenses/gpl.html for the full text of the license. This project includes software developed by JumpMind (http://www.jumpmind.com/) and a community of multiple contributors. METL is licensed to JumpMind as the copyright holder under one or more Contributor License Agreements. METL and the METL logos are trademarks of JumpMind.

2. Installation

Metl is provided as a web application archive (war) file. The .war file can be deployed in two different ways.

  • Deployed stand-alone using Metl’s embedded Jetty server

  • Deployed to an existing servlet container (i.e. Tomcat, JBoss, Websphere, Oracle Application Server, etc.)

Deployed stand-alone using Metl’s embedded Jetty server

The simplest way to run Metl is to run it stand-alone using the internal Jetty container. In order to run Metl stand-alone, simply run the following from a command line.

A Java 8 run-time environment must be installed and in the path to run java.
java -jar metl.war

In the simplest form shown above, Metl will choose a configuration direction based on the logged in user’s home directory (i.e. /users/myusername/.metl) for linux based systems and (c:/users/myusername/.metl) on windows based systems. In this form metl will use a file-based H2 database for for the Metl data store.

Deployed to an existing servlet container

In order to deploy Metl to an existing servlet container, simply copy the metl.war file to the deploy directory of the application server.

Specifying the Metl configuration directory

The default configuration directory can be set by specifying the system property org.jumpmind.metl.ui.init.config.dir.

java -Dorg.jumpmind.metl.ui.init.config.dir=/opt/metl -jar metl.war

Application properties file (metl.properties)

Metl will create a default properties file in its configuration directory if one does not exist. The property file is named metl.properties and has the following content.

db.driver=org.h2.Driver
db.url=jdbc:h2:./metl-dev
execution.retention.time.ms=3600000
execution.retention.time.ms.cancelled=60000
log.to.console.enabled=true
log.to.file.enabled=true
table.prefix=METL
#log.file=

Specifying an alternate database resource for metl configuration and run-time data is as simple as specifying a jdbc driver and url in the metl.properties file.

db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://myserver:1433/mydatabase
db.user=myuserid
db.password=mypassword

By default, Metl uses a DBCP connection pool. In addition to those above, all DBCP configuration settings can be set in the metl.properties file including:

db.validation.query=
db.pool.initial.size=
db.pool.max.active=
db.pool.max.idle=
db.pool.min.idle=
db.pool.max.wait.millis=
db.pool.min.evictable.idle.millis=
db.test.on.borrow=
db.test.on.return=
db.test.while.idle=

Installing Metl as a service

Metl can be installed as a linux/unix or windows service. To install Metl as a service, run the following from a command line.

java -Dorg.jumpmind.metl.ui.init.config.dir=/opt/metl -jar metl.war install

By default, Metl will be installed as a service with service name metl. A service configuration file named metl_service.conf will be written to the configuration directory and can be used to further configure the Metl service.

Starting the Metl service

To start the Metl service, run the following from a command line

java -Dorg.jumpmind.metl.ui.init.config.dir=/opt/metl -jar metl.war start

or, use the operating system tools to start and stop the service. On linux:

service metl start

For windows, use the local services control panel to start the Metl service.

Stopping the Metl service

To stop the Metl service, run the following from a command line

java -Dorg.jumpmind.metl.ui.init.config.dir=/opt/metl -jar metl.war stop

or, use the operating system tools to start and stop the service. On linux:

service metl stop

For windows, use the local services control panel to stop the Metl service.

Uninstalling Metl as a service

To uninstall Metl as a service, run the following from a command line.

java -Dorg.jumpmind.metl.ui.init.config.dir=/opt/metl -jar metl.war uninstall

Once Metl has been started, it can be accessed via a web browser as follows:

http://myservername:myport/metl

If Metl is running on the default port (42000) on the local machine, the url would be as follows:

http://localhost:42000/metl

3. The Metl User Interface

Accessing the Metl User Interface

Once Metl has been started, it can be accessed via a web browser as follows:

http://myservername:myport/metl

If Metl is running on the default port (42000) on the local machine, the url would be as follows:

http://localhost:42000/metl

The following home screen will be displayed:

home

Navigation Menu

The navigation menu shown below is the mechanism for traversing the system.

navigation menu

The Metl UI is split into five major areas:

  • Design - Design and Develop Integrations

  • Deploy - Deploy integrations to a Metl run time agent

  • Manage - Manage integrations that have been run on a given agent including looking at past executions, etc.

  • Explore - Explore resources that are configured for this Metl instance

  • Admin - Administrative management of Metl including defining system users, managing logging levels, etc.

In addition to these five major navigation points, the navigation menu also allows for:

  • Help - Access this help documentation

  • Logout - Log the current user out of the Metl UI

4. Design

The Design screens are used to design and develop integrations. The main design screen is shown below.

design

The design screen is split into two main panels, the navigation pane and the content/editor pane. The navigation pane shown below allows for traversing projects, flows, models and resources.

navigation panel

When Metl is first installed, it comes installed with a sample project that includes sample flows, models and resources. These samples allow the user to immediately see Metl in action. In addition, these sample flows are referenced in the component documentation in order to better understand how individual components work.

The content/editor pane is where flows, resources and models are edited. The screenshot below shows a database resource being edited.

content editor panel

4.1. Projects

Projects are a means to group integrations together in some logical way. Typically, the boundaries of a project should equate to a deployable unit. That means the scope or context of the project should be those flows that must be bundled together when deployed. I.E. if two flows depend on each other and are so tightly coupled that changes to one dictate changes in the other, then those flows should be bundled together in a single project. The project is the level at which flows and their associated resources and models are versioned (see Versions) and imported/exported (see Import/Export).

A new project can be added via the menu.

projects

4.1.1. Versions

Projects can have multiple versions. When a project is first created an initial version is also created with name master. After the master project is released (deployed to production), future changes to the project can be completed in a new version such that each production migration of the project can be tracked. By making each production release of a project under a new project version visibility to differences between versions is also available. New versions of a project can be created from any subsequent version. I.E. if you have versions 1.0.0, 1.1.0, and 1.2.0, a new version can be created from ANY of the previous 3 versions. A patch release (1.1.1) version can be created from the 1.1.0 version while simultaneously making changes to the 1.2.0 version for some future release date. This is very similar to branching and merging concepts that are available in most source control systems.

To create a new version of a project from an existing version, first select the source version from which the new version should be created and then click File New Project Version as shown below.

projects versions

4.1.2. Project Version Dependencies

One project version can be dependent on another project version. A project dependency can be added with File New Project Dependency. When a project is dependent on another it gains access to Resources, Models and Flows in the child project. A good use case for adding Project Dependencies is for sharing common resources.

4.1.3. Import/Export

Project configuration can be exported and imported. Use File Export…​ and File Import…​. The export is to a JSON file. Import and Export are used to migrate from one environment to another or to backup your configuration.

Also note that a subset of your project can be exported if need be.

projects import export

4.2. Flows

An integration is called a flow. A flow defines the input to be read, the processing rules to be applied, and the output to be written.

Flows are a graphical set of instructions for how data is integrated (flows) from a source system to a target system.

4.3. Components

Flows consist of a set of components (readers, processors or writers) connected together with component links.

A typical component (the rdbms-reader) component is shown below.

flows component

Each component has an input port and an output port (blue circles in the image above). The input and output ports are used to connect the component to other components with component links. Each port has a designation that dictates the type of message that can be sent into the component and the type of message that the component generates. The designations are:

  • E - A model based message

  • T - A text based message

  • B - A binary based message

  • * - Any message type

Each component completes some action when an input message is received and sends one or more resultant messages when it is complete. The RDBMS-Reader as an example takes any input message type and produces model based message types. When an input message is received, the RDBMS-Reader executes a configured sql script and returns the resultant rows from execution of the script out as model based messages.

The following shows a simple flow that reads a table from a database and writes it to three different flat files, a delimited file, a fixed length file and an xml file.

sample flow

In the flow above, each component completes a specific task as follows:

Component Type Task

Setup Person Database

Sql Executor

Executes a sql sript to set up the database and tables that will be used in the sample flow

Person Table Reader

RDBMS Reader

Executes a sql statement to read the person table from the database created by the Setup Person Database component

Delimited Formatter

Format Delimited

Formats the person data sent from the Person table reader in a delimited format

Delimited File Writer

Text File Writer

Writes the delimited formatted data to a text file

Fixed Length Formatter

Format Fixed

Formats the person data sent from the Person table reader in a fixed length format

Fixed Length Writer

Text File Writer

Writes the delimited formatted data to a text file

XML Formatter

Format XML

Formats the person data sent from the Person table reader in an xml format

XML File Writer

Text File Writer

Writes the delimited formatted data to a text file

When a flow executes, all components in the flow are initialized in parallel and begin waiting for messages to process. Any component with NO inbound component links receives a Control Message from the Metl framework telling it to perform its action.

The outbound messages from those components started by the Metl framework are fed to downstream components, and the flow runs until completion. In the example above, when the flow is run, all components are initialized and wait for inbound messages.

The Setup Person Database component receives a startup message, executes its sql script to create the initial database used in the sample flow, and then sends an output message to the Person Table Reader component which runs its sql statement to select the person records from the database. The output of the RDMBS reader is messages that contain rows which are sent to the Formatters, and so forth down the chain until all components are complete, and then the flow ends.

4.4. Messages

Data flows between components over the component links in a series of messages that are generated and consumed by each component. There are three different types of messages.

  • Model based message - A message that contains records in model format. I.E. data parsed and stored according to a given Metl Model

  • Text based message - A message that contains text based records. I.E. data in string format

  • Binary based message - A message that contains binary based data. I.E. A binary object

The difference in the message types, is the type of payload that is carried by each. A model based message has a payload that contains an array of model based records. Each model based record has attributes and values. Each value is tied to a specific attribute in the model.

A text based message has a payload that contains an array of strings (text data), and a binary based message has a payload of binary data.

4.4.1. Unit of Work

Several components can deal with multiple units of work in the execution of a given flow.

As an example, the Text File Reader can take one or more inbound messages that specify the path and name of a file to read. In this instance, the text file reader receives three messages, and will read data from the three files specified in each input message. When the text file reader completes reading of a given file and sends all data in output messages, it will send a control message that indicates it is complete with the given "unit of work" (i.e. reading the file that was specified by the input message).

Downstream components can utilize that control message in different ways depending on the implementation and configuration of the downstream component. As an example, many components have a "Run When" configuration option which can be set to "Per Message" or "Per Unit of Work".

Setting the "Run When" option to "Per Unit of Work" will enable the component to run when a unit of work control message is received".

Setting the "Run When" option to "Per Message" will enable the component to run each time any data message is received.

4.5. Components

The following table shows a list of available components.

Icon Name Use When

Readers

metl binary reader 48x48 color

Binary File Reader

Data needs read from a binary file

excel 48x48 color

Excel File Reader

Data needs read from an Excel file

metl file poller 48x48 color

File Poller

A file based resource needs to be checked for the existence of a file or set of files

metl reader 48x48 color

RDBMS Reader

Data needs read from a relational database

metl text constant 48x48 color

Text Constant

A text constant is needed in message content in the flow

metl text file 48x48 color

Text File Reader

Data needs read from a text file

metl unzip 48x48 color

Unzip

A compressed zip file needs to be extracted in order to process its content

metl xml formatter 48x48 color

XML Reader

An XML file needs to be read and element content of that file sent as individual messages

Processors

metl assert 48x48 color

Assert

Check for an assertion

metl flow 48x48 color

Embed Flow

A flow needs to be embedded in another flow

metl content router 48x48 color

Content Router

Content within a message needs to be routed to different downstream components in the existing flow

metl deduper 48x48 color

DeDupe

Input model based records must be deduplicated before being sent to downstream components

metl delay 48x48 color

Delay

A delay is needed when sending data between components

testtube green 48

Deserializer

A component that will parse XML or JSON to a Model

metl execute 48x48 color

Execute

There is a need to execute an operating system command as part of a flow

metl file util 48x48 color

File Util

Files need to be renamed, copied, moved or deleted

metl delimitedformatter out 48x48 color

Format Delimited

Model based data needs to be formatted into delimited text

metl fixed length 48x48 color

Format Fixed

Model based data needs to be formatted into fixed length text

metl xml formatter out 48x48 color

Format XML

Model based data needs to be formatted into xml text

metl lookup 48x48 color

Lookup

Reference data exists that needs to be looked up / retrieved by a specified key, and another attribute of the the resulting reference data passed to downstream components.

metl mapping 48x48 color

Mapping

Data needs to be mapped from one set of entities/attributes (model structure) to another set of entities/attributes

metl mapping 48x48 color

Rel/Hier Mapping

Data needs to be mapped from a relational model to a hierarchical model

metl merger 48x48 color

Merger

Different instances of model based records need to be merged into a single instance that combines the attributes from all instances.

metl message logger 48x48 color

Message Logger

Messages need to be logged to the Metl console and log file

metl diff 48x48 color

Model Data Diff

A set of model based data needs compared to another set of model based data in order to generate insert/update/delete instructions for a writer component

metl multiplier 48x48 color

Multiplier

Inbound messages need to be multiplied (duplicated n number of times) and the sent to downstream components.

metl puzzle 48x48 color

No Op

A message needs to be passed through to another component without any action being taken on the message

metl delimitedformatter in 48x48 color

Parse Delimited

An inbound text message in some delimited text format needs to be parsed into a model based message structure

metl fixed length 48x48 color

Parse Fixed

An inbound text message in a fixed length text format needs to be parsed into a model based message structure

metl xml formatter in 48x48 color

Parse XML

An inbound text message in xml text format needs to be parsed into a model based message structure

metl script 48x48 color

Script

A custom action should be performed that can be written using Groovy

testtube orange 48

Serializer

A component that will format an entity to XML or JSON

metl sequencing 48x48 color

Sequence

A sequence number needs to be created for a given attribute in a model based record, and the starting value of the sequence can be derived by an sql statement.

metl sorter 48x48 color

Sorter

Data within a model based message needs to be sorted by one of the attributes within the model

metl temp rdbms 48x48 color

Temp RDBMS

Entity message data needs to be staged and queried using SQL.

metl regex replace 48x48 color

Text Replace

A piece of text in a text based message needs replaced with alternate text

metl transform 48x48 color

Transformer

An attribute in a model based message needs transformed in one way or another (trim, substring, constants, etc.)

metl union 48x48 color

Union

Inbound messages need to be combined into a single message and then sent to downstream components.

metl web 48x48 color

Web Request

A web service (either REST or WSDL) needs to be called to perform an action such as retrieving or writing data

metl xslt 48x48 color

XSLT Processor

XSLT needs applied to a set of input data in order to transform it into a given output format

Writers

metl binary writer 48x48 color

Binary File Writer

Data needs written to a binary file

metl email 48x48 color

Email Writer

An email needs to be sent

metl excel writer 48x48 color

Excel File Writer

Data needs written to an Excel file

metl writer 48x48 color

RDBMS Writer

Data needs written to a relational database

metl sql execute 48x48 color

SQL Executor

Data needs written to or deleted from a database using a specific sql statement

metl text writer 48x48 color

Text File Writer

Data needs written to a text based file

metl zip 48x48 color

Zip

Data needs to be put in a compressed file

Services

metl web 48x48 color

Http Request

A RESTful service needs to be created and hosted by the Metl server

metl web 48x48 color

Http Response

A RESTful service needs to be created and hosted by the Metl server

metl subscriber 48x48 color

Subscriber

A component that registers with a pub/sub system (like JMS). Works with resources that support subscriptions

Controls

metl gate 48x48 color

Gate

Flow needs controlled by one input before remaining process continues

metl delay 48x48 color

Last Unit of Work

Flow needs to be held at this point until all inputs complete before continuing

4.5.1. Readers

Binary File Reader

Icon

metl binary reader 48x48 color

Use When

Data needs read from a binary file

Samples

Description

The Binary File Reader is used to read data from one or more binary based files.

The Binary File Reader can read one or more files and pass the data read to downstream components. The file name(s) and path(s) of the file(s) to be read can be specified in the File Path parameter below, or can be specified in the payload of the inbound message.

Inbound Message Type

Text Based Message

Output Message Type

Binary Based Message

Properties
Name Description

Resource

The file based resource (either local or remote) on which the file to be read resides.

Enabled

Run When

Get File Name From Message

If selected, the path and file name to be read will be determined by the payload of the incoming message. This option is used frequently with the File Poller component. If this setting is not selected, the path and file to be read is based on the File Path property below.

Must Exist

Whether the file must exist

File Path

The relative path and file of the file to be read. The relative path and file are concatenated with the absolute path specified in the File based resource.

Action On Success

Archive On Success Path

Action On Error

Archive On Error Path

Size / Message (MB)

TODO: implement if the file is bigger than the allowable message size

Send Control Message on EOF

Whether to send a control message at EOF for each file read

Log Input

Log Output

Inbound Queue Capacity

Excel File Reader

Icon

excel 48x48 color

Use When

Data needs read from an Excel file

Samples

Description

The Excel File Reader is used to read data from one or more Excel based files.

The Excel File Reader can read one or more files and pass the data read to downstream components. The file name(s) and path(s) of the file(s) to be read can be specified in the File Path parameter below, or can be specified in the payload of the inbound message. Excel files that are read are parsed as defined within the component editor.

Inbound Message Type

Text Based Message

Output Message Type

Entity Based Message

Properties
Name Description

Resource

The file based resource (either local or remote) on which the file to be read resides.

Output Model

Enabled

Run When

Get File Name From Message

If selected, the path and file name to be read will be determined by the payload of the incoming message. This option is used frequently with the File Poller component. If this setting is not selected, the path and file to be read is based on the File Path property below.

Must Exist

Whether the file to read must exist for the flow to execute.

Ignore on Formula Error

Whether to substitute null values for cells containing an error.

File Path

The relative path and file of the file to be read. The relative path and file are concatenated with the absolute path specified in the File based resource.

Rows Per Message

Records in the Excel file reader are defined by a row of data.

Action On Success

Archive On Success Path

Action On Error

Archive On Error Path

Header Lines to Skip

The number of header lines in the file that should be skipped and not sent to downstream components

Send Control Message on EOF

Whether to send a control message after receiving an EOF

Log Input

Log Output

Inbound Queue Capacity

Specifying None on the Action on Error does NOT stop the flow from failing when a file cannot be read. It simply specifies what should be done with the file when the error condition does occur.
Component Editor

Double clicking on the Excel File Reader component in the flow will result in the Excel File Reader editor being displayed as shown below.

excel file reader editor

The editor displays a row for every entity and attribute of the output model, and allows setting the Sheet:Column of each.

The syntax is: Sheet Name:Column Letter

Sheet Name is the exact name as it appears on the tab to be imported.

Column Letter is the corresponding column the desired data exists in.

File Poller

Icon

metl file poller 48x48 color

Use When

A file based resource needs to be checked for the existence of a file or set of files

Samples

Description

The File Poller is used to look at a file based directory structure and send a message to downstream components if a file or files match a given file pattern exists. The file poller sends a payload of an array list of strings with the file names of each file found that matches the File Poller’s File Pattern parameter. The File pattern parameter can be a single pattern or a list of patterns separated by commas. Wildcards can be used in any pattern.

Inbound Message Type

None

Output Message Type

Text Based Message

Properties
Name Description

Resource

The File based resource on which the File Poller should operate

Input Model

Enabled

Run When

Get File Pattern From Message

Get the file pattern from an inbound text message versus the File Pattern parameter below.

File Pattern

The File Pattern specifies the pattern(s) of files to look for. Multiple patterns can be specified in a comma delimited list. Wildcards (*) can be used. See examples below.

Cancel On No Files

File Sort Order

Indicates the order files will be reported.

Sort Descending

Order the sort in descending order.

Files/Msg

The number of file paths that should be published per message. The default value is 1.

Min Files to Poll

Files will not be reported unless at least this number of files are found

Max Files to Poll

No more than this number of files will be reported during on execution

Action On Success

Archive On Success Path

Action On Error

Archive On Error Path

Use Trigger File

Many systems post a single trigger file to designate that other data files are ready to process. The existence of the trigger file indicates the other files are ready to process. If "Use Trigger File" is true, the File Poller will first check for the existence of a file named the same as the Relative Trigger File Path parameter. If that file exists, the File Poller will then check for files matching the File Pattern parameter. If the trigger file does not exist, the File Poller will send a shutdown message to any downstream linked components. If the Use Trigger File is set to false, no file triggering mechanism will be used

Relative Trigger File Path

The relative path and file name of a trigger file that will be used to indicate the files that match the File Pattern parameter are ready for processing.

Log Input

Log Output

Inbound Queue Capacity

Example 1. Search for all .csv files
*.csv
Example 2. Search for all.csv and all .txt files
*.csv,*.txt
RDBMS Reader

Icon

metl reader 48x48 color

Use When

Data needs read from a relational database

Samples

Relational Database to Flat File, Normalized Table to Denormalized File, Normalized Table to Denormalized File v2

Description

The RDBMS Reader is used to read data from a sql compliant relational database by specifying a SQL query.

The RDBMS Reader can take one or more input messages. The reader will be executed (i.e. the sql query run) based on the 'Run When' option. One time, once for every input message or once for every entity record within an input message that it receives. Any entity/attribute within an input message can be used as a parameter in the SQL parameter by specifying the input model entity and attribute name in the query prefaced with a colon. See Example 1 below.

By default, the sql results will be mapped directly to the output model based on the table and column name of the selected fields. When aggregate or other functions are used within the sql statement the table and column name will not be available for mapping to the output model, so hints may be used to map the field directly to an output model entity and attribute. See Example 2 below.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Resource

The SQL Database Resource on which the SQL query should be run to read data.

Input Model

Output Model

Enabled

Sql

The sql query or script that will be executed for this reader. May have one or more statements to execute.

Run When

This component also has the option of PER ENTITY. This means that the component will execute SQL for each entity.

Unit of Work

When the 'Run When' option is PER UNIT OF WORK this defines when an 'end of transaction' (control message) is forwarded to downstream components.

COMPONENT LIFETIME - Send one final control message after the entire SQL content has been completed

SQL SCRIPT - Send a control message once per SQL script completed

SQL STATEMENT - Send a control message after each SQL statement completed

Rows Per Message

Trim Columns

Whether leading and trailing spaces should be trimmed from character fields after being selected from the database and before they are placed in the output model format

Match On Column Name

Whether when matching SQL results to the output model, you match on table and column or column name only

Pass Input Rows Through

Whether to forward input messages along with the output SQL results

Log Input

Log Output

Inbound Queue Capacity

Example 1. Using Input Message Entity/Attribute as a parameter
select
   field1,
   field2
from
   mytable
where
   field3=:INPUT_MODEL_ENTITY_NAME.INPUT_MODEL_ATTRIBUTE_NAME

In the example above, the SQL Reader query will be run for each input record within each input message. Each time the query is run, field3 will be restricted by the incoming row’s INPUT_MODEL_ENTITY_NAME.INPUT_MODEL_ATTRIBUTE field.

All parameters in the sql statement should be prefixed with a colon. Parameters prefixed with a colon will use a prepared statement parameter to execute the query with the parameter. Using the deprecated $(parameter_name) will use string substitution versus a prepared statement parameter and is not recommended.
Example 2. Using Hints to Map to the Output Model
select
   field1,
   field2,
   count(*) /* OUTPUTMODEL_ENTITY_NAME.OUTPUT_MODEL_ATTRIBUTE_NAME */
from
   mytable

In the example above, the results from the query execution will be mapped to:

  1. field1 ⇒ MYTABLE.FIELD1

  2. field2 ⇒ MYTABLE.FIELD2

  3. count(*) ⇒ OUTPUTMODEL_ENTITY_NAME.OUTPUT_MODEL_ATTRIBUTE_NAME

If a single result set / query results from several queries using the union operator, only place the sql column hints on the first query’s columns, not each set of columns in every sql. Also, hints need to directly follow the field they correspond to prior to any comma if other fields follow the hint.
Text Constant

Icon

metl text constant 48x48 color

Use When

A text constant is needed in message content in the flow

Samples

Description

The Text Constant allows a constant text literal to be placed in the payload of a message and sent to downstream components.

Inbound Message Type

Any Message Type

Output Message Type

Text Based Message

Properties
Name Description

Enabled

Run When

Text

The text constant that will be placed into the message payload and sent to downstream systems

Split On Line Feed

Whether to generate separate entities based on line feeds in the Text box entries or send as one entity regardless of line feeds

Rows Per Message

Send Control Message on Text Send

If Run When is PER MESSAGE this option will trigger a control message to be sent after each text message sent

Log Input

Log Output

Inbound Queue Capacity

Example 1. Sample Text Constant Text
4,Diane,Prince,F
3,Clark,Kent,M
1,Peter,Parker,M
2,Bruce,Wayne,M
Text File Reader

Icon

metl text file 48x48 color

Use When

Data needs read from a text file

Samples

Flat File to Relational Database

Description

The Text File Reader is used to read data from one or more text based files.

The Text File Reader can read one or more files and pass the data read to downstream components. The file name(s) and path(s) of the file(s) to be read can be specified in the File Path parameter below, or can be specified in the payload of the inbound message. Text files that are read can be in any format (fixed length, comma delimited, etc.) and can be parsed by downstream processing components like the Parse Delimited and Parse Fixed components.

Inbound Message Type

Text Based Message

Output Message Type

Text Based Message

Properties
Name Description

Resource

The file based resource (either local or remote) on which the file to be read resides.

Enabled

Run When

Get File Name From Message

If selected, the path and file name to be read will be determined by the payload of the incoming message. This option is used frequently with the File Poller component. If this setting is not selected, the path and file to be read is based on the File Path property below.

Must Exist

Whether the file to read must exist for the flow to execute.

File Path

The relative path and file of the file to be read. The relative path and file are concatenated with the absolute path specified in the File based resource.

Rows Per Message

Records in the text file reader are defined by a line of data up to the files row delimiter (i.e. <CR> or <CR><LF>)

Action On Success

Archive On Success Path

Action On Error

Archive On Error Path

Encoding

Header Lines to Skip

The number of header lines in the file that should be skipped and not sent to downstream components

Send Control Message on EOF

Whether to send a control message after receiving an EOF

Log Input

Log Output

Inbound Queue Capacity

Specifying None on the Action on Error does NOT stop the flow from failing when a file cannot be read. It simply specifies what should be done with the file when the error condition does occur.
Unzip

Icon

metl zip 48x48 color

Use When

A compressed zip file needs to be extracted in order to process its content

Samples

Description

The Unzip component allows a compressed archive/zip file to be extracted so that its contents can be processed in the flow.

Inbound Message Type

Text Based Message

Output Message Type

Text Based Message

Properties
Name Description

Enabled

Zip Source Directory Resource

The directory where the zip file will be polled at

Unzip Target Directory Resource

The target file resource where extracted files will be written

Target Path

The path to where the unzipped files will be written

Create Sub Dir From File Name

Whether to create a root sub-directory for the extracted files that will match the zip file name

Extract Empty Files

Whether to extract empty files

Overwrite

Whether to overwrite the target file if it already exists

Encoding

Delete Source File

Whether the source zip file should be deleted after its contents have been extracted

Log Input

Log Output

Inbound Queue Capacity

XML File Reader

Icon

metl xml formatter 48x48 color

Use When

An XML file needs to be read and element content of that file sent as individual messages

Samples

Description

The XML Reader component allows XML data to be read from a file and data within tags to be sent as individual messages

Inbound Message Type

Text Based Message

Output Message Type

Text Based Message

Properties
Name Description

Resource

The file based resource (either local or remote) on which the file to be read resides.

Enabled

Run When

Get File Name From Message

If selected, the path and file name to be read will be determined by the payload of the incoming message. This option is used frequently with the File Poller component. If this setting is not selected, the path and file to be read is based on the File Path property below.

File Path

The relative path and file name for the file to read unless relative path and file name come from the inbound message

Tag to find and send, defaults to root

The tag that encloses data to be sent as individual messages

Number of read tags to send together

The number of instances of tags from above to be packed into a single message before sending the message

Log Input

Log Output

Inbound Queue Capacity

4.5.2. Processors

Assert

Icon

metl assert 48x48 color

Use When

A flow needs to check for certain assumptions

Samples

Description

The Assert component is used to test certain assumptions about the flow.

The Assert can check for a specified number of messages by the various types or by a count of records from a defined SQL statement.

If any of the checks fail in the Assert it will Fail the flow.

Inbound Message Type

Any Message Type

Output Message Type

Any Message Type

Properties
Name Description

Input Model

Output Model

Enabled

Expected Number of Entity Messages

Number of entity messages expected.

Expected Number of Entities Per Entity Message

Number of entities per entity message expected.

Expected Number of Text Messages

Number of empty payload messages expected.

Expected Number of Binary Messages

Number of binary messages expected.

Expected Number of Control Messages

Number of control messages expected.

Expected Number of Empty Payload Messages

Number of empty payload messages expected.

Assert DataSource

The SQL Database Resource on which the SQL query should be run to read data.

Assert Sql

The SQL query used to check for assertion.

Expected Count from Assert Sql

Number of records expected returned from the Assert Sql.

Log Input

Log Output

Inbound Queue Capacity

Content Router

Icon

metl content router 48x48 color

Use When

Content within a message needs to be routed to different downstream components in the existing flow

Samples

Partition By Gender

Description

The content router routes content within a message to one or more downstream components based on the routing criteria configured in the component.

Inbound Message Type

Any Message Type

Output Message Type

Any Message Type

Properties
Name Description

Input Model

Enabled

Rows Per Message

Only Route First Match

If checked, the content router will only route an inbound content to the first criteria in the criteria list that evaluates to true, not all criteria in the list that evaluate to true.

Log Input

Log Output

Inbound Queue Capacity

Routing Criteria
Expression

A groovy script expression that returns true or false. The groovy script is executed/evaluated for each record in an inbound message. If the expression evaluates to true, the message is passed to the step at the right. If the expression is evaluates to false, the message is NOT passed to the step at the right.

Target Step

The target step is the connected downstream component that will receive the message if the groovy expression evaluates to true.

The inbound message will go to ALL target steps where the expression evaluates to true (i.e. a message can go to more than one target step) unless the "Only Route First Match" option is checked.
Example 1. Sample groovy expressions for an entity based message
if (ENTITY.ATTRIBUTE=='Value 1') return true;
if (ENTITY.ATTRIBUTE=='Value 2') return true;
If an entity based message has multiple entities within a single record, the record will be treated as a whole, not as its individual entities within the record. This means that the entire record (including all of its entities) will be delivered to a downstream component if the groovy expression evaluates to true, and NONE Of the entities will be delivered to the downstream component if the expression evaluates to false. For example, if the Person model contains entities NAME and ADDRESS, and the groovy expression is something like "NAME.FIRST_NAME='TOM'", a record with NAME.FIRST_NAME='TOM' will send both the NAME and ADDRESS entities to the downstream component. A record with NAME.FIRST_NAME='BOB' will send NEITHER the NAME nor ADDRESS entities of the record to the downstream component.
Example 2. Sample groovy expressions for a text based message
if (text.indexOf("Some text string") > 0) return true;
if (text.indexOf("Some other text string") > 0) return true;
Example 3. Sample groovy expressions for routing based on a message header. This example tests to see if the header variable is available before testing it
binding.variables.containsKey('chainId') && chainId.equals('001')
Example 4. Sample groovy expressions for routing based on the change type of an entity message - whether the entity message is passed to downstream components as in Add, Change or Delete ('ADD', 'CHG', or 'DEL')
CHANGE_TYPE == 'DEL'
Example 5. Sample groovy expressions for whether a specific entity exists in a given message
ENTITY_NAMES.contains("my_entity_name")
Note that the expression can explicitly return true or false or can simply evaluate to true or false as shown in the examples above.
Model Data Diff

Icon

metl diff 48x48 color

Use When

A set of model based data needs compared to another set of model based data in order to generate insert/update/delete instructions for a writer component

Samples

Calculate Differences Only

Description

The Model Data Diff component compares two sets of model based data in order to generate insert/update/delete instructions for a writer component in order to get the model based data sets to match.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Source of Old Version

The source component that is sending data to the data diff component that represents the old or prior data for the compare

Source of New Version

The source component that is sending data to the data diff component that represents the new or current data for the compare

Rows Per Message

In Memory Compare

Whether the compare should be done in memory or on disk. If checked, the compare will be done completely in memory

Log Input

Log Output

Inbound Queue Capacity

Deduper

Icon

metl deduper 48x48 color

Use When

Input model based records must be deduplicated before being sent to downstream components

Samples

Remove Duplicates

Description

The Deduper removes duplicate records from a message or set of messages in a given unit of work. Once the complete unit of work has been received by the deduper, the unit of work will repackage the records from the inbound message(s) to a set of output messages based on the configured number of records per message, and send those deduped outbound messages to downstream component(s). Records may be deduped by Entity (full record) or Attribute (selected column(s)).

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Dedupe Type

This is the type of dedupe to execute. Options are ENTITY or ATTRIBUTE. Entity will execute a full record compare and Attribute will only look for duplicate records based on selected attributes defined through the component editor screen.

Preserve Record

Applicable only when dedupe type is Attribute. Options are 'First Record' or 'Last Record'. If a duplicate record is found and 'First Record' is chosen then the first matched record is forwarded. If 'Last Record' is chosen, then the final record matching the selected attribute(s) is forwarded and the earlier record(s) are dropped.

Rows Per Message

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Deduper component in the flow will result in the Deduper editor being displayed as shown below.

deduper editor

The Deduper editor displays the entities contained within the input model in a list. To selected which attribute to dedupe on, select the Entity the attribute desired belongs to and click the 'Edit Columns' button at the top.

In the window that appears, click the checkbox next to the attribute(s) to use as the dedupe key shown below.

deduper attribute editor

Delay

Icon

metl delay 48x48 color

Use When

A delay is needed when sending data between components

Samples

Description

The Delay causes messages being sent through the delay component to be delayed/paused for the configured number of seconds before being sent to downstream components.

Inbound Message Type

Any Message Type

Output Message Type

Any Message Type

Properties
Name Description

Input Model

Output Model

Enabled

Run When

Delay (ms)

The amount of time in milliseconds that the messages will be delayed before being passed to downstream components

Log Input

Log Output

Inbound Queue Capacity

Deserializer

Icon

testtube green 48

Use When

An entity message needs to be automatically parsed into a model from XML or JSON

Samples

If the structure is BY_TABLE, then the expected format will be as follows:

[{"name":"PERSON","rows":[{"GENDER":"M","ID":"1","LAST_NAME":"Arbuckle","FIRST_NAME":"Garfield"},{"GENDER":"M","ID":"2","LAST_NAME":"Arbuckle","FIRST_NAME":"Odie"},{"GENDER":"M","ID":"3","LAST_NAME":"Arbuckle","FIRST_NAME":"Jon"},{"GENDER":"F","ID":"4","LAST_NAME":"Wilson","FIRST_NAME":"Liz"},{"GENDER":"F","ID":"5","LAST_NAME":"Cat","FIRST_NAME":"Arlene"}]}]

If the structure is BY_INBOUND_ROW, then the expected format will be as follows:

[{"name":"PERSON","data":{"GENDER":"M","ID":"1","LAST_NAME":"Arbuckle","FIRST_NAME":"Garfield"}},{"name":"PERSON","data":{"GENDER":"M","ID":"2","LAST_NAME":"Arbuckle","FIRST_NAME":"Odie"}},{"name":"PERSON","data":{"GENDER":"M","ID":"3","LAST_NAME":"Arbuckle","FIRST_NAME":"Jon"}},{"name":"PERSON","data":{"GENDER":"F","ID":"4","LAST_NAME":"Wilson","FIRST_NAME":"Liz"}},{"name":"PERSON","data":{"GENDER":"F","ID":"5","LAST_NAME":"Cat","FIRST_NAME":"Arlene"}}]

Description

Parses JSON input into a model entities.

Outbound Message Type

Model Based Message

Properties
Name Description

Output Model

Enabled

Format

Can have values of AUTOMATIC, JSON or XML. Default is AUTOMATIC. If AUTOMATIC is chosen, then the deserializer will attempt to look at the flow parameters to determine what format should be used. If an Http Request started the flow, then the Accept header, the Content-Type header and the format parameter will all be inspected in order to determine the type.

Structure

Options for how to format the document. Options are: BY_TABLE and BY_INBOUND_ROW

Log Input

Log Output

Inbound Queue Capacity

Embed Flow

Icon

metl flow 48X48 color

Use When

A flow needs to be called from another flow

Samples

Description

The Call Flow component allows a flow to be called from another flow.

Inbound Message Type

Any Message Type

Output Message Type

Any Message Type

Properties
Name Description

Input Model

Output Model

Enabled

Flow

The flow to be called

Log Input

Log Output

Inbound Queue Capacity

Execute

Icon

metl execute 48x48 color

Use When

There is a need to execute an operating system command as part of a flow

Samples

Description

The execute component executes an operating system command on the host on which the agent is running.

Inbound Message Type

Any Message Type

Output Message Type

Text Based Message

Properties
Name Description

Enabled

Run When

Command

The operating system command to be executed for every inbound message to the component

Continue on Error

Whether the flow should continue if the Command fails to execute properly

Success Code

The code that should be returned in the text based outbound message if the Command is successful

Parameter replacement

Enables token replacement for the Command. Message headers and flow parameters can be token replaced. The following token formats can be used: $(HeaderKey) or $(FlowParameter). Note that you can link:Stamp entity and text message values in a message header if you want to use message values.

Log Input

Log Output

Inbound Queue Capacity

File Util

Icon

metl file util 48X48 color

Use When

Files need to be renamed, copied, moved or deleted

Samples

Description

The File Util component provides a sets of file utilities that can be used to manipulate files at the operating system level including copying, reanmeing, moving or deleting.

Inbound Message Type

Any Message Type

Properties
Name Description

Resource

The source file based resource (either local or remote) on which the source file(s) to be manipulated reside

Enabled

Run When

Action

Either Copy, Rename, Move, Delete or Touch. Copy will copy the file, move will move the file, delete will delete the file, and touch will update the files last updated date/time.

Source Path

The path and file name of the source file(s) to be manipulated. Wildcard character (*) can be used.

Target Path

The target path used to write files if the Action is Copy, Move, or Rename. This path will be appended to the path of specified Resource be specified as a path only, and the target file names will be derived from the source file names.

Get Input File Name From Message

Whether to get input file name from the payload of inbound messages or from the Source Path parameter. If selected, source file names come from the payload of inbound messages

Target File Name

The name to use for the target file. If not set, the original name will be used.

Append To Name

A text string that will be appended to the end of each target file name (before the extension).

Log Input

Log Output

Inbound Queue Capacity

Format Delimited

Icon

metl delimitedformatter 48x48 color

Use When

Model based data needs to be formatted into delimited text

Samples

Relational Database to Flat File

Description

The format delimited component takes a model based message and formats its content into a delimited text message

Inbound Message Type

Model Based Message

Output Message Type

Text Based Message

Properties
Name Description

Input Model

Enabled

Delimiter

The character that should be used to delimit the attribute values

Quote Character

The character that should be used to quote the attribute values that are of type String

Header line

Whether a header line should be created that contains attribute column names

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Format delimited component in the flow will result in the Format Delimited editor being displayed as shown below.

format delimited editor

The editor displays a row for every entity and attribute in the input model, and allows setting the ordinal position of each for the delimited output. Order the attributes in the order in which they should be displayed in the delimited result. Attributes can be ordered by:

Attribute Ordering Options
  • Selecting an attribute and dragging it up or down in the list

  • Selecting an attribute clicking the "Move Up", "Move Down", "Move Top" or "Move Botton" buttons

  • Selecting an attribute or set of attributes, clicking "Cut" to remove them and then "Pasting them elsewhere in the list

In addition to specifying the order of the attributes in the delimited list, transform functions can also be applied to each attribute. These transform functions are good for applying formatting, etc.

Format Fixed

Icon

metl fixed length 48x48 color

Use When

Model based data needs to be formatted into fixed length text

Samples

Relational Database to Flat File

Description

The format fixed component takes a model based message and formats its content into a fixed length text message

Inbound Message Type

Model Based Message

Output Message Type

Text Based Message

Properties
Name Description

Input Model

Enabled

Header line

Whether a header line should be created that contains attribute column names

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Format Fixed component in the flow will result in the Format Fixed editor being displayed as shown below.

format fixed editor

The editor displays a row for every entity and attribute in the input model, and allows setting the ordinal position and width of each attribute for the fixed length output. Order the attributes in the order in which they should be displayed in the delimited result. Attributes can be ordered by:

Attribute Ordering Options
  • Selecting an attribute and dragging it up or down in the list

  • Selecting an attribute clicking the "Move Up", "Move Down", "Move Top" or "Move Botton" buttons

  • Selecting an attribute or set of attributes, clicking "Cut" to remove them and then "Pasting them elsewhere in the list

Set the width by typing the width in to the Width field. Start and end positions are automatically calculated based on the order and width.

In addition to specifying the order and width of the attributes, transform functions can also be applied to each attribute. These transform functions are good for applying formatting, etc.

Format XML

Icon

metl xml formatter 48x48 color

Use When

Model based data needs to be formatted into xml text

Samples

Relational Database to Flat File, DB request to WSDL to DB results

Description

The format xml component takes a model based message and formats its content into an xml text message

Inbound Message Type

Model Based Message

Output Message Type

Text Based Message

Properties
Name Description

Input Model

Enabled

Ignore namespaces for XPath matching

XML Format

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Format XML component in the flow will result in the Format XML editor being displayed as shown below.

format xml editor

The first step in configuring the xml output is to specify the xml template that will be used to define the xml tag structure of the document. The XML template can be imported from an XSD or WSDL source that resides in a file or at a specified URL. Clicking the "Import Template" button opens the import dialog show below.

format xml editor import template

An XML template can also be manually entered by clicking the "Edit Template" button.

Once an XML template is defined XPath is used to map entities/attributes from the model to the xml template. See examples below.

Example 1. Format Person Entity to XML

XML Template

<Persons>
    <Person>
        <Id></Id>
        <FirstName></FirstName>
        <LastName></LastName>
        <Gender></Gender>
    </Person>
</Persons>

XPath Mapping

format xml editor

Lookup

Icon

metl lookup 48x48 color

Use When

Reference data exists that needs to be looked up / retrieved by a specified key, and another attribute of the the resulting reference data passed to downstream components.

Samples

Description

The Lookup component receives reference data from a source component, and then uses that reference data as a lookup source for data coming from another source component.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Lookup Data Source

The source component that will send reference data (to be looked up) to the Lookup component

Lookup Key Attribute

The model attribute in the reference data that will be used as the lookup key to the reference data

Lookup Key Value

The model attribute in the reference data that will be passed back from the lookup request

Replacement Key Attribute

The model attribute that will be used to lookup data from the Lookup Data Source. This value will be matched to the Lookup Key Attribute

Replacement Value Attribute

The model attribute that will be replaced by the Lookup Key Value from the resultant row in the Lookup Data Source

Rows Per Message

Log Input

Log Output

Inbound Queue Capacity

Ensure Lookup Key Attribute and Replacement Key Attribute are of the same data type, or the lookup will not work. I.E. model based messages are passed into the lookup component. These two keys must have the same data type in order to compare properly.
Mapping

Icon

metl mapping 48x48 color

Use When

Data needs to be mapped from one set of entities/attributes (model structure) to another set of entities/attributes

Samples

Flat File to Relational Database, Normalized Table to Denormalized File, Partition by Gender

Description

The mapping component allows you to map messages from one model structure to another.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Output Model

Enabled

Set Unmapped Attributes To Null

Whether attributes in the output model that don’t have a mapping specified from the input model should be set to null.

Entity Per Record

Determines whether inbound records will be split into multiple output records, one for each entity. Defaults to false. If set, the output model will have null values for every entity/attribute combination that is in the output model but not mapped from the input model. If not set, the output model will not contain entities/attributes that are not mapped from the input model.

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Mapping component in the flow will result in the Mapping editor being displayed as shown below.

mapping editor

The Mapping editor displays the input model on the left hand side of the screen, and the output model on the right hand side of the screen. Data can be mapped from the input model to the output mode by:

  • Clicking on an attribute in the input model and dragging until your mouse cursor is over the desired output model attribute

  • Clicking on an attribute in the input model, then clicking again on the desired output model attribute

For large models, typing an entity or attribute name in the Filter box will narrow the displayed model to entites or attributes matching the Filter criteria.

The AutoMap feature will look at both input and output models and attempt to map the models based on similarly named attributes.

Merger

Icon

metl merger 48x48 color

Use When

Different instances of model based records need to be merged into a single instance that combines the attributes from all instances.

Samples

Normalized Table to Denormalized File

Description

The Merger component takes different instances of entities (records) and merges them together based on a set of join criteria.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Component Editor

Double clicking on the Merger component in the flow will result in the Merger editor being displayed as shown below. The Merger editor displays a row for every entity/attribute in the input model and allows the selection of the columns on which to use as the key for the merge.

merger editor

Example 1. Sample Merge

Model with Entity DENORMALIZED_PERSON having the following attributes:

* ADDRESS1
* ADDRESS2
* CELL_PHONE
* CITY
* FIRST_NAME
* GENDER
* HOME_PHONE
* LAST_NAME
* POSTAL_CODE
* STATE
* WORK_PHONE

A merger component with two sources, Person and Address Mapper which sends the following columns:

* ADDRESS1
* ADDRESS2
* CITY
* FIRST_NAME
* GENDER
* LAST_NAME
* POSTAL_CODE
* STATE

The second source to the merger is the Home Phone Mapper which sends the following columns:

* FIRST_NAME
* LAST_NAME
* HOME_PHONE

The merge criteria selected on the Component Editor is

* FIRST_NAME
* LAST_NAME

The merger will merge rows with the same FIRST_NAME and LAST_NAME, combining their attributes into a single entity instance (record), and then forward that record to downstream components.

Message Logger

Icon

metl message logger 48x48 color

Use When

Messages need to be logged to the Metl console and log file.

Samples

List Files in Temp Directory, Remove Duplicates, Sort Records, WSDL Service Call

Description

The message logger component takes inbound messages and logs them to the Metl console and log file.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Qualify Entity Attributes with Entity Name

Whether attributes passed in a Model Based Message should be displayed with the Entity Name. If checked, the Entity Name will be displayed.

Log Input

Log Output

Inbound Queue Capacity

Multiplier

Icon

metl multiplier 48x48 color

Use When

Inbound messages need to be multiplied (duplicated n number of times) and then sent to downstream components.

Samples

Description

The multiplier component multiplies or duplicates an inbound message a number of times based on a second seed set of inbound messages.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Multiplier Source

The component that provides the multiplier. I.E. if an inbound record from another component should be multiplied n times, the Multiplier Source is the component that would send n records into the Muliplier component. The Multiplier Source are NOT the messages to be multiplied, but the messages that dictate how many times messgaes from other components will be multiplied.

Rows Per Message

Log Input

Log Output

Inbound Queue Capacity

No-Op

Icon

metl puzzle 48X48 color

Use When

A message needs to be passed through to another component without any action being taken on the message

Samples

Description

The No Op component passes messages to a downstream component without any modifications to the original message.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Parse Delimited

Icon

metl delimitedformatter 48x48 color

Use When

An inbound text message in some delimited text format needs to be parsed into a model based message structure

Description

The parse delimited component takes an inbound text message in delimited format and parses it into a model based outbound message.

Inbound Message Type

Text Based Message

Output Message Type

Model Based Message

Properties
Name Description

Output Model

Enabled

Delimiter

The character that represents the delimiter in the inbound text message. Delimiters can be specified with a simple character like a , or ^, or they can be specified by a hex string (for special characters) like \u00fd

Quote

The string or character that is used to quote string text in the inbound text message.

Encoding

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Parse delimited component in the flow will result in the Parse Delimited editor being displayed as shown below.

parse delimited editor

The editor displays a row for every entity and attribute in the output model, and allows setting the ordinal position of each for the delimited output. Order the attributes in the order in which they should be displayed in the delimited result. Attributes can be ordered by:

Attribute Ordering Options
  • Selecting an attribute and dragging it up or down in the list

  • Selecting an attribute clicking the "Move Up", "Move Down", "Move Top" or "Move Botton" buttons

  • Selecting an attribute or set of attributes, clicking "Cut" to remove them and then "Pasting them elsewhere in the list

In addition to specifying the order of the attributes in the delimited list, transform functions can also be applied to each attribute. These transform functions are good for applying formatting, etc.

Parse Fixed

Icon

metl fixed length 48x48 color

Use When

An inbound text message in a fixed length text format needs to be parsed into a model based message structure

Samples

Description

The parse fixed length component takes an inbound text message in fixed length format and parses it into a model based outbound message.

Inbound Message Type

Text Based Message

Output Message Type

Model Based Message

Properties
Name Description

Output Model

Enabled

Delimiter

The character that represents the delimiter in the inbound text message

Quote

The character that is used to quote string text in the inbound text message

Encoding

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Parse Fixed component in the flow will result in the Parse Fixed editor being displayed as shown below.

parse fixed editor

The editor displays a row for every entity and attribute in the output model, and allows setting the ordinal position and width of each attribute for the fixed length output. Order the attributes in the order in which they should be displayed in the delimited result. Attributes can be ordered by:

Attribute Ordering Options
  • Selecting an attribute and dragging it up or down in the list

  • Selecting an attribute clicking the "Move Up", "Move Down", "Move Top" or "Move Botton" buttons

  • Selecting an attribute or set of attributes, clicking "Cut" to remove them and then "Pasting them elsewhere in the list

Set the width by typing the width in to the Width field. Start and end positions are automatically calculated based on the order and width.

In addition to specifying the order and width of the attributes, transform functions can also be applied to each attribute. These transform functions are good for applying formatting, etc.

Parse XML

Icon

metl xml formatter 48x48 color

Use When

An inbound text message in xml text format needs to be parsed into a model based message structure

Samples

DB Request to WSDL to DB Results

Description

The parse xml component takes an inbound text message in xml format and parses it into a model based outbound message.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Output Model

Enabled

Ignore namespaces for XPath matching

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Parse XML component in the flow will result in the Parse XML editor being displayed as shown below.

format xml editor

The first step in configuring the parser is to specify the xml template that will be used to define the xml tag structure of the document. The XML template can be imported from an XSD or WSDL source that resides in a file or at a specified URL. Clicking the "Import Template" button opens the import dialog show below.

format xml editor import template

An XML template can also be manually entered by clicking the "Edit Template" button.

Once an XML template is defined XPath is used to map entities/attributes from the model to the xml template. See examples below.

Example 1. Parse Person XML to Entity

XML Template

<Persons>
    <Person>
        <Id></Id>
        <FirstName></FirstName>
        <LastName></LastName>
        <Gender></Gender>
    </Person>
</Persons>

XPath Mapping

format xml editor

Script

Icon

metl script 48x48 color

Use When

A custom action should be performed that can be written using Groovy

Samples

See Script Templates

Description

The script component executes one or more configurable groovy scripts. Groovy scripts can be configured to run at different points in the component lifecycle including Component Initialization (onInit), Inbound message arrival (onHandleMessage), Successful flow completion (onSuccess), and Flow completion with error (onError). The different run points are implemented as methods in Script Helper. All methods and fields in ScriptHelper are available for use in your script.

There are several Templates that can be referenced or used as a starting point to building your script.

Inbound Message Type

Any Message Type

Output Message Type

Any Message Type

Properties
Name Description

Resource

A resource that can be accessed and utilized by the script.

Input Model

Output Model

Enabled

Import Statements

Import statements for external dependencies

Init Script

The script to be executed upon component initialization

Handle Msg Script

The script to be executed upon receipt of an inbound message

Ended with Success Script

The script to be executed upon successful completion of the flow

Ended with Error Script

The script to be executed upon a failed completion of the flow. Note: this will not be called if a flow uses an Assert component to capture an error. This only triggers on regular flow errors.

Log Input

Log Output

Inbound Queue Capacity

Example 1. Send Text Message
String body = "My Text Message Body";
callback.sendTextMessage(null, body);
Sequence

Icon

metl sequencing 48x48 color

Use When

A sequence number needs to be created for a given attribute in a model based record, and the starting value of the sequence can be derived by a sql statement.

Samples

Description

The sequence component allows an attribute in a model based message to be filled with a generated sequence number. The initial value for the sequence is derived based on a sql statement configured in the component. From that point forward, every new message/record that comes into the component gets an incremented sequence number placed in the designated sequence number attribute field.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Resource

The Database Resource that will be used to execute the Starting Sequence Sql

Input Model

Enabled

Sequence Attribute Name

The name of the attribute in form [entity].[attribute] that should be filled in with the sequence number generated

Select Starting Sequence Sql

The Sql statement that will be executed in order to retrieve the starting sequence value. This sql will be run once on component start up to retrieve the sequence seed value

Is Sequence Shared

Whether the sequence is shared amongst multiple sequence generators

Shared Name

If the sequence is shared, this is the name that each of the sequence generators will use to reference the shared sequence

Log Input

Log Output

Inbound Queue Capacity

Serializer

Icon

testtube orange 48

Use When

An entity message needs to be automatically formatted to JSON or XML

Samples

Format a model object to JSON prior to sending to a link:http-response(Http Response) for a web service

Description

Formats entity messages based on the input model

Inbound Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Format

Can have values of AUTOMATIC, JSON or XML. Default is AUTOMATIC. If AUTOMATIC is chosen, then the serializer will attempt to look at the flow parameters to determine what format should be used. If an Http Request started the flow, then the Accept header, the Content-Type header and the format parameter will all be inspected in order to determine the type.

Structure

Options for how to format the document. Options are: BY_TABLE and BY_INBOUND_ROW

Log Input

Log Output

Inbound Queue Capacity

Sorter

Icon

metl sorter 48x48 color

Use When

Data within a model based message needs to be sorted by one of the attributes within the model or data needs to be sorted by multiple attributes within an entity in the model

Samples

Description

The Sorter is used to sort data elements within a message that is being processed through a flow.

The sort can be defined at either the message level or by entity/attribute using the component editor screen. When sorting by entity/attribute the data will be sorted per entity, where each entity will be treated as though it is a separate database table and the ordering occurs across the selected attributes (columns).

NOTE: Using the entity/attribute sort option will use an H2 database to load the records into using the model entity as a table and the attributes as columns. Then it will perform an 'order by' in a sql statement to re-order the records. The tables will determine the key structure based on the way the entity is modeled in Metl and will use the key to load the H2 table(s). Word of caution, the key needs to be unique otherwise if your flow allows duplicate records this sort will remove these duplicates. Also, you may not have null values in the attributes you select as the key to the table.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Sort Entity.Attribute (optional)

The single model entity and attribute to sort the data by. The entity and attribute must be concatenated with a period (entity.attribute).

NOTE: Either an entry must be defined here or attributes selected in the Component Editor screen for the component to function. A value in this field overrides any attribute(s) selected on the Component Editor screen.

Rows Per Message

Log Input

Log Output

Inbound Queue Capacity

Component Editor

Double clicking on the Sorter component in the flow or clicking the Component Editor button will result in the Sorter editor being displayed as shown below.

sorter editor

The Sorter editor displays a row for every entity/attribute contained within the input model and allows the selection of the attributes on which to use as the sort along with the ability to order them.

When a sort checkbox for an attribute is selected, the screen will re-sort all selected attributes to the top by entity. To change the sort order, select one or more attributes within an entity and use the buttons at the top to Move the selected item(s) up, down, to the top or to the bottom.

You can only re-order the attributes within a single entity at any one time.
Format Fixed

Icon

metl temp rdbms 48x48 color

Use When

Model based data needs to be staged and queried.

Samples

Description

The Temp RDBMS component stages model based message data in a temporary database to be queried. This component is the same as the RDBMS Reader but instead of providing a data source, the data source is automatically created using the user provided Input Model. As model messages are received, they are loaded into the temporary database.

If the Run When parameter is configured to 'PER UNIT OF WORK', only one database is created and the SQL expression is executed after all model based messages have been received. If the Run When parameter is configured to 'PER MESSAGE', each new model based message will initiate the creation of a new temporary database. The provided SQL will be executed after each message has been loaded into the database. Once the SQL has been executed against the temporary database, the database is cleaned up and removed from memory or the file system.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Enabled

Run When

Sql

The sql query or script that will be executed for this reader. SQL hints are supported.

Rows Per Message

In Memory Database

Whether the temporary database should be created in memory or on the file system.

Log Input

Log Output

Inbound Queue Capacity

If you would like to see the contents of the temporary h2 database, replace the Temp RDBMS SQL with the text "script to '/home/user/temp_rdbms_db.sql'". When this runs, the database contents will be exported to the local file system. After the export completes, Metl will correctly throw an error since the query did not return any results. This should be done for debugging only.
Text Replace

Icon

metl regex replace 48x48 color

Use When

A piece of text in a text based message needs replaced with alternate text

Samples

Description

The text replace component uses a regular expression to replace a piece of text with another piece of text within a text based message.

Inbound Message Type

Text Based Message

Output Message Type

Text Based Message

Properties
Name Description

Enabled

Search For (regex)

A regular expression that describes the text for which to search

Replace With

A text string that should be used to replace the text found by the Search For (regex) parameter

Log Input

Log Output

Inbound Queue Capacity

Transformer

Icon

metl transform 48x48 color

Use When

An attribute in a model based message needs transformed in one way or another (trim, substring, constants, etc.)

Samples

Description

The transformer component allows transformation functions to be applied to record elements from inbound messages.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Component Editor

Double clicking on the Transformer in the flow will result in the Transformer editor being displayed as shown below.

transformer editor

The editor displays a row for every entity and attribute in the input model, and allows and allows transform functions to be applied to each. The following transform functions are available:

Function Description

abbreviate(maxwidth)

Abbreviates the attribute using ellipses. This will turn "Now is the time for all good men" into "Now is the time for…​"

currentdate()

Replaces the attribute with the current date and time

currentdate(format)

Replaces the attribute with the current system date and time formatted based on the format expression. See Date Format Expression for details on the date format specification.

daysFromNow(numberDays)

Replaces the attribute with the date of current date + numberDays

flowParameter(parameterName)

Replaces the attribute wih the flow parameter specified by name parameterName

format(spec)

Formats the attribute into a string according to the provided spec. See Java Formatter class for details on the specification string

formatdate(pattern)

Formats the date attribute according to the pattern parameter. See Date Format Expression for details on the date format specification.

getAttributeValueByName(attributeName)

Gets the value of the specified attribute. Could be used to reference a different attribute from within the current one.

integer()

Converts the attribute to an integer

left(length)

Truncates the attribute to the left-most length digits

lower()

Converts the attribute to lower case

lpad(padChar,length)

Left pads the attribute with length padChar characters

messageParameter(paramterName)

Replaces the attribute with the given message parameter specified by name parameterName

nullvalue()

Replaces the attribute with the null value

nvl(substituteForNull)

Conditionally replaces the attribute with substituteForNull value if the attribute is null

parseAndFormatDate(parsePattern,formatPattern)

Parses the string attribute into a date based on the parsePattern and then formats it into a date time string according to the formatPattern parameter

parseBigDecimal()

Parses the string attribute into a Big Decimal

parseDouble()

Parses the string attribute into a Double

parseInt()

Parses the string attribute into an Integer

parseLong()

Parses the string attribute into a Long

parsedate(pattern)

Parses the string into a Date based on the pattern. See Date Format Expression for details on the parse pattern

replace(searchString,replacement)

Find and replace searchString with replacement. Tip: You can chain replace() calls. e.g., replace("1","a").replace("2","b")

right(length)

Truncates the attribute to the right-most length digits

rpad(padChar,length)

Right pads the attribute with length padChar characters

sequence(seedValue, incrementValue, breakField)

Increments a sequence by incrementValue starting at seedValue. Restarts at seedValue when breakField changes. breakField is an attribute name for the current entity record.

substr(start,end)

Returns the substring of the value beginning with the character at the specified start index and extends to the end index - 1

stringConstant(value)

Replaces the attribute value with the constant value provided

trim()

Removes leading and trailing whitespace

upper()

Converts the attribute to uppercase

Format and parse expressions for dates can be defined using the following designations.

Letter Date or Time Component Presentation Examples

G

Era designator

Text

AD

y

Year

Year

1996; 96

M

Month in year

Month

July; Jul; 07

w

Week in year

Number

27

W

Week in month

Number

2

D

Day in year

Number

189

d

Day in month

Number

10

F

Day of week in month

Number

2

E

Day in week

Text

Tuesday; Tue

a

Am/pm marker

Text

PM

H

Hour in day (0-23)

Number

0

k

Hour in day (1-24)

Number

24

K

Hour in am/pm (0-11)

Number

0

h

Hour in am/pm (1-12)

Number

12

m

Minute in hour

Number

30

s

Second in minute

Number

55

S

Millisecond

Number

978

z

Time zone

General time zone

Pacific Standard Time; PST; GMT-08:00

Z

Time zone

RFC 822 time zone

-0800

Union

Icon

metl merger 48x48 color

Use When

Inbound messages need to be combined into a single message and then sent to downstream components.

Samples

Description

The union component combines all inbound messages from each input link and then sends one combined message to the downstream components. The combination of messages continues until all messages have been processed and the unit of work boundary is reached.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Web Request

Icon

metl web 48x48 color

Use When

A web service (either REST or WSDL) needs to be called to perform an action such as retrieving or writing data

Samples

WSDL Service Call, DB Request to WSDL to DB results

Description

The web component allows REST or WSDL based web services to be called, sending the response data to downstream components.

The Web component can be used to call either WSDL or REST based services. The Web component takes a text based message as input. This text based input message is formatted XML that represents the web service request. Thus, the web component is frequently used with the XML Formatter component which takes entity data and formats it into an xml formatted text message.

Inbound Message Type

Text Based Message

Output Message Type

Text Based Message

Properties
Resource An Http resource where the service resides and can be called

Enabled

Run When

When providing the web service request message set this to PER UNIT OF WORK. When the request message is received from a prior component set this to PER MESSAGE

Append to URL

Text that will be appended to to the Connection url forming the base url for the web service call

Body From

Either 'Message' or 'Provided'. If Message the body of the web service request message will come from the inbound message. If 'Provided" the body of the web service request message will come from the Body Text parameter

Body Text

If the Body From parameter is 'Provided', this parameter is used to specify the body of the text message for the web service request

Http Headers

A list of line feed separated Http Headers that will be passed in the web request. The name value pairs should be separated by a colon (:).

Http Parameters

A list of line feed separated Http Parameters that will be passed on the URL of the web request. The name value pairs should be separated by a colon (:).

Parameter replacement

Enables token replacement for the Command. Message headers and flow parameters can be token replaced. The following token formats can be used: $(HeaderKey) or $(FlowParameter). Note that you can link:Stamp entity and text message values in a message header if you want to use message values.

Log Input

Log Output

Inbound Queue Capacity

XML Formatter

Icon

metl xml formatter out 48x48 color

Description

The XML Formatter component takes an inbound model based message and formats into an xml formatted text message.

Inbound Message Type

Model Based Message

Output Message Type

Text Based Message

XSLT Processor

Icon

metl xslt 48x48 color

Use When

XSLT needs applied to a set of input data in order to transform it into a given output format

Samples

Description

The XSLT Processor component applies XSLT to a set of input data and sends the output of the XSLT processing as text messgaes to downstream components.

Inbound Message Type

Model Based Message

Output Message Type

Text Based Message

Properties
Input Model

Enabled

Output all attributes

XML Format

Parameter replacement

Enables token replacement for the Command. Message headers and flow parameters can be token replaced. The following token formats can be used: $(HeaderKey) or $(FlowParameter). Note that you can link:Stamp entity and text message values in a message header if you want to use message values.

Log Input

Log Output

Inbound Queue Capacity

4.5.3. Writers

Binary File Writer

Icon

metl binary writer 48x48 color

Use When

Data needs written to a binary based file

Samples

Description

The Binary File Writer is used to write data to a binary based file.

The Binary File Writer can write to one output file either locally or to a remote destination.

Inbound Message Type

Binary Based Message

Output Message Type

Text Based Message

Properties
Name Description

Resource

The file based resource (either local or remote) on which the file will be written.

Enabled

Path and File

The relative path and file of the file to be written. The relative path and file are concatenated with the absolute path specified in the File based resource

Must Exist

Requires the file to exist prior to writing

Append

Add input data to the end of an existing file

Get File Name From Message

If selected, the path and file name to be written will be determined by the payload of the incoming message. If this setting is not selected, the path and file to be read is based on the File Path property.

Param Name For Msg Based File Name

If 'Get File Name From Message' property is checked, this is the name of parameter to pull the filename from.

Log Input

Log Output

Inbound Queue Capacity

Email Writer

Icon

metl email 48x48 color

Use When

An email needs to be sent

Samples

You have a situation or business event where someone needs to be notified via email.

Description

The Email Writer is used to send one or multiple emails to one or multiple recipients.

The Email Writer can take one or more input messages. The writer will be executed according to the 'Run When' setting. Depending on the setting and the type of input message the email settings (subject, body, to …​) that have tokens will be filled out.

Attachments can also be included with the email message. The file(s) to be attached must be located on the local server of the Metl instance.

Inbound Message Type

Any

Output Message Type

Text Based Message

Properties
Name Description

Resource

A mail server session. If this is left blank, then the global mail server session will be used

Input Model

Enabled

Subject

The subject line for the email. This can be templated

Body

The body text for the email. This can be templated

FROM:

The from line for the email. If this is not set, the value will be used that is configured in the resource

TO:

The TO line for the email. This can be templated. The TO values can also be provided from a source step

CC:

The CC line for the email. This can be templated. The CC values can also be provided from a source step

BCC:

The BCC line for the email. This can be templated. The BCC values can also be provided from a source step

Source Step For Email Addresses

This is optional. If the recipients come from a database or source file this can be used to feed them to the component

Source Step Email Addresses Type

How to use the source recipients. Values can be TO, CC, and BCC

One Email per Recipient

Setting that indicates if multiple emails should be sent or just one

Include Attachment

Setting that indicates if an attachment should be included with the email sent

Attachment File Full Path

Fully qualified local server file path. To include multiple attachments include a pipe '|' between files.

For example: C:\Temp\testfile.txt|C:\Some Folder\My Filename Here.pdf

Run When

When this component should send email

Log Input

Log Output

Inbound Queue Capacity

Excel File Writer

Icon

metl excel writer 48x48 color

Use When

Data needs written to an Excel based file

Samples

Description

The Excel File Writer is used to write data to an Excel based file.

The Excel File Writer can write to one output file either locally or to a remote destination. Excel files that are written can be in either Microsoft Excel (.xls) or Microsoft Excel XML (.xlsx) format.

Note: The current version of the Excel File Writer only allows the writing of a single tab in a workbook and does not allow updates to a file, it will only replace the file if the file already exists.

Inbound Message Type

Entity Based Message

Output Message Type

Text Based Message

Properties
Name Description

Resource

The file based resource (either local or remote) on which the file will be written.

Input Model

Enabled

Excel Output Type

The type of Excel file to generate. (.xls or .xlsx)

Must Exist

Requires the file to exist prior to writing

Empty File On No Input

Output an empty file if no input messages occur

Path and File

The relative path and file of the file to be written. The relative path and file are concatenated with the absolute path specified in the File based resource. Note, the extension must match the output type selected or flow will error. Also, if no extension is provided the component will automatically add the proper extension based on the type.

Sheet Name

String to use as the Excel worksheet (tab) name. Must follow Excel guidelines for naming. If invalid characters are identified they will be replaced with spaces. Not providing will use the default of Sheet1.

Include Header Row

Whether to add a header row with attribute names as column headings in the output file.

Get File Name From Message

If selected, the path and file name to be read will be determined by the payload of the incoming message. If this setting is not selected, the path and file to be read is based on the File Path property.

Param Name For Msg Based File Name

Name of the parameter containing the file to be read.

Log Input

Log Output

Inbound Queue Capacity

Notes

General notes about this component.

The output rows/columns will start in cell A1.
RDBMS Writer

Icon

metl writer 48x48 color

Use When

Data needs written to a relational database

Samples

Flat File to Relational Database, DB request to WSDL to DB results

Description

The RDBMS Writer is used to write data to a SQL compliant relational database by specifying an input model.

The RDBMS Writer can take one or more input messages. The writer will be executed (i.e. the update and/or insert sql query run) for every entity record within an input message that it receives.

By default, input records will attempt to be inserted into the defined Input Model. Parameters can be selected that will allow an update only or a combination of an update and insert in either order depending on selections.

Inbound Message Type

Model Based Message

Output Message Type

Text Based Message

Properties
Name Description

Resource

The SQL Database Resource on which the SQL query should be run to write the data.

Input Model

Enabled

Catalog

The catalog where the target table resides

Schema

The schema where the target table resides

Replace rows if they exist

Whether to execute an update sql if a unique key exception occurs on the default insert sql attempt

Update rows first instead of insert

Whether to execute an update sql first

Fallback to insert if no rows updated

Whether to execute an insert sql if at first the update sql updates no rows

Quote table and column names

Whether or not to include table and column names in quotes

Trim character data to fit within column

Whether to trim the character field data if the incoming values length is larger than the target column size

Continue on Error

Whether to continue processing records on a failure and count the error record in the ignore count

Batch Mode

Whether to execute the insert and/or update as a transaction allowing rollback capability upon failure (assuming the RDBMS is not an auto commit system)

Create table if missing

Whether to create the target table if it does not exist on the database.

Log Input

Log Output

Inbound Queue Capacity

Specifying both a Catalog and Schema, the flow will only use the Catalog value and ignore the Schema Specifying 'Fallback to insert…​' has no affect unless 'Update rows first…​' is also selected When no options are checked the default is to attempt an insert only
Sql Executor

Icon

metl sql execute 48x48 color

Use When

Data needs written to or deleted from a database using a specific sql statement

Samples

Description

The Sql Executor is used to write data to a SQL compliant relational database by specifying a sql query.

The Sql Executor can take one or more input messages. The sql will be executed based on the 'Run When' option, either Per Message - one time per message received, Per Entity - for every entity record in an input message or On Success - one time at flow completion.

Inbound Message Type

Model Based Message

Output Message Type

Text Based Message

Properties
Name Description

Resource

The SQL Database Resource on which the SQL query should be run to write the data.

Input Model

Enabled

Run When

Timing of when to run the defined sql. Whether the sql executes after each message received, once per entity record in a message or on flow completion.

File

File containing Sql statements to run. If a value is included here it will be used instead of any Sql Statements in the next box.

Sql

Sql statement(s) to run. Could be an insert, update or delete statement. Multiple statements can be executed by including a semi-colon (;) between sql statements.

Log Input

Log Output

Inbound Queue Capacity

Text File Writer

Icon

metl text writer 48x48 color

Use When

Data needs written to a text based file

Samples

Description

The Text File Writer is used to write data to a text based file.

The Text File Writer can write to one output file either locally or to a remote destination. Text files that are written can be in any format (fixed length, comma delimited, etc.) and can be formatted by an upstream processing component like the Format Delimited and Format Fixed components.

Inbound Message Type

Text Based Message

Output Message Type

Text Based Message

Properties
Name Description

Resource

The file based resource (either local or remote) on which the file will be written.

Enabled

Path and File

The relative path and file of the file to be written. The relative path and file are concatenated with the absolute path specified in the File based resource

Must Exist

Requires the file to exist prior to writing

Append

Add input data to the end of an existing file

Line Terminator

String to append to the end of each line written to the file

Encoding

Get File Name From Message

If selected, the path and file name to be read will be determined by the payload of the incoming message. If this setting is not selected, the path and file to be read is based on the File Path property.

Param Name For Msg Based File Name

Name of the parameter containing the file to be read.

Empty File On No Input

Output an empty file if no input messages occur

Log Input

Log Output

Inbound Queue Capacity

Specifying None on the Action on Error does NOT stop the flow from failing when a file cannot be written. It simply specifies what should be done with the file when the error condition does occur.
Zip

Icon

metl zip 48x48 color

Use When

Data needs to be put in a compressed file

Samples

Description

The Zip component is used to generate a compressed file of data provided to it.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Source Directory Resource

The file based resource (either local or remote) on which the source files reside.

Target Directory Resource

The file based resource (either local or remote) on which the compressed file will be written.

Enabled

File Path

The relative path and file of the zip file to be written. The relative path and file are concatenated with the absolute path specified in the File based resource.

Delete Source Files

Whether to delete the source files upon completion of creating the compressed file

Encoding

File encoding type, defaults to UTF-8

Log Input

Log Output

Inbound Queue Capacity

4.5.4. Services

Http Request

Icon

metl web 48x48 color

Use When

A RESTful service needs to be created and hosted by the Metl server

Samples

Service Get All Persons, Service Get All Persons Custom XML, Service Get Person By Id, Service Upsert Persons

Description

The HTTP Request component is used to define the expected inbound HTTP Request for a RESTful service that will be hosted by the Metl server

Inbound Message Type

None

Output Message Type

Any

Properties
Name Description

Output Model

If an output model is specified, Metl will attempt to automatically unmarshal the payload of the inbound message (either xml or json) into the structure specified by the output model definition. See Example 1 below.

Enabled

HTTP Method

The http method for which http requests will be directed to this component. Either, GET, PUT, POST or DELETE.

Path

The path definition for which http requests will be directed to this component. This is a partial path that will be used in conjunction with the base metl path. I.E. if Metl is accessed at http://myserver:42000/metl and the path specIfied is /customer/listAll, then HTTP requests to http://myserver:42000/metl/api/ws/customer/listAll will be directed to this component and flow.

Parameters for a service request can be specified in one of two ways. The first is with a standard http request parameter. For example, with a path of /customer/getById and a base Metl url of http://myserver:42000/metl, http requests to http://myserver/api/ws/customer/getById?customerId=1234 will pass http requests to the component with customerId available as a parameter. Parameters can be accessed by enclosing them in $(). I.E. $(customerId).

The second way to specify a parameter is within the url itself. I.E. with the following path /customer/getById/{customerId} and base metl url of http://myserver:42000/metl, requets to http://myserver/api/ws/customer/getById/1234 will also pass http requests to the component with customerId available as a parameter.

Log Input

Log Output

Inbound Queue Capacity

Example 1. Automatic unmarshelling of xml or json in the http request component

As discussed above, the http request component will attempt to automatically unmarshal the payload of the inbound message (either xml or json) if the Output Model is specified. The format of the xml or json must look as follows to be successfully unmarshalled.

XML Example

<ArrayList>
	<item>
		<name>PERSON</name>   <!-- The model entity -->
		<data>
			<GENDER>M</GENDER>  <!== The model attributes -->
			<ID>1</ID>
			<LAST_NAME>Arbuckle</LAST_NAME>
			<FIRST_NAME>Garfield</FIRST_NAME>
		</data>
	</item>
	<item>
		<name>PERSON</name>	<!-- The second row of entity and attributes -->
		<data>
			<GENDER>M</GENDER>
			<ID>2</ID>
			<LAST_NAME>Arbuckle</LAST_NAME>
			<FIRST_NAME>Odie</FIRST_NAME>
		</data>
	</item>
</ArrayList>

JSON Example

[
	{
		"name":"PERSON",
		"data":
		{
			"GENDER":"M",
			"ID":"1",
			"LAST_NAME":"Arbuckle",
			"FIRST_NAME":"Garfield"
		}
	},
	{
		"name":"PERSON",
		"data":
		{
			"GENDER":"M",
			"ID":"2",
			"LAST_NAME":"Arbuckle",
			"FIRST_NAME":"Odie"
		}
	}
]
Http Response

Icon

metl web 48x48 color

Use When

A RESTful service needs to be created and hosted by the Metl server

Samples

Service Get All Persons, Service Get All Persons Custom XML, Service Get Person By Id, Service Upsert Persons

Description

The HTTP Response component is used to define the expected outbound HTTP Response for a RESTful service that will be hosted by the Metl server

Inbound Message Type

None

Output Message Type

Any

Properties
Name Description

Input Model

If an input model is specified, Metl will attempt to automatically marshal the payload of the entity based inbound message to the http response component into a json or xml outbound message See Example 1 below.

Enabled

Content Type

The content type of the output. If an input model is specified, and the http response component is automatically marshalling the output, the content type will be set automatically based on the accept parameter of the request. If no input model is specified and the flow is formatting the outbound message as part of the flow, the content type should be set as approproiate.

Log Input

Log Output

Inbound Queue Capacity

Example 1. Automatic marshelling of xml or json in the http response component

As discussed above, if the Inbound Model is specified, the http response component will attempt to automatically marshal the payload of the inbound entity message into either an xml or json outbound payload. The format of the xml or json will look as follows:

XML Example

<ArrayList>
	<item>
		<name>PERSON</name>   <!-- The model entity -->
		<data>
			<GENDER>M</GENDER>  <!== The model attributes -->
			<ID>1</ID>
			<LAST_NAME>Arbuckle</LAST_NAME>
			<FIRST_NAME>Garfield</FIRST_NAME>
		</data>
	</item>
	<item>
		<name>PERSON</name>	<!-- The second row of entity and attributes -->
		<data>
			<GENDER>M</GENDER>
			<ID>2</ID>
			<LAST_NAME>Arbuckle</LAST_NAME>
			<FIRST_NAME>Odie</FIRST_NAME>
		</data>
	</item>
</ArrayList>

JSON Example

[
	{
		"name":"PERSON",
		"data":
		{
			"GENDER":"M",
			"ID":"1",
			"LAST_NAME":"Arbuckle",
			"FIRST_NAME":"Garfield"
		}
	},
	{
		"name":"PERSON",
		"data":
		{
			"GENDER":"M",
			"ID":"2",
			"LAST_NAME":"Arbuckle",
			"FIRST_NAME":"Odie"
		}
	}
]

If an input model is NOT specified, the flow will need to format the output in either xml or json format accordingly.

Subscriber

Icon

metl subscriber 48x48 color

Use When

You need to consume messages from a Pub/Sub system (like JMS)

Samples

Description

This component requires a resource that supports subscriptions. When enabled, it will subscribe with the resource. Each message it receives results in an execution of a flow.

Inbound Message Type

None

Output Message Type

Text

Properties
Name Description

Enabled

Log Input

Log Output

Inbound Queue Capacity

4.5.5. Controls

Gate

Icon

metl gate 48x48 color

Use When

A component(s) needs to wait until another component has completely finished

Samples

Service Get All Persons, Service Get All Persons Custom XML, Service Get Person By Id, Service Upsert Persons

Description

The Gate component passes all incoming messages except for messages from the control source component to a downstream component once the gate component receives the final unit of work control message from the control source.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

Properties
Name Description

Input Model

Output Model

Enabled

Gate Control Source

The source component that controls the opening of the gate

Log Input

Log Output

Inbound Queue Capacity

Last Unit of Work

Icon

metl delay 48x48 color

Use When

A component needs to wait until one or more other components complete prior to continuing

Samples

Description

The Last Unit of Work component waits until all end of work control messages are received from all input components before a message is forwarded to the remaining downstream components.

Inbound Message Type

Model Based Message

Output Message Type

Model Based Message

4.5.6. Common Properties

Action on Error

Either None, Archive or Delete. If Delete, the input file will be deleted if there is an error when reading. If Archive, the input file will be archived to the Archive on Error Path property location if there is an error when reading. If None, the input file will be left in its original position if there is an error when reading.

Archive on Error Path

If Action on Error is set to Archive, this property sets the archive location where the input file should be archived if there is an error when reading.

Action on Success

Either None, Archive or Delete. If Delete, the input file will be deleted after it is successfully read. If Archive the input file will be archived to the Archive on Success Path property location. If None, the input file will be left it its original location once read.

Archive On Success Path

If Action on Success is set to Archive, this property sets the archive location where the input file should be archived once successfully read.

Cancel On No Files

If there are not files to process then cancel the entire flow (versus putting the flow in ERROR status.)

Enabled

Whether the component is enabled for the given flow.

Encoding

The encoding to use when reading or writing of text data.

Inbound Queue Capacity

The number of pending inbound messages this component can store as it processes other requests.

Input Model

The input model that describes input data sent to this component.

Log Input

Whether to the log input messages to this component to the log file.

Log Input

Whether to the log output messages from this component to the log file.

Output Model

The output model that describes data sent from this component.

Rows/Msg

The number of entity rows that will be packaged into a single message.

Run When

Indicates when the component will run. Most components have options of:

PER UNIT OF WORK

The component will run when it receives a control message.

PER MESSAGE

The component will run when it receives a content message.

Shared

Whether this component definition is shared across multiple flows.

4.6. Models

Data integrated with Metl can be structured or unstructured data. When dealing with Structured data, the structured data can be defined by modeling the data. Metl Models allow you to describe that structured data. Models can either be relational or hierarchical in nature. All structured data must be modeled in Metl. For example, a delimited file may exist that looks as follows:

Smith,John,male,10/19/2000
Smith,Sally,female,5/4/2004
Smith,Sarah,female,2/1/2015

This file represents people including their last name, first name, gender and date of birth. In order to use this file in a structured way, you must model the data contents of the file. I.E. define an entity named something like Person, and then define attributes for that person (lastName, firstName, gender, dateOfBirth).

4.6.1. Relational Models vs Hierarchical Models

A model in Metl can either be a relational model or a hierarchical model. The model type needed depends directly on the structure of the data to be modeled. Data coming from or going to a relational database, most commonly utilizes a relational model. A relational model has tables, columns, primary keys, foreign keys, etc. A relational model in Metl is largely like a relational model in any relational database platform. Hierarchical models are traditionally used when the data you are modeling doesn’t have explicit relationships defined in the data, but instead has relationships that are implicitly defined based on nesting of entities. Hierarchical models are commonly used for xml and json structures which are used in service based architectures.

4.6.2. Working with Models

To create a new model, click on the "Models" folder in the navigation pane, and then click "New" - "Model", and then either "Relational" or "Hierarchical" as shown below. For this example, we will create a Relational Model as the contents of the file will be sent to a relational database.

models new model

Once the model has been created, double click the model in the navigation pane in order to edit the model. From the content editor pane, there are buttons for adding and entities and attributes as well as positing those attributes within the model. Highlighting an entity and clicking the "Add Attribute" button will add an attribute to the given entity.

models edit model

Importing Relational Models from a Database

Similarly when dealing with data in a relational database, there is an inherent model defined in the relational database itself. I.E. tables and columns equate to entities and attributes. Metl relational models for relational databases can be automatically imported from the database structure itself, or they can be maintained manually. In order to import a model from a relational database, click the "Import…​" button. The following screen will be displayed which displays all database resources. Drilling into a database resource allows the user to multi-select one or more tables in order for those tables to be imported into the model.

models import model

4.6.3. Importing Relational Models from a Comma Separated File

If a relational model contains several entity and/or attributes you can use a comma separated file to import the model layout. In order to import a model from a file, click the "Import…​" button, then select "Relational CSV File". The following screen will be displayed where you can click the button to choose your file. Then click "Import".

models import file model

The CSV file must contain only 5 columns with no header row. The columns are as follows:

Entity Name

Required *first row of each Entity

This is the name of your Entity, it is only required on the first row for a given Entity. Subsequent rows (attributes) will assume the prior Entity if none is provided.

Attribute Name

Required

This is the name of the Attribute

Description

Optional

A description of the Attribute

Data Type

Optional

This is the data type of the attribute. Note: if none is provided or if the value is not a valid type, a type of "OTHER" will be used. These can then be manually edited later.

Is Primary Key

Optional

This defines whether the attribute should be treated as the PK of the Entity. Valid values are 'Y', 'Yes', 'X' or 1. Any other value entered will be ignored and the attribute will not be marked as a PK.

4.6.4. Example file

ENTITY_1,Attribute_1,Description of Attribute 1,INTEGER,X
,Attribute_2,Description of Attribute 2,VARCHAR(20),
,Attribute_3,,CHAR,
ENTITY_2,Attr1,,BIGINT,
ENTITY_2,Attr2,,STRING,
ENTITY_2,Attr3,Key Field,INTEGER,Y
ENTITY_2,Attr4,,DECIMAL(9,2),

4.6.5. Importing Relational Models from a source file containing a Column Header row

If a source file contains a delimited header row of column names you can use this to create a relational model with attributes of the column names from the file. In order to import a model from the header row in a file, click the "Import…​" button, then select "Source File Header Row". The following screen will be displayed where you will enter the Entity Name, Delimiter (single character delimiter ie: , \ | \t …​) and click the button to choose your file. Then click "Import".

models import header model

The source file must have a header row that contains a delimited set of column names to use as attribute names. The attributes will be created as VARCHAR data types and none will be selected as PK and no descriptions will be added.

[id="resources",reftext="Resources"] === Resources

Resources represent connections to physical end-points where data is read from or written to. A resource is configurable per environment (agent). This allows a flow to be tested against a resource configured for testing and then deployed to an agent with resources configured for production.

The following resource types are available in Metl:

[options="header"]

|Rsource Type|Description 2+|Database |Database|A JDBC connection to a JDBC compliant relational database 2+|Directory |Local File System|A connection to a local file system |FTP|An FTP connection to a file system |SFTP|An SFTP connection to a file system |SMB|An SMB connetion to a server |JMS|A JMS connection to a specified queue and topic 2+|HTTP(S) |Web Resources|An HTTP(S) connection to REST or SOAP based services 2+|Mail Session |Mail Session|An SMTP connection to a Mail server 2+|Subscribe |JMS|A JMS connection to a specified queue or topic that can be subscribed to

Resources are defined on a project by project basis. In order to create a new resource, Click on the "Resources" folder in the Navigation pane, click the "New" menu item, and then select the type of resource to be created.

resources new

If resources will be shared across multiple projects, a project can be defined that only contains resources, and then that project can be used as a Project Dependency for other projects that will leverage the shared resources.

When a flow is deployed, its associated resources are automatically deployed with it. A flow, and thus its associated resources, can be deployed to one or more Metl agents. Each deployment of that resource on a given agent can have different settings. For example, companies typically have several environments for a given system (i.e. development, test, user acceptance test, production). A flow and its resources can be deployed to the development agent/environment with settings specific to the development environment (i.e. server names, ids, passwords, etc.) When that same flow is deployed to the test agent/environment the resources on the test environment can be changed for the specifics of that environment. The flow itself stays the same regardless of the environment in which it is running. This allows seamless migrations from environment to environment.

To edit the settings of a resource, double click the resource in the navigation pane, and the settings details will be displayed in the content pane.

==== Database

The following settings are available for the Database resource

[options="header" cols="<25%,<75%"]

|Setting|Description |Driver|The fully qualified Java class name of the JDBC driver to be used |Url|The connection URL to be passed to the JDBC driver to establish a connection |User|The connection username to be passed to the JDBC driver to establish a connection |Password|The connection password to be passed to our JDBC driver to establish a connection |Validation Query|The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row. If not specified, connections will be validated by calling the isValid() method |Initial Size|The initial number of connections that are created when the pool is started |Max Active|The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit |Max Idle|The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit |Min Idle|The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none |Wait Time (ms)|The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely |Evict Time (ms)|The minimum amount of time an object may sit idle in the pool before it is eligible for eviction by the idle object evictor (if any) |Test on Borrow|The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another |Test on Return|The indication of whether objects will be validated before being returned to the pool |Test while Idle|The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool |Init Sql|An SQL statement that will be sent to the jdbc driver upon each connection to the database. Can be used for custom initialization |Fetch Size|The number of rows to fetch at a time for a sql select statement Query timeout (seconds)|The allowable time a query can run without completing before being cancelled Connection Properties|The connection properties that will be sent to our JDBC driver when establishing new connections. Format of the string must be [propertyName=property;]

===== Adding a JDBC Driver .jar file

Metl comes pre-packaged with the following jdbc drivers:

* SQL Server jTDS jdbc driver * H2 jdbc driver * Postgres jdbc driver

For other databases such as Oracle, jdbc drivers can be downloaded from the respective vendor and added to the classpath of Metl. Adding the jdbc driver to the classpath depends on whether Metl is being run as a service.

If Metl is being run as a service:

* Download the jdbc driver and place it on the local file system * Edit the Metl metl_service.conf file and change the wrapper.java.classpath line in the file.

---- wrapper.java.classpath.1=/path/to/metl/metl.war:/path/to/oracle/jdbc/driver/ojdbc6.jar ----

TIP: The separator for the classpath shown above depends on the operating system. For OSX and Linux, use ":", for Windows use ";"

If Metl is NOT being run as a service, set the classpath to include the additional jdbc drivers as well as the metl.war and launch metl by specifying the class name within the .war file as shown below.

---- java -cp /path/to/oracle/jdbc/driver/ojdbc6.jar;/path/to/metl/metl.war org.jumpmind.metl.Metl ----

===== Example database drivers and urls

===== In Memory H2 Database

[options="header" cols="<25%,<75%"]

|parameter|value |Driver|org.h2.Driver |Url|jdbc:h2:mem:mydatabasename

===== File based H2 Database (linux, osx)

[options="header" cols="<25%,<75%"]

|parameter|value |Driver|org.h2.Driver |Url|jdbc:h2:~/foldername/databasename

===== File based H2 Database (windows)

[options="header" cols="<25%,<75%"]

|parameter|value |Driver|org.h2.Driver |Url|jdbc:h2:C:/foldername/databasename

===== SQL Server Using JTDS driver

[options="header" cols="<25%,<75%"]

|parameter|value |Driver|net.sourceforge.jtds.jdbc.Driver |Url|jdbc:jtds:sqlserver:/servername.domainname:1433;databaseName=databasename;

===== Oracle

[options="header" cols="<25%,<75%"]

|parameter|value |Driver|oracle.jdbc.OracleDriver |Url|jdbc:oracle:thin:@//servername.domainname:1521/servicename

===== Postgres

[options="header" cols="<25%,<75%"]

|parameter|value |Driver|org.postgresql.Driver |Url|jdbc:postgresql://servername:5432/databasename

===== MySql

==== Local File System

The following settings are available for the Local File Resource

[options="header" cols="<25%,<75%"]

|Setting|Description |Path|The base path that should be used when accessing files from this resource

==== FTP

The following settings are available for the FTP resource

[options="header" cols="<25%,<75%"]

|Setting|Description |Server|The name of the FTP server including domain name. i.e. servername.domainname |Port|The TCP port over which to communicate. Default FTP port is 21 |User|The user name that should be used to connect to the FTP resource |Password|The password that should be used to connect to the FTP resource |Base Path|The base path that should be used once the connection has been made (i.e. the connection will default to this base directory on the server for subsequent requests) |Connection Timeout|The amount of time to wait for connection before canceling the connection

==== SFTP

[options="header" cols="<25%,<75%"]

|Setting|Description |Server|The name of the SFTP server including domain name. i.e. servername.domainname |Port|The TCP port over which to communicate. Default SFTP port is 22 |User|The user name that should be used to connect to the SFTP resource |Password|The password that should be used to connect to the SFTP resource (if password is used to authenticate) |Key File Location|The full path and file name of a key file that should be used for authentication (if public key authentication is used to authenticate) |Base Path|The base path that should be used once the connection has been made (i.e. the connection will default to this base directory on the server for subsequent requests) |Connection Timeout|The amount of time to wait for connection before canceling the connection

==== Web Resources

The following settings are available for Web Resources

[options="header" cols="<25%,<75%"]

|Setting|Description |Url|The URL of the web resource to be utilized |Http Method|The Http method t be sent for requests sent to this resource, either GET, PUT or POST |Http Timeout|The max time to wait for a response from the request before canceling the request |Content Type|The content type for the body for requests sent to this resource |Security|Either None, Basic Auth, OAuth 1.0 or Token |User|If Security is set to Basic Auth, the user name to be used to authenticate requests sent to this resource |Password|If Security is set to Basic Auth, the password to be used to authenticate requests sent to this resource |Token|If Security is set to Token, this is the token provided by the web service provider to be used to authenticate requests sent to this resource

[id="release",reftext="Release"] == Release [id="deploy",reftext="Deploy"] == Deploy

Once flows have been designed and configured, they can be deployed to a Metl agent to be run. When a flow is deployed to an agent, its associated resources are also deployed to that agent and can be configured differently for each agent deployment. In a typical scenario, a company might have application environments for development, system test, user acceptance test and production. Similarly with Metl, you can have a development, system test, user acceptance test and production Metl agents. After a flow has been designed and configured, it can be deployed to the development agent for dev testing. The resources that are deployed on the Metl development agent can point to the external systems development resources. When the flow is ready for system testing, it can be deployed to a Metl system test agent, and the associated resources on that agent can point to the external systems system test resources, etc.

Each running instance of the Metl application can have one or more Metl agents running underneath it. As an example, development, test and production agents could all be run under a single running instance of the Metl application. More commonly each agent is deployed on a different physical server or virtual machine.

When a flow is run from the design screen, Metl uses a pre-configured agent on the current running instance of Metl called the design-time agent. This agent is like any other Metl agent except that it is created automatically.

Similar to the design screen, the deploy screen is split into different panels. The navigation panel show below, allows viewing of the Metl agents that are configured and running for the current instance of the Metl application.

navigation panel

Agents are organized within folders. To create a new folder, click "New" - "Folder", and type the new name of the folder.

new folder

Similarly, to create a new Metl Agnet, click on the folder under which you would like the agent organized, click "New" - "Agent", and type the name of the new agent.

new folder

Double clicking on an agent in the navigation panel, or selecting an agent and clicking "Edit" - "Open" will open the agent details in the content editor panel as shown below.

agent details

The Start Mode for each agent can be "AUTO" or "MANUAL". When in "AUTO" mode, all agents defined in this instance of the Metl application will be started automatically when the Metl software is run. The Hostname lists the computer’s hostname on which the agent is being run.

Agent specific parameters can be set on the Agent screen. Clicking the "Parameters" button displays a dialog box where these parameters can be maintained. These parameters are available to flow components and can be used when things (in addition to resources) need to be specified on an environment by enironment basis.

agent parameters

The main list on the content editor panel of the deploy screen displays the flows and resources that have been deployed to a given agent.

agent deploys

[options="header" cols="<25%,<75%"]

|Column Name|Description |Project Name|The name of the project to which the deployed item belongs |Deployment|The name of the item that has been deployed to this agent. This can be a resource name or a flow name |Type|The type of the item that has been deployed. Either "Resource" or "Flow" |Status|The status of the deployed item. Either "DISABLED" or "ENABLED". If "ENABLED" the flow will can be run on a scheduled or manual basis from the agent. If "DISABLED" the job must be enabled before it can be run. This column is only applicable for a flow. |Log Level|The log level for the deployed item. Either "DEBUG", "INFO", "WARN", or "ERROR". Determines the log level that will be output for the flow when the flow is run on the agent. This column is only appicable for a flow. |Start Type|Determines how the flow will be started or run on the agent. Either "MANUAL", "ON_DEPLOY", or "SCHEDULED_CRON". If "MANUAL" the flow will not be invoked automatically by the system, but can be run manually by a user from the deploy screen. If "ON_DEPLOY", the flow will be run immediately after the flow has been deployed and enabled. If "SCHEDULED_CRON", the flow will be run on a scheduled cron basis that can be maintained by double clicking the flow in the list. |Start Expression|If Start Type is set to "SCHEDULED_CRON", this is the cron expression that will determine the run time of the job

[discrete] === Deploying a flow

To deploy a flow to an agent, click on the "Add Deployment" button and the Add deployment screen will be displayed as show below:

agent deploy flow

All projects and flows will be displayed. Selecting a flow, and clicking the "select" button will deploy the flow to the Metl agent. When a flow is first deployed to an agent, its status will be "Disabled." To enable the flow for the agent, select the flow, and click the "Enable" button. The flow status will be changed to "REQUEST_DEPLOY" and once deployed will be changed to "DEPLOYED."

[discrete] === Running a flow

Once a flow has been deployed to an agent and enabled, the flow can be run from the deploy screen. Select the flow to be run, and click the "RUN" button. After the flow has run, the flow’s run statistics will be displayed as follows.

[options="header" cols="<25%,<75%"]

|Column Name|Description |Component Name|The name of the component in the flow |Thread|The number of threads that were used when running the component |Status|The completion status of the component. Either "DONE", "ERROR", or "CANCEL". If "DONE", the component completed all of its work successfully. If "ERROR", the component finished in error. If "CANCEL" the component was cancelled before it could complete its work |Msgs Rcvd|The number of messages this component received during the run of the flow. |Msgs Sent|The number of messages this component sent during the run of the flow |Entities Prcd|The number of entities processed (if the component takes a model based inbound message) |Start|The start run time of the component |End|The end run time of the component

Selecting the component in the top pane will result in run information for that component being display in the bottom pane. Run information for the component can be filtered by typing a filter expression in the Filter box.

[id="enterprise-scheduler",reftext="Running a flow from an enterprise scheduler"] [discrete] === Running a flow from an enterprise scheduler

Metl has a REST API that can be used to call a deployed flow from an external enterprise scheduler. Information about the Rest API can be found here [rest-api]. The Rest service allows an HTTP GET request to run a flow and get a response back that provides the status of the flow run.

One way to initiate the REST API is to create a .bat file using cURL as shown in the example below.

---- curl -H "Accept: application/xml" http\://[SERVER]\:8080/metl/api/agents/[AGENT]/deployments/[FLOW_NAME]/invoke?paramName=paramValue

grep '<status>DONE</status>' ---- [id="manage",reftext="Manage"] == Manage The Manage screen allows viewing run information for flows. The manage screen is shown below:

manage home

The Manage screen is split into two panels, the navigation panel on the left hand side of the screen and the content panel on the right hand side of the screen. The navigation panel allows selection of the flows to be viewed. The following options are available.

[options="header"]

|Option|Description |Currently Running|Displays all flows that are currently executing on any Metl run-time agent in this Metl instance |In Error|Displays all flows that are currently in an error status on any Metl run-time agent in this Metl instance |Agents|Allows selection of flows to be viewed based on the run-time agent to which they are deployed. When a specific run-time agent is selected in the navigation panel, all flows that have been run on that agent will be displayed in the content panel. |Flows|Allows selection of flows to be viewed based on flow name. When a flow name is selected, every run of the given flow will be listed in the navigation panel.

Double clicking a row/run in the content panel will display the run statistics for that run of the flow. There are two modes for displaying run statistics for a flow, table and graphical. If the Show Diagram checkbox is checked, a graphical representation of statistics for the flow will be displayed. If it is not checked, a tabular representation of the stats will be displayed. Both representations are shown below.

Tabular

agent run

Graphical

agent run graphical

In the tabular representation, the following data is available:

[options="header" cols="<25%,<75%"]

|Column Name|Description |Component Name|The name of the component in the flow |Thread|The number of threads that were used when running the component |Status|The completion status of the component. Either "DONE", "ERROR", or "CANCEL". If "DONE", the component completed all of its work successfully. If "ERROR", the component finished in error. If "CANCEL" the component was cancelled before it could complete its work |Msgs Rcvd|The number of messages this component received during the run of the flow |Payload Rcvd|The number of content messages this component received during the run of the flow |Msgs Sent|The number of messages this component sent during the run of the flow |Payload Sent|The number of content messages this component sent during the run of the flow |Entities Prcd|The number of entities processed (if the component takes a model based inbound message) |Start|The start run time of the component |End|The end run time of the component

Selecting the component in the top pane will result in run information for that component being display in the bottom pane. Run information for the component can be filtered by typing a filter expression in the Filter box.

== Explore

The explore screen is for users to explore deployed resources. Metl currently supports exploring Sql Datasources via a Sql Explorer and Directory based resources via a Directory Explorer.

== Admin

The administration screens allow setup of admin data including:

* Users - Managing users * Groups - Managing User groups * REST - Displaying information about the REST API * General Settings - Managing purge, auto backup and password settings * Plugin Repositories - Managing maven repositories from which plugins are downloaded * Mail Server - Configuring the SMTP mail server * Notifications - Configuring notifications * Active Users - Displaying active users * Audit Events - Displaying system events such as logins, restarts, config imports, etc. * Logging - Displaying Metl log information * About - Displaying information about the running Metl system

=== Users

Selecting "Users" in the Admin navigation panel displays a list of Metl users in the content panel as shown below.

admin users

Clicking on the "New" button allows creation of a new user, while clicking on the "Edit" button allows editing of an existing user. In either case the edit user dialog will be display as follows:

admin edit users

[options="header" cols="<25%,<75%"]

|Field|Description |Login ID|The ID the user will use to log into the Metl user interface |Full Name|The full name for the user. For documentation purposes only |Password|The password the user will use to log into the Metl user interface |Available Groups|The available groups configured in the "Groups" section that are available to be assigned to the user |Selected Groups|The groups to which this user currently belongs

=== Groups

Selecting "Groups" in the Admin navigation panel displays a list of Metl groups in the content panel as shown below.

admin groups

Clicking on the "New" button allows creation of a new group, while clicking on the "Edit" button allows editing of an existing group. In either case the edit user dialog will be display as follows:

admin edit group

[options="header" cols="<25%,<75%"]

|Field|Description |Group Name|The name of the group that will be availble to assign to users |Available Privileges|The available privileges in the system. The available privileges are "DESIGN", "DEPLOY", "MANAGE", "EXPLORE", and "ADMIN". Each of these privileges allows the user to access the the corresponding high level screens in Metl and perform the actions on those screens. For example, design privileges allow the user to design new flows. Deploy allws users to deploy existing flows to Metl agents. "Manage" allows users to look at run time data for flows running on the given agent, etc. |Selected Privileges|The selected privileges the user group will have. The default user group has access to all privileges.

[id="rest-api",reftext="Rest API"] === REST

Selecting "REST" in the Admin navigation panel displays a list of Metl Rest Services in the content panel as shown below.

admin rest

The current REST service that is available for Metl is a service to allow flows deployed on an agent to be run by invoking the REST service. This is used frequently when scheduling a Metl flow with an existing enterprise scheduler. Documentation for the service includes:

* HTTP Method - GET * URL - /api/agents/{agentName}/deployments/{deploymentName}/invoke. {agentName} should be replaced with the agent that hosts the flow to be run, and {deploymentName} should be replaced with the flow name to be run. * Parameters - agentName and deploymentName from above * Example Response - The response that is sent by the REST service after invoked.

See [enterprise-scheduler] for an example.

=== Mail Server

Metl allows notifications to be sent to an email address or distribution list for certain flow run statuses. See [notifications] for details. In order for notifications to be sent, the mail server must be configured. Selecting "Mail Server" in the Admin navigation panel allows the mail server to be configured in the content panel as shown below.

admin mail server

[options="header" cols="<25%,<75%"]

|Field|Description |Host Name|The server name on which the mail server resides |Transport|The transport over which to connect to the mail server. Either "SMTP" or "SMTPS". SMTPS is secure. |Port|The port on which to connect to the mail server |From Address|The email address notification emails should be sent FROM |Use TLS|Whether to use transport layer security protocol over SMTP to provide certificate based authentication. |Use Authentication|Whether the mail server requires authentication |Username|The username to be used to connect to the mail server if authentication is required |Password|The password to be used to connect to the mail server if authentication is required

[id="notifications",reftext="Notifications"] === Notifications

Metl allows notifications to be sent to an email address or distribution list for certain flow run statuses. In order for notifications to be sent, the notifications must be configured. Selecting "Notifications" in the Admin navigation panel shows a list of configured notifications in the content panel as shown below.

admin notifications

Clicking on the "New" button allows creation of a new notification, while clicking on the "Edit" button allows editing of an existing notification. In either case the edit notification dialog will be display as follows:

admin edit notification

[options="header" cols="<25%,<75%"]

|Field|Description |Level|The level at which this notification applies. Either "GLOBAL", "AGENT", or "DEPLOYMENT". If "GLOBAL" the notification will be sent for all flows and all agents in this instance of Metl. If "AGENT", the notification will be sent for the agent specified by the "Linked To" field, and if "DEPLOYMENT", the notification will be sent for the flow and specified agent in the "Linked To" field. |Linked To|If "AGENT" or "DEPLOYMENT" is specified for the "Level" field, this field specifies for which agent or deployment (agent and flow) the notification should fire |Event|The event for which the flow should fire. Either "FLOW_START", "FLOW_END" or "FLOW_ERROR". This field is used in conjunction with the "Level" and "Linked To" fields to determine when to fire events. "FLOW_START" indicates a notification should be fired when the flow begins, "FLOW_END" when the flow finishes (either successfully or on error), and "FLOW_ERROR" when the flow finishes with an error condition. |Name|The name of the notification |Recipients|The recipient list. One or more email addresses may be entered |Subject|The subject message that should be displayed for the email notification |Message|The message body for the email notification |Enabled|Whether this notification is enabled or not. If not enabled, the notification will not be sent

[discrete] ==== Notification variables that can be used in Subject or Message

There are several variables that can be used in the notification subject and message. These are specifically helpful when the notification Level is at the Agent or Global level as details can be added that specify which flow fired the notification.

* $(_flowName) - The name of the flow that fired the notification message * $(_agentName) - The name of the agent that fired the notification message * $(_date) - The date the notification message was fired * $(_time) - The time the notification message was fired

=== Logging

Selecting "Logging" in the Admin navigation panel shows Metl’s log file as shown below.

admin logging

The log file can be filtered or downloaded by using the appropriate fields on the logging screen.

Unresolved directive in user-guide.asciidoc - include::examples/examples.asciidoc[] === Creating RESTful Services Metl can be used to create and host RESTful services. Creating a RESTful service in Metl is as simple as creating a flow and deploying it to one of the configured agents. Metl comes with several sample flows that show the creation of RESTful services. The samples can be reviewed and run from the Samples project.

The core of Metl’s service capabilities come from the Http Request and Http Response components.

Following are instructions on how to create and host a REST service that lists all persons from a relational database. Metl REST services are created by configuring a Metl flow to accept the Http request, using other components to complete the logic of the flow, and then using the Http response component to return the results to the caller.

The first step in creating the sample REST service is to create the resource that will be used to gather the person data from the database. For this sample, an in memory H2 database will be used and is configured as follows:

sample database resource

Next, a model must be created to represent a person. In this example, the structure of the data of a person will be the same in the relational database source and the response of the REST service, so the same model will be used for both. Different models for each could be used and mapped within the flow, but for this sample, we’ll keep things simple. The person model looks as follows:

person model

Now that our source resource and model are defined, the flow can be created that handles the serivce logic. The completed flow looks as follows.

flow get all persons

First, let’s go through the non-service aspects of the flow. The SQL Executor labeled "Setup Person Database" is used in the sample flow to create the person table and populate it with data. The Gate component labeled "Ensure DB Setup Completes" is used to control the order of execution of the flow when an HTTP response triggers the flow to begin. It simply ensures SQL Executor "Setup Person Database" completes before reading the data from the database itself. In most scenarios, these two steps would not be necessary as the database where the data resides most likely already exists. These two steps are included in the sample simply to make it run stand-alone without the need for predefined database resources.

Next, let’s look at the Http Request component and its associated properties.

http request properties

Each property above is described in the Http Request component definition. For this example, the "HTTP Method" of type "GET" and the "Path" of "/person/getAll" tells the flow to listen for HTTP GET requests to a URL of http://myserver:port/metl/api/ws/person/getAll, where "myserver" and "port" are the server and port on which Metl is running. If Metl is running locally on its standard port of 42000, HTTP GET requests to http://localhost:42000/metl/api/ws/person/getAll would be handled by the flow.

Once the request hits the flow, the "Setup Person" SQL Executor would run and setup the database. The "Ensure DB Setup Completes" Gate would ensure the SQL Executor is finished setting up the database before sending the HTTP request to the RDBMS Reader labeled "Read From Database". The RDBMS Reader component is set up as follows:

rdbms reader person getall

Note the output model of "Person" and the Sql of select * from person. The RDBMS Reader will simply read all rows from the person table and return the results based on the Person model. Once the data has been read from the database, it will be forwarded to the Serializer component configured as follows:

serializer properties

Now that the response has been formatted it send to the Http Response configured as follows:

http response properties

Each property above is described in the Http Response component definition.

The following shows the results of a GET request to the service with an Accept header of application/xml. ---- <ArrayList> <item> <name>PERSON</name> <data> <GENDER>M</GENDER> <ID>1</ID> <LAST_NAME>Arbuckle</LAST_NAME> <FIRST_NAME>Garfield</FIRST_NAME> </data> </item> <item> <name>PERSON</name> <data> <GENDER>M</GENDER> <ID>2</ID> <LAST_NAME>Arbuckle</LAST_NAME> <FIRST_NAME>Odie</FIRST_NAME> </data> </item> <item> …​ </ArrayList> ---- With an Accept header of application/json, the result would look as follows: ---- [ { "name": "PERSON", "data": { "GENDER": "M", "ID": "1", "LAST_NAME": "Arbuckle", "FIRST_NAME": "Garfield" } }, { "name": "PERSON", "data": { "GENDER": "M", "ID": "2", "LAST_NAME": "Arbuckle", "FIRST_NAME": "Odie" } } …​ ] ----

If different XML or JSON formatting is desired, the flow can be configured to format entity based data into XML or JSON using the XML Formatter or custom scripts and the resulting text messages from the formatters can be passed to the HTTP Response component. The HTTP Response component will return the pre-formatted text (XML, JSON, or other) accordingly.

TIP: If the flow formats its own output, the HTTP Response component should be configured to communicate the content-type to the caller. This can be done by setting the HTTP Response "Content Type" accordingly (i.e. application/xml, etc.)

In order for a REST service flow to be used (run), it must be deployed to an existing Metl agent. See the Deploy section for details on creating agents and deploying flows.

To test the flow in the design mode, use the Run button and the flow will be auto-deployed to the design agent and a user interface will be presented for testing the web services.

test webservices