Integrate data from various apps in minutes

Jitterbit is an out-of-box integration suite that can integrate data between various enterprise apps, web services, databases, XML data sources and other systems

Tuesday, June 03, 2008

Integrating enterprise apps for data exchange is essential for every business today, in order to ensure smooth flow of data across the organization. This sort of data integration between various enterprise apps is a complex and timconsuming process and is also not the same for every business environment. Plus it also requires significant investments and resources. Microsoft's SharePoint and BizTalk Servers are some of the integration solutions that an enterprise can opt for. But if you're on a tight budget , then Jitterbit is an excellent alternative.

Jitterbit is an Open Source alternative that provides quick, easy, and affordable integration. It is a fully graphical integration solution that provides users with a platform to create and share their integration projects. Jitterbit can be used to integrate all major enterprise apps, databases, flat-files, Web Services, and messaging systems like WSDL or JMS. It has been optimized to function over Windows, Linux, or Solaris based OSs. With Jitterbit an organization can integrate data between independent apps easily and quickly, and also have a very low cost of ownership as compared to paid integration solutions.

In the following article we will integrate data coming from a database that will be converted to an XML format and will be posted on a location in the shared file server. Doing so we will see how to configure the Jitterbit server and its client.

Getting started
Jitterbit Integration suite can be availed from this month's PCQ_Professional DVD. It comprises of the following two components:

• Jitterbit Integration Environment: It has an intuitive GUI that enables the user to configure, test, deploy, and even manage the integration projects on Jitterbit Server.
• Jitterbit Integration Server: A scalable run-time engine that processes all operations for the integration projects. It uses a multi-threaded architecture so as to handle several transactions concurrently.

To install the Jitterbit server, extract the contents from the file jitterbit-server-1.3.2.19-with-postgresql-win32.exe to a directory. In this directory, run the setup.exe file which will initiate the Jitterbit server installation wizard. During the setup process, the wizard will prompt for installation of PostgreSQL server. If you already have PostgreSQL database server installed you can skip this step, else you can have the wizard install it for you.

Once installed, the wizard asks for credentials of database admin. You'll alos be asked to create the credentials for Jitterbit administrator. After the wizard completes its job, we can ensure that the server is running by checking that JitterbitTomcat and JitterbitProcessEngine processes are running through the Task Manager.

We can install the client on the same system or on any other system on the network. The Jitterbit Integration Environment can be installed through the Jitterbit-client-1.3.2.5-win32.exe file on this month's DVD. This file also extracts the installation files into a directory and through the setup.exe file; we can install the client interface of Jitterbit by following the wizard's steps.
With the client and server installed we can proceed with the integration of data between disparate sources, but before that we must ensure that the database server (PostgreSQL) is running. If it's not, then it can be started from Start Menu programs>PostgreSQL and then click on the Start Service option.

Starting Integration Project
Launch the Jitterbit Integration Environment, and from the options select the Create New Integration Project and name the project as TestBuild. Then click the Create button that will launch the main Integration environment screen. To test the integration as we create it, we must connect to the Jitterbit Integration Server. Clicking on the Login button on the main toolbar launches the Login window. Enter the credentials that you mentioned during the installation process. Upon successful connection to server, the user ID and the server name appear on the top right corner of the interface. Since we are getting data from a database and converting it to an XML file, we will define a complete operation for which we will be having the source, target, as well as transformation details.

For defining the target parameters, we have to specify the shared file server path and its login along with the name of file	To establish mapping between source and target, select appropriate elements from both panes and click on Map button

To create a new operation goto File > New > New Operation. In the operation window give a name to the operation and from activity dropdown menu, select Transformation. Now comes the part of defining the source. Click on the button next to the Source file and from the pop-up menu select Create New. A new Source window will open up. Give the name for this as MyDB and Type as database. For connection parameters, select driver as PostgreSQL ANSI and enter the server name or IP address for the PostgreSQL database (in our case it's localhost as we installed the client and server on the same machine). Now save the source details and through Test connection button, we can test the source's connectivity to the database.

After defining the source, we need to define the target. Target defines where you wish to put the information. In our case it's going to be an XML file to be saved on a shared file server. From the project tree, right-click on Target, and select New Target as option from the pop-up menu. Fill the fields as shown in the visual. Now comes the part of defining the transformation.

Transformation lets you map data from the source to a target format. In our case it will be tables from the MyDB database that will be mapped to an XML file. When you create a new transformation, a window opens where you have to define the source and target formats and in following steps you have to map the respective tables and their fields to the structure of the XML file. This XML file formatting can be done by specifying a schema or dtd (document type definition) file. For mapping the source fields to the target fields, select the sources filed and then selecting its corresponding target filed. Clicking on the Map button will establish the mapping relationship between them. Once the mappings have been defined, Jitterbit tests transformations on-the-fly by uploading sample data and running the test. To do this, click on Transformation toolbar's Toolkit button and select Test > Transformation.

Upon saving the transformation details, we can activate this operation by first deploying the project on the server by clicking on the Deploy button on the main toolbar and then clicking on the Activate button in the operation's toolbar the operation gets placed on the server's queue for execution. Jitterbit can similarly be used to source data from ERP apps and make that data available in a standardized format for other applications to consume.

Page(s)   1