Matter faced during integration project

Wednesday, September 2, 2009

ETL Process Through Email Attachment

Last June, one of my post was "A BPEL process that saves e-mail attachment".
The aim was to try to insert data within the ERP via an e-mail attachment.
For example, to load the "daily mineral figures" of South-Africa, the South-Africa manager was sending a mail to etl.load@mycompany.com with an attached file (an xml file). This attachment would be processed and its containing data inserted into the ERP.
Security was insured by a x.509 certificate signature.

This process is very convenient as sending a mail is an easy process and can be done with a limited access to the web.

We tried to enable this process with "Oracle BPEL Process Manager", but the following issues arise:
1. Nearly impossible to authenticate the certificate signature
2. Difficult to retrieve the attachment with the correct encoding
3. Difficult to retrieve more than one file
4. Impossible to move mail in other folder after processing
5. To deal with all the operations, lots of Java embedded code had to be written.
6. Mail with attachment bigger than 1MB could not be processed
This is not exhaustive, but the feature for BPEL PM that enable to start a process by receiving a mail, is not meant to deal with complex mail content.

So we decided to change our option and use the Java Mail API.
We used Quartz to schedule our process and the process was written in Java.
The operations are:
1. Call the IMAP server every 5 minutes to check mail box for incoming mail
2. Retrieve the attachments and copy them to the correct location
3. Send e-mail if an error occurred or if the attachment was correctly copied
4. Move the mail from "Inbox" to "Processed", "Error" or "Discard" folder

The process is hosted within our J2EE server and is a reliable and robust solution.

Post a message if you want the source code of this process.

1 comment: