Home » Internet Goodness, Linux Administration

Jitterbit – Migrating dissimilar data, continuously even

29 May 2007 No Comment

I run a webhosting business based in Sydney, Australia specialising in supporting open source software and managed / custom solutions for our clients.

A bit off-topic here but for any web hosts reading this, there’s some great up and coming GPL control panels AND billing systems.

Billing System
http://www.ultrize.com/minibill/ (Simple & small but looks good)
http://www.solid-state.org/ (More complete and last I looked they had WHM integration in Subversion)

but I digress..

The point is we are migrating from an old custom-written php/mysql billing system written by the previous owner of the business to Modernbill. The design, or lack of in the old database has meant that there’s no ‘relationships’ in the database, say between a customer, their account/s, their domains and their billing details. Maybe he came from some old ISAM days because all the relationships are dealt with at a php level – it’s all very ugly.

Anyway, it’s not something I want to contract out to some developers and I am not an sql genius so I went looking for a way to map fields for two dissimilar sources. I had read about Jitterbit a long time ago and thankfully stumbled across it again. Jitterbit’s USP = Business Integration Made easy – and it’s no far wrong.

Jitterbit is written in Java, relies on Postgresql and uses JDOBC to connect to different databases, but it doesn’t stop there. It can manipulate data from web services, flat text files, xml files, ldap, active directory and enables you to map that data into different web services, databases, flat files, xml etc.

They have “Jitterpacks” available, which are basically pre-configured setups for migrating data from<->to some enterprise software like Peoplesoft, JD Edwards,
Salesforce.com, SAP, Netsuite, SugarCRM, Compiere, Vtiger

Anyway, the data migration isn’t yet complete: we’re still cleaning up data using python for text manipulation prior to the import – it’s times like these that the power of regular expressions just shine.

Once we get through that, Jitterbit’s ‘data transformations’ are able to take care of the rest of it. It even enables us to run both billing systems simultaneously and keep the data synchronised.

A quick note on Installation and Usage:

Installation of V1.1.0 into Windows XP worked beautifully. An installation of V1.1.2 is proving difficult because Postgresql V8.1 keeps failing. Additionally, I aliened the RPM’s of client and server to deb’s for an Ubuntu Feisty install but came up against some issues, so I ran the server component in a Suse 10.2 virtual machine and am running the Jitterbit client from the Ubuntu machine.

You may find that the Mysql ODBC driver in the Suse repositories don’t work and you have to install the ODBC driver from mysql website.

You will then need to copy the driver to /usr/lib/unixODBC/

and then run:

myodbc3i -a -d -t”MySQL ODBC 3.51 Driver;DRIVER=/usr/lib/unixODBC/libmyodbc3.so;SETUP=/usr/lib/unixODBC/libmyodbc3S.so”

to register the ODBC driver. After that, restart jitterbit

/etc/init.d/jitterbit restart

and you should have your mysql driver there when you connect from the Jitterbit client.

Technorati Tags: , , ,

Powered by ScribeFire.

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.