ETL Capabilities of WSO2 Enterprise Integrator — Part I
Data migration from a legacy database (RDBMS) to Salesforce via a Schedule task
The first blog of the series will take you through migrating data from a legacy database (RDBMS) to Salesforce using the WSO2 Enterprise Integrator (EI). We have used the tooling support provided via WSO2 EI tooling to create and manage the artifacts.
An brief overview of the use case is as follows:
- The legacy database system used here will be a MySQL database. This database will store information about Salesforce accounts.
- The legacy database will be exposed as a data service. This will expose a REST/SOAP interface to interact with the legacy database.
- A REST API will be defined to handle the data migration process. The account information will be migrated to Salesforce using EI and the Salesforce connector. This REST API would first get all records in the legacy database using the exposed data service and will add each of those as new Accounts to Salesforce.
- This data migration will happen via a scheduled task i.e. it will migrate data periodically on a given time everyday. So ideally the schedule task would invoke the REST API exposed to carry out the migration.
The diagram below gives you a high level overview of the use case:
Follow the steps given below:
- First define a data service in EI to expose the legacy database. The data service should have a query to retrieve all available data in the database.
- Then define a REST API to handle the data migration process. We have used EI tooling to create this artifact.
Deploy the created REST API in EI. Now let’s go through each element in the API and understand what each means.
The ‘callout mediator’ is used to block an external service invocation during mediation. Here it is used to invoke the data service exposed to retrieve the account information from the legacy database in a blocking manner. The ‘target’ property specified will be the node or the property of the request message to which the payload (resulting from the value specified for the Source parameter) would be attached.
The response from the data service i.e. account information is set to a property which is used by ‘iterate’ mediator.
‘salesforce.getUserInfo’ will create a connection with Saleforce with the local entry referred in the ‘configKey’ attribute and will get information about the user logged in.
The local entry which defines the Salesforce connection configuration will be as follows:
The ‘iterate’ mediator is used to iterate over all the accounts retrieved and to add each of them to Salesforce. For each account it will create a ‘xml’ payload using the ‘payload factory’ mediator with the format required by the Salesforce connector.
Then it will send the payload created to the ‘salesforce.create’ operation to create the account in Salesforce. This will be done for all the accounts retrieved from the legacy database.
After adding all the accounts to Salesforce without any failure the ‘payload factory’ mediator will be used to create success message and this will be responded back to the client using the ‘respond’ mediator.
3. Define a schedule task to carry out this data migration periodically
We can specify a cron expression to determine how the task should be scheduled. Here we have scheduled the task to be run at 1.30 am.
We also have to specify a sequence in which we define the operations that the schedule task should carry out. In the sequence specified in the schedule task above we simply invoke the REST API created above which will trigger the data migration process.
After the schedule task is executed you can see that the account information in your legacy database system has been added to Salesforce.
For more information refer the following resources:
- https://docs.wso2.com/display/EI640/Exposing+a+Datasource+as+a+Data+Service
- https://docs.wso2.com/display/EI640/Working+with+Connectors
- https://docs.wso2.com/display/ESBCONNECTORS/Configuring+Salesforce+Connector+Operations
- https://docs.wso2.com/display/EI640/Scheduling+ESB+Tasks
- https://docs.wso2.com/display/EI640/ESB+Mediatorsss
Stay tuned for the second blog post of the series which will give you an understanding of migrating real time data from Salesforce to Data Lake (MongoDB) and legacy database system (RDBMS) !! :) :)
