This post will help you create a simple step by step ETL process flow within Adeptia.

To create a simple ETL process flow, start by extracting data from various sources like databases or files. Transform the data by cleaning, filtering, and aggregating it as needed. Then, load the transformed data into a target destination such as a data warehouse. This process ensures data quality, consistency, and accessibility for analysis

If you have just started using Adeptia we would recommend that you follow the evaluation guide that has basic examples with detailed steps to proceed. Refer to the evaluation guide and developer guide links below for a more detailed explanation:

• https://docs.adeptia.com/display/AS/Evaluation+Guide
• https://docs.adeptia.com/display/AS/Developer+Guide

File Source Activity: The File Source provides the ability to specify any file that is located on the local hard disk, as a source.

Polling Service Activity: Polling Services allow the process flow to ‘wait’ and ‘listen’ to a defined location, at which specific file is to arrive or is to be modified before the execution of the next activity. The Polling Services perform the ‘listen’ action at a frequency specified while creating the Polling activity.

File Trigger Activity: Trigger Events are used to schedule and trigger a process flow. Trigger Events enable you to specify when and how frequently the process flow should be executed on a recurring basis. The File Event enables you to specify when and how frequently a process flow should be executed based on either creation of a new file, or existence of a file(s) in a pre-defined location or upon its modification.

Here are the simple ETL Process Flow steps for transferring a file from any source to target after transformation:

Step 1: If your file is on the local machine, create a new file source activity under Configure > Services > Source > File. Configure the full path of the source file name in the File Path field and the source file name in the File Name field. Save it. For more help click on Creating Source Activity and then click on Creating File Source Activity in the Developer guide.

Step 2: Create a new schema activity under Configure > Services > Schema > for the source file. A Schema is the structure of a file format and it specifies information about different data fields and record types that a message or a data file may contain. You can create different types of Schemas according to the file structure. For more help click on Creating Schema Activity in the Developer guide.

Step 3: Create a new schema activity under Configure > Services > Schema > for the target file. If the target file structure is same as source file structure then you don’t need to create a new schema.

Step 4: Create a new Data Mapping activity under Configure > Services > Data Transform > Data Mapping. Data Mapping is used to map source schema elements to target schema elements. You can map one source schema element to a target schema element directly using the drag and drop approach. The process of mapping elements comprises of various steps:

For more help click on Transforming Data, click on Using Data Mapper and then click on Map Source and Target Elements in the Developer guide.

Step 5: Create a new file target activity under Configure > Services > Target > File. Specify the name and path of the target file to be created. For more help click on Creating Target Activity and then click on Creating File Target Activity in the Developer guide.

As you have created all the activities now you need to create a process flow. The process flow is a set of activities arranged in a sequence to perform a specific task by combining various activities i.e. Source, Target, Schema or Transformer etc. You can refer to the “Working With Process Flow” link in Developer guide. Now select all the above-created activities in the process designer window and join each activity with sequence flow. Please refer the Creating Process Flow, Designing Process Flow using BPMN Graphical Elements, and Attaching Adeptia Server activities with the BPMN elements link in Developer guide.

Your process flow should be like in this way:

Start Event > File Source (Step1) > Source Schema (Step 2) > Data Mapping (Step 4) > Target Schema (Step 3) > File Target (Step 5) > End Event

Note: You must change the “transformer” property of the target schema (Step3) with “XMLStream2stream” in the process flow by double click on it. Please refer the Changing Transformer Type in the developer guide.

Step 6: Go to Design > Process Flow and select the above process flow and click on execute.

Schedule a demo with Adeptia’s integration solution and see how it enables companies to deliver the value promised to customers and accelerate revenue.