Jitterbit – Migrating dissimilar data, continuously even

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.

Published by salubrium

I am a Systems Administrator based in Sydney, Australia with some hugely varied interests: Topics covered are Virtualization, Web Hosting, Remote Desktop, Security and Backups, PHP, Python, MVC Frameworks, SEO

Leave a comment

Your email address will not be published. Required fields are marked *