WSO2 Salesforce Bulk and Rest Connectors

In this article, I am going to explain how to use Wso2 Salesforce connectors within Wso2 Micro Integrator. You can access the Salesforce Bulk Rest API and Salesforce Rest API via these connectors. Both connectors can be used to create, query, retrieve, update, and delete records and the main differences between them are Bulk connector allows the quick insertion of large sets of data into Salesforce or deleting large sets of data from Salesforce.

Before start working on the integration studio project, you should obtain the Client Id, Client Secret, Access Token, and Refresh Token from your salesforce account.

WSO2 Salesforce Bulk Connector

👉 In this example, I am going to Create a new job in the Salesforce account and insert data, and then I will retrieve the status of the created batch from the Salesforce account.

I am going to expose both operations via an API (Salesforcebulk-API) which has two resources.

/insertRecords : Create a new job and Insert data.

/getStatusOfBatch : Retrieve the status

Step 1

Create an Integration project in WSO2 Integration Studio.

Step 2

Right-click the project that you created and click on Add or Remove Connector -> Add Connector. Then you will get directed to the WSO2 Connector Store. So find the bulk connector and add it.

Step 3

Create the API as below.

<?xml version="1.0" encoding="UTF-8"?>
<api context="/salesforce" name="Salesforcebulk-API" xmlns="http://ws.apache.org/ns/synapse">
</api>

Step 4

Then create the insertRecords resource as below.

    <resource methods="GET" url-mapping="/insertRecords">
        <inSequence>
            <property expression="//operation/text()" name="operation" scope="default" type="STRING"/>
            <property expression="//object/text()" name="object" scope="default" type="STRING"/>
            <property expression="//contentType/text()" name="contentType" scope="default" type="STRING"/>
            <property expression="//objects" name="objects" scope="default" type="STRING"/>
            <property expression="//isQuery/text()" name="isQuery" scope="default" type="STRING"/>
            <log level="custom">
                <property name="operation" value="operation"/>
            </log>
            <salesforcebulk.init>
                <apiUrl>url</apiUrl>
                <tokenEndpointHostname>https://host.salesforce.com</tokenEndpointHostname>
                <accessToken>accessToken</accessToken>
                <apiVersion>56.0</apiVersion>
                <refreshToken>refreshToken</refreshToken>
                <clientSecret>clientSecret</clientSecret>
                <clientId>clientId</clientId>
            </salesforcebulk.init>
            <salesforcebulk.createJob>
                <operation>{$ctx:operation}</operation>
                <object>{$ctx:object}</object>
                <contentType>XML</contentType>
            </salesforcebulk.createJob>
            <log level="full"/>
            <salesforcebulk.init>
                <apiUrl>url</apiUrl>
                <tokenEndpointHostname>https://host.salesforce.com</tokenEndpointHostname>
                <accessToken>accessToken</accessToken>
                <apiVersion>56.0</apiVersion>
                <refreshToken>refreshToken</refreshToken>
                <clientSecret>clientSecret</clientSecret>
                <clientId>clientId</clientId>
            </salesforcebulk.init>
            <salesforcebulk.addBatch>
                <objects>{$ctx:objects}</objects>
                <jobId>{$ctx:jobId}</jobId>
                <isQuery>{$ctx:isQuery}</isQuery>
                <contentType>{$ctx:contentType}</contentType>
            </salesforcebulk.addBatch>
            <respond/>
        </inSequence>
        <outSequence/>
        <faultSequence/>
    </resource>

Once you execute the createJob operation, you can get the JobId as the response and you can use it as a input to the addBatch operation. Once you execute the addBatch operation, it returns batchId.

Find the below sample input payload for this resource.

<inserRecord>
    <operation>query</operation>
    <contentType>application/xml</contentType>
    <object>Contact</object>
    <isQuery>false</isQuery>
    <objects>
        <values>
            <sObject>
                <description>Created from Bulk API on Tue Apr 14 11:15:59 PDT 2009</description>
                <name>Account 711 (batch 0)</name>
            </sObject>
            <sObject>
                <description>Created from Bulk API on Tue Apr 14 11:15:59 PDT 2009</description>
                <name>Account 37811 (batch 5)</name>
            </sObject>
        </values>
    </objects>
</inserRecord>

Step 5

Then create the getStatusOfBatch resource as below.

<resource methods="POST" url-mapping="/getStatusOfBatch">
        <inSequence>
            <property expression="//jobId/text()" name="jobId" scope="default" type="STRING"/>
            <property expression="//batchId/text()" name="batchId" scope="default" type="STRING"/>
            <salesforcebulk.init>
                <apiUrl>url</apiUrl>
                <tokenEndpointHostname>https://host.salesforce.com</tokenEndpointHostname>
                <accessToken>accessToken</accessToken>
                <apiVersion>56.0</apiVersion>
                <refreshToken>refreshToken</refreshToken>
                <clientSecret>clientSecret</clientSecret>
                <clientId>clientId</clientId>
            </salesforcebulk.init>
            <salesforcebulk.getBatchStatus>
                <jobId>{$ctx:jobId}</jobId>
                <batchId>{$ctx:batchId}</batchId>
            </salesforcebulk.getBatchStatus>
            <respond/>
        </inSequence>
        <outSequence/>
        <faultSequence/>
    </resource>

The input payload for this resource should be like this. You can find the jobId and batchId from the response of the previous resource call.

<getBatchStatus>
    <jobId>7509q000001jtlWAAQ</jobId>
    <batchId>7519q000002CA1bAAG</batchId>
</getBatchStatus>

WSO2 Salesforce REST Connector

👉 In this example, I am going to query the existing account details from the salesforce and insert them into a MSSQL database.

Step 1

Create an Integration project and add the WSO2 Salesforce REST Connector by following the same previous steps.

Step 2

Create an API (salesforcerest) as below.

<?xml version="1.0" encoding="UTF-8"?>
<api context="/salesforcerest" name="salesforcerest" xmlns="http://ws.apache.org/ns/synapse">
    <resource methods="GET" url-mapping="/retrieveRecords">
        <inSequence>
            <sequence key="retrieve"/>
            <respond/>
        </inSequence>
        <outSequence/>
        <faultSequence/>
    </resource>
</api>

Step 3

Then create a Sequence(retrieve) as below.

<?xml version="1.0" encoding="UTF-8"?>
<sequence name="retrieve" trace="disable" xmlns="http://ws.apache.org/ns/synapse">
    <!-- Run salesforce query to retrieve data -->
    <salesforcerest.query configKey="SALESFORCEREST_CONNECTION_1">
        <queryString>select Id,Name,Type,RecordTypeId,Phone,OwnerId from Account LIMIT 5</queryString>
    </salesforcerest.query>
    <respond/>
</sequence>

So, In this example, I defined the salesforce connection parameters as a local entry.

<?xml version="1.0" encoding="UTF-8"?>
<localEntry key="SALESFORCEREST_CONNECTION_1" xmlns="http://ws.apache.org/ns/synapse">
    <salesforcerest.init>
        <hostName>hostname</hostName>
        <connectionType>init</connectionType>
        <timeout>3000</timeout>
        <apiVersion>v56.0</apiVersion>
        <apiUrl>url</apiUrl>
        <clientSecret>clientSecret</clientSecret>
        <clientId>clientId</clientId>
        <accessToken>accessToken</accessToken>
        <name>SALESFORCEREST_CONNECTION_1</name>
        <refreshToken>refreshToken</refreshToken>
    </salesforcerest.init>
</localEntry>

let’s assume the above query returns an output as below.

{
    "totalSize": 2,
    "done": true,
    "records": [
        {
            "attributes": {
                "type": "Account",
                "url": "/services/data/v56.0/sobjects/Account/0AAY"
            },
            "Id": "0019q01232034iESAvY",
            "IsDeleted": false,
            "MasterRecordId": null,
            "Name": "Cass Ferry Household",
            "Type": "Household",
            "RecordTypeId": "0127F032001kiqQAA",
            "Phone": "0433 854 096",
            "OwnerId": "0059q000000ISjcAAG"
}
]
}

So then evaluate the fields and insert the values into the Database.

<!-- Convert the payload into XML Format -->
    <property name="messageType" scope="axis2" type="STRING" value="application/xml"/>
    <!--Loop the Salesforce Response, Connect with MSSql Database, Insert Data -->
    <foreach expression="//records" id="foreach_1">
        <sequence>
            <dbreport>
                <connection>
                    <pool>
                        <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
                        <url>jdbc:sqlserver://localhost:1433;databaseName=salesforce_db;encrypt=true;trustServerCertificate=true</url>
                        <user>root</user>
                        <password>root</password>
                    </pool>
                </connection>
                <statement>
                    <sql><![CDATA[insert into records (Id,Name,Type,RecordTypeId,Phone,OwnerId) values (?,?,?,?,?,?)]]></sql>
                    <parameter expression="//Id" type="VARCHAR"/>
                    <parameter expression="//Name" type="VARCHAR"/>
                    <parameter expression="//Type" type="VARCHAR"/>
                    <parameter expression="//RecordTypeId" type="VARCHAR"/>
                    <parameter expression="//Phone" type="VARCHAR"/>
                    <parameter expression="//OwnerId" type="VARCHAR"/>
                </statement>
            </dbreport>
        </sequence>
    </foreach>
    <log level="custom">
        <property name="adding" value="Records added!!!!"/>
    </log>
    <payloadFactory media-type="xml">
        <format>
            <result>Data Inserted Successfully!!</result>
        </format>
        <args/>
    </payloadFactory>
    <respond/>