Wednesday, October 21, 2015

WSO2 ESB: Transferring data from files to a database

In some integration scenarios you may want to use ESB to poll a directory for files and store the content to a database.

Following steps can be used to read CSV files from a directory, extract their data and then store them in a database.

We use a VFS proxy to poll the directory and look for new files. The parameters transport.vfs.FileURI and transport.vfs.FileNamePatter are used to indicate where/what types of files to look for (any regular expression can be used here).
Once a file is read, we need to convert it to a format that is supported by the DB Report mediator of the ESB. In this case we convert the csv data to xml using the Smooks mediator.

Once the conversion is done, we can easily use the converted xml data inside the DB Report mediator to enter them to a DB with XPath expressions.

In addition, we can specify some actions such as where the file to be placed (or deleted) after storing in the DB (transport.vfs.ActionAfterProcess),  what actions to be taken if a failure occurs (transport.vfs.ActionAfterFailure) etc

The complete proxy configuration is given below. Please note that you have to enable vfs transport in axis2.xml in order to get this working.

In addition to the above proxy, you have to add the smooks-config xml file to the location specified in the proxy configuration. The smooks configuration for this sample is as follows:

You also need to add a local entry to the esb configuration to point to the smooks config file. (the above proxy configuration points to this local entry key for smooks configuration)

No comments:

Post a Comment