ss_blog_claim=4037e1ab37562895784b0e2f995a5eec ss_blog_claim=4037e1ab37562895784b0e2f995a5eec

4.22.2010

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.

9 comments:

  1. 美麗的事物是永恆的快樂,它的可愛日有增加,不會消逝而去........................................

    ReplyDelete
  2. 唯有學習不已的老師,才能認真的教,唯有燃燒自己,才能點亮他人的燈......................................................

    ReplyDelete
  3. Well done!
    I have the same task as you have done in Job 3. Can you tell me, please, how exactly the data from Excel file is being loaded to the DB?

    ReplyDelete
  4. hi diana. thanks for reading my blog. were you able to resolve your issue here? i simply created a metadata schema for my excel file. expand the metadata on the repository tree,right click on the file excel, and choose create file excel.hope that helps:)

    ReplyDelete
  5. Thank you for the answer. I've created metadata schema for my excel file, I can see it in the Repository Tree. But I don't understand how to make the information from my excel file be loaded to the store (I use MySQL).

    ReplyDelete
  6. Thank you! I've already resolved my problem. Your blog really helps. But you can also write some details about the implementation of components in JasperETL.
    It was very useful to me. Hope there'll be something new soon =)
    Thanx.

    ReplyDelete
  7. Hi, I have also been assigned something similar to job 4. like i have to read some data from mysql database and convert it into a exec sheet.
    so i want to know in some detail how would you connect to the database via ETl , and how to get the data?/

    ReplyDelete
  8. Hi

    I have to extract 28 tables from MS SQL Server 2000 using jaspersoft etl, transfrom them and load them onto a Mysql database. The question is:

    Does this mean creating 28 jobs? This I know how to do.

    Or can I extract them in one job? If so how?

    ReplyDelete