Data Aggregation via Email and Database

Version: 17.07

Supported Since: 17.01

Use case description

Zenythz Corp. is a manufacturer of heavy machinery, runs several factories in different parts of mother country. As tooling for manufacturing heavy machinery is fairly expensive, Zenythz wishes to improve their tooling maintenance process by reporting maintenance details of each machine in a per-factory basis, daily, to the headquarters, which will maintain an aggregated record for each machine for further analysis.

Proposed Solution

After end of each day’s work shift, each factory sends a CSV file to the headquarters via email (as an attachment), containing maintenance details of each machine in the following format:

id,hours,restarts,minor_repairs,major_repairs,extra_cost
1,10,0,0,0,0
2,8,1,2,0,100

In order to distinguish the maintenance emails from other emails sent to headquarters, the email includes a special header X-Zenythz-Factory containing the ID of the respective factory.

An ESB instance running at headquarters is assigned with the task of processing the sent attachments and aggregating daily maintenance details of each machine into a central MySQL database.

UltraStudio Configuration

UltraESB-X Configuration

Implementation of the Solution

Prerequisites

  • A MySQL installation, with access to a user account with new database creation privileges

  • An email account supporting IMAP (or POP3, in which case the settings in the sample would have to be modified accordingly)

In order to implement above use case you must first select following dependencies when you are creating a new Ultra project.

  • File Connector, and Mail Connector from the connector list

  • Attachment Processing, Content Extraction, Database Operations, EIP Routing, Logging, Throttling, Transformation, and XML Operations from the processor list

If you have already created a project, you can add above dependencies via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the Connectors list and Processors list, select above dependencies.
Implementation

We can break this workflow into 2 integration flows:

  • a Report Receiver for fetching relevant emails and saving attached reports to the filesystem

  • a Report Processor for processing saved reports and updating the database

Report Receiver

Add a new integration flow report-receiver for the first stage of processing, and then add required components by going through following steps in order.

  1. Add a Mail Ingress Connector with the configurations shown below (assuming a Gmail email address; replace account details as appropriate)

  2. Next add a Logger to generate a log before attempting to save the attachment.

  3. As the file content does not contain any reference to the originating factory, we shall save it by the name of the factory itself (i.e. the value of X-Zenythz-Factory header), for correlation during further processing. For this, add an Attachment Saver with the configuration shown below.

  4. Finally, complete the flow with a Successful Flow End element.

The completed integration flow should look like below.

email db data aggregation b complete flow

Configuration for each element is as below. The numbering corresponds to the numbers shown in above diagram.

Design View

Text View

.

1. Mail Ingress Connector (Basic Tab)

email db data aggregation b component 1

Other Settings Map

email db data aggregation b component 1 1

1. Mail Ingress Connector (Advanced Tab, Search Headers Map)

email db data aggregation b component 1 2

1. Mail Ingress Connector (Scheduling Tab)

email db data aggregation b component 1 3

2. Logger

email db data aggregation b component 2

3. Attachment Saver