What JasperETL can do

Here's what I've been doing for the past two days. I was requested to learn an ETL (Extract-Transform-Load) tool. Luckily Jaspersoft's ETL is as easy as ABC. So far.. so good.

For my sample scenarios, I used SqlServer database, MySQL database, and excel file as my data sources. But note that JasperETL has an extensive list of supported data sources. On the database side alone there is AS400, Access, DB Generics, DB JDBC, FireBird, Informix, Ingres, Interbase LDAP, MaxDB, Oracle, PostgreSQL, SQLite,Sybase,Teradata to name a few.

Below is a detailed decription of some jobs I did:

1. Job 1
- This job transfers all employee data from the SqlServer database to the employee table on the MySQL database. If the transaction succeeds, trigger 1 will run and send email to some recipients with the message "All data successfully transferred". If the transaction fails, trigger 2 will run and send email to some recipients with the message "An error occurred on the job process".

Components Used :
- tSendMail
- tMSSqlInput
- tMysqlOutput
- tMap
- tLogRow (for testing purposes)

This is how the entire workspace looks like. The diagram at the center show you the job design of Job1

2. Job 2
- This job transfers all products of a specific brand name. The data will be captured from the product table on SQLServer and loaded on both the product table on MySQL and the excel file.

Components Used :
- tFilterRow
- tMSSqlInput
- tMysqlOutput
- tFileOutputExcel
- tMap
- tLogRow (for testing purposes)

This is the job design for Job 2

3. Job 3
- This job fetches data from an excel file and loads it on the store table from MySQL database. Note that a new field was added on the store table (store_address) to hold the concatenated value of store_street, store_city, and store_state from the excel file. There is also one field on the excel file that has a different field name on the store table of the MySQL database.

Components Used :
- ExcelMetadata
- tMysqlOutput
- tMap
- tLogRow (for testing purposes)

This is the job design for Job 3

4. Job 4
- This job fetches data from the store database and loads it on an excel file (excel file used for Job 3)

Components Used :
- tMSSqlInput
- tFileOutputExcel
- tMap
- tLogRow (for testing purposes)

This is the job design for Job 4

5. Job 5
- This job joins tables employee and salary from the SQLServer database, get's the joined data and loads it on another table employee_salary on the MySQL database. It also throws all rejected inner join data to an xml file which, when exists, will email a notification that there are rejected data on the join

Components Used :
- tMSSqlInput
- tMysqlOutput
- tFileOutputXML
- tFileExist
- tSendMail
- tMap
- tLogRow (for testing purposes)

This is the tmap design for Job 5

This is how the job design looks like while it's running and both Statistic and Traces are ticked on the Run tab.