Open in app

Sign in

Write

Sign in

Data Integration Capabilities of WSO2 Enterprise Integrator

Natasha Wijesekare
10 min readJun 10, 2019

Data integration is a dominant part of an integration process. Let’s consider a typical integration process that is managed by WSO2 EI. To complete this integration use case data that is stored in different datasources are needed. Data service functionality embedded in WSO2 EI enables you to manage this integration scenario by decoupling the data from the datasource layer and exposing them as web services or REST/SOAP services. This will expose the capabilities of the datasource as a service. So the integration flow defined in WSO2 EI will have the capability to manage the data using data service. After the data service is defined you can handle the data stored in the datasources by calling the relevant operation defined in the data service (Eg: CRUD operations)

This blog will demonstrate the capabilities of WSO2 EI in the data integration area. WSO2 EI can be used to seamlessly work with any complex data integration scenarios.

  • Comprehensive and OOTB support for data services and integration
  • Extensive tooling support for data integrations
  • Ability to develop data models that vary from simple data mappings to complex data models
  • WSO2 EI connectors to encapsulate third-party API calls. These connectors enable you to connect to and interact with the APIs of services such as Twitter, Salesforce, JIRA etc.

The use case is based on real business case with major simplifications in order to speed up preparation time.

Following is a high-level overview of the scenario:

Systems involved

All systems listed here are provided for demo scenario understanding only:

  1. Salesforce CRM — CRM system
  2. PPM System — Project portfolio management system
  3. FM System — Financial and business management system

For the purpose of the demo we have mocked the PPM System and FB System as REST APIs.

Scenario

The steps followed in the scenario is as follows:

  1. A sales team member creates a lead entity in Salesforce
  2. A lead entity is evolved into a contract entity after the contract is signed. Let’s assume a contract was signed, then a sales team member should go and change the contract status of the lead entity to “Signed”.

An event is triggered from Salesforce when this change happens and it is notified to WSO2 EI. The following will happen with this event trigger using WSO2 EI:

3. After the contract is signed a project entity will be created in the PPM system.

Following are the attributes of the project entity created in the PPM System:

5. The project id field of the entity in Salesforce is updated with the value of the project id generated.

6. Upon project creation in the PPM system, a project entity is created in FM system. The project amount should be calculated and added to the FM system (Amount = Project Amount + 20%).

Following are the attributes of the project entity created in the FM System:

7. After project creation in the FM system a structured json file is created on a FTP server with the project details

8. A MySQL database table is populated with the project details

Prerequisites

Configuring the Salesforce

PushTopic pushTopic = new PushTopic();pushTopic.Name = 'TriggerContractStatus';pushTopic.Query = 'SELECT Name, Id, Contract_number__c, Contract_signed_date__c, End_date__c, Contract_amount__c, Contact_person_s_fullname__c, Contact_phone_number__c, Contact_email__c, Company_Name__c, Company_tax_ID_number__c, Contract_Status__c,Project_ID__c , Street__c, City__c, State__c, Postal_Code__c, Country__c FROM Entity__c WHERE Contract_Status__c=\'Signed\'';pushTopic.ApiVersion = 37.0;pushTopic.NotifyForOperationCreate = false;pushTopic.NotifyForOperationUpdate = true;pushTopic.NotifyForOperationUndelete = false;pushTopic.NotifyForOperationDelete = false;pushTopic.NotifyForFields = 'Where';insert pushTopic;

Configuring the Json-server to mock the PPM System and FM System

PPM System :

FM System :

  • Start the 2 mock servers by executing the following commands on the given ports
json-server --watch PPM-System.json --port 3001json-server --watch FM-System.json --port 3002

Configuring the database

  • Install the MySQL server.
  • Download the JDBC driver for MySQL from here and copy it to your <EI_HOME>/lib directory.
  • Create a database named “entity”.
  • Create the “project” table inside the “entity” database
Use entity;CREATE TABLE project (Project_ID varchar(20) NOT NULL, Customer_Name varchar(255) NOT NULL, Project_Amount varchar(255) NOT NULL, PRIMARY KEY (Project_ID));

Configuring the FTP Server

If you need to save the file in a FTP server :

  • We need to have a FTP server running to save the file with the project details, so we need to first install one.

Refer the following to install a FTP server:

If you need to save the file in the local file system, then you do not need a FTP server.

Configuring WSO2 EI

Before starting the EI server follow the steps given below:

Follow the steps below to import the Salesforce certificate into the EI client keystore:

  • Log in to your Salesforce account in your browser (e.g., https://login.salesforce.com), and click the lock on the address bar to view the certificate.
  • Export the certificate to the file system.
  • Import the certificate into the EI client keystore using the Management Console or by using the following command:
keytool -importcert -file <certificate file> -keystore <EI_HOME>/repository/resources/security/client-truststore.jks -alias "Salesforce"

After starting the EI server follow the steps below:

Creating the artifacts

  • To create the artifacts we will be using the WSO2 EI tooling
  • Install WSO2 EI tooling using the instructions in Installing Enterprise Integrator Tooling.
  • In Eclipse, open the Developer Studio dashboard by clicking the Developer Studio menu and choosing Open Dashboard.
  • Click ESB Solution Project to create a new ESB project.
  • Create a project named IntegrationProject.

Be sure to select the following check boxes so that the relevant projects will be created.

  • Create Connector Exporter Project
  • Create Composite Application Project

Click Finish. You have now created the following projects in the Project Explorer: IntegrationProject, IntegrationProjectCompositeApplication and IntegrationProjectConnectorExporter

  • Let’s configure the inbound-endpoint. Right-click IntegrationProject in the Project Explorer and navigate to New -> Inbound-Endpoint.
  • Ensure Create a New Inbound Endpoint is selected and click Next.
  • Enter the information given below to create the new inbound endpoint:
  • Once the inbound endpoint is created, go to the source view by clicking on the Source tab at the bottom. Replace the content in it with the content in the following configuration file:
  • Update the username and password values with the user name and password of your Salesforce developer account. The password that should be provided here is a concatenation of the user password and the security token provided by Salesforce. For information on creating a security token in Salesforce, refer https://help.salesforce.com/articleView?id=user_security_token.htm&type=5.

For more information on configuring the Salesforce inbound endpoint, refer https://github.com/wso2-extensions/esb-inbound-salesforce

  • Since we will be using the Salesforce connector in the project to update the records, let’s add the connector to the project. Download the Salesforce connector from the store.
  • To add the connector to the project right-click IntegrationProject and click Add or Remove Connector.On the wizard that appears, select Add Connector and click Next. Since we have already downloaded the connect select the Connector location option and browse to the connector file from the file system. Click Finish. The connector is imported into the workspace and available for use with all the projects in the workspace.
  • Then let’s add the imported connector to the connector exporter project. Right-click on IntegrationProjectConnectorExporter and point to New and then click Add/Remove Connectors. Click Add Connector and then select Workspace. This will list down the connectors that have been imported into the EI Tooling environment workspace. Select the Salesforce connector and click OK.

For more information on working with connectors, refer https://docs.wso2.com/display/EI640/Working+with+Connectors+via+Tooling

  • To use the Salesforce connector, add the <salesforce.init> element to your configuration before carrying out any other Salesforce operations. Right-click IntegrationProject in the Project Explorer and navigate to New -> Local Entry.
  • Ensure Create a New Local Entry is selected and click Next.
  • Enter the information given below to create the local entry:
  • Now let’s create the sequence to be invoked once an event is received by the inbound endpoint. Right-click IntegrationProject in the Project Explorer and navigate to New -> Sequence.
  • Ensure Create a New Sequence is selected and click Next.
  • Enter the information given below to create the sequence:
  • Once the sequence is created, go to the source view by clicking on the Source tab at the bottom. Replace the content in it with the content in the following configuration file:

If you need to save the file in a FTP server

Update the URI pertaining to your FTP server in Line 138:

Eg: <address uri=”vfs:ftp://linda:linda123@10.100.1.228/files"/>

If you need to save the file in the local file system

Update the URI pointing to the directory of the local file system where you want to save the file

Eg: <address uri=”vfs:file:///home/user/test”/>

Packaging artifacts into composite applications

  • Right-click on IntegrationProjectCompositeApplication (this is the C-App project) in the Project Explorer and click Export Composite Application Project to create a CAR file out of that project.
  • Give the location of the CAR file.
  • Select the project/artifacts you want to include in the CAR file. Let’s select the following projects out of the available projects:
  1. IntegrationProject — This project will have all the artifacts we created in the previous steps
  2. IntegrationProjectConnectorExporter — This project will have the Salesforce connector
  • Click on Finish

For more information refer: https://docs.wso2.com/display/ADMIN44x/Packaging+Artifacts+into+Composite+Applications

Deploying CAR files in WSO2 EI

  • Since we already started the EI server log into the management console using https://localhost:9443/carbon as an admin. (username — admin, password — admin)
  • Click the Main tab on the Management Console, go to Manage -> Carbon Applications and then click Add
  • Click Choose File, select the CAR file we created in the previous step and click Upload.
  • After you upload the CAR file, follow let’s check if the CAR file is successfully deployed. Click the Main tab on the Management Console, go to Manage -> Carbon Applications and then click List. The Carbon Applications List screen appears. If successfully deployed, the C-App will be listed here.

Upload the data service

  • Since we have already configured the database, let’s upload the data service that is already created to EI.
  • Save the following data service configuration file as “SampleDataService.dbs” in to a directory of your choice
  • Update the username and password of the MYSQL server running in your machine.
  • Click the Main tab on the Management Console, go to Manage -> Services -> Data Service and then click Upload. Upload the dbs file that you saved before.
  • After you upload the data service file, let’s check if the data service is successfully deployed. Click the Main tab on the Management Console, go to Manage -> Services and click List. The Deployed Service List screen appears. If successfully deployed, the data service will be listed here as SampleDataService

Demo

Let’s demonstrate the capabilities of WSO2 EI

  • Login to your Salesforce account
  • The custom object tab you created for the custom object “Entities” is displayed at the top. Click on the “Entities” tab to view the list of entity records available.
  • Click on New button (at the right corner) to create a new lead entity.
  • The Edit mode of a new lead entity appears on the screen. Fill in the fields as much as you can or as required. Leave the value of “Contract Status” as it is without changing (By default it will be ‘Not-Signed’)
  • When you’re done, click the Save button. The Entity detail page appears. On this page, you can click the Edit button whenever you need to modify information on the record.
  • A link to the first entity appears in your Recent Items list in the left sidebar.
  • Then let’s assume that the lead entity created before was evolved into a contract entity after signing the contract. This information should be updated in the entity record.
  • Go to the record you want to edit and click the Edit button. It will automatically open the entity record in Edit mode.
  • Change the value of “Contract Status” to “Signed” by selecting it from the drop down
  • This will trigger an event to WSO2 EI.

Changes after the event trigger

  • Use a client like Postman to test if the 2 systems i.e. PPM System and FM System has been updated with the project entity details.

PPM System — http://localhost:3001/contract_entities

FM System — http://localhost:3002/project_entities

You can see that both the systems have been updated with project entity details.

  • Use a MySQL client to check if the project entity record has been added to the database.
  • Or you can also use a client like Postman to invoke the data service we deployed in WSO2 EI to check if a new project entity has been added.

URL: http://localhost:8280/odata/SampleDataService/entity/project

  • Navigate to the directory location you specified to save the file with the project entity details in the FTP server or in your local file system. You can see that a json file (<company_name>-<project-id>.json) has been created with the project details.
  • Let’s check if the project_id generated by PPM System has been updated in the record in Salesforce. Login to Salesforce and Click on the Entities tab at the top. This will list all the entities that are available. Click on the Edit button of the entity that you changed and check the Project Id field value. You can see that it has been updated with the value of the generated project id.

If the changes given above were observed, you have successfully configured and demonstrated the data integration capabilities of WSO2 EI.

Now as for how to end with a bang? Well, that’s another blog post for another time so stay tuned:) :)

Wso2 Ei

--

--

Natasha Wijesekare

Written by Natasha Wijesekare

54 Followers

Associate Lead Solutions Engineer at WSO2

Help

Status

About

Careers

Blog

Privacy

Terms

Text to speech

Teams