SAP Cloud Integration with Salesforce – Part 3

[PriyankaChak]

Introduction:

In this blog post, I will explain how to use the Bulk API 2.0 in Salesforce using a case replication scenario. It also covers groovy script logic for fixed length to CSV conversion.

Scenario:

1. External system will place a fixed-length file in FTP server.


2. Cloud Integration flow will pick it from FTP server and convert to CSV format.


3. The flow will create a bulk job in Salesforce, upload data and close the job.

Prerequisite:

Go through the blog post SAP Cloud Integration with Salesforce – Part 1 to understand the prerequisite setup and basic configuration of Salesforce adapter.

Usage of Bulk API 2.0:

The REST-based Bulk API 2.0 is used to asynchronously upload, query, or delete large data sets in  Salesforce.

Help Link: Bulk API 2.0

Design Solution in Cloud Integration:

                                                                     Integration Flow

Step 1:

Configure FTP sender adapter.

Step 2:

Set header field names, fixed width and separator as properties for CSV file generation using Content Modifier.

Content Modifier

Step 3:

Use Groovy Script to convert fixed-length format to CSV format.

import com.sap.gateway.ip.core.customdev.util.Message

def Message processData(Message message) {

    def body = message.getBody(String)

    def width = "[" + message.getProperties().get("fixedWidth") + "]"

    def fieldWidths = evaluate(width)

    def separator = message.getProperties().get("separator")

    def values = ''

    def lines = ''

    body.splitEachLine('\n') { fields ->

    int start = 0;

    fields.each { 

     

        for (i in 0..<fieldWidths.size()) {

            lines = it.substring(start, start+fieldWidths[i]).trim()

            start = start + fieldWidths[i]

            values = i > 0 ? values + separator + lines : values + lines

        }

    }

    values = values + '\n'

    }

    message.setBody(values)

    return message

}

Step 4:

Use Content Modifier to add header field names in CSV format. Alternatively, the header field names can also be added in the previous Groovy Script.

Content Modifier

Step 5:

Use Content Modifier to store CSV formatted data as property.

Content Modifier

Step 6:

Call Local Integration Process for job creation in Salesforce via Process Call.

Step 6.a:

Use Content Modifier to set input body for bulk data load job creation.

Content Modifier - Properties

Content Modifier - Body

Step 6.b:

Use Request-Reply step to call Salesforce Bulk API 2.0 to create a job.

Salesforce Receiver Adapter

Step 7:

Set job id as property and CSV formatted data as message body using Content Modifier.

Content Modifier - Property

Content Modifier - Body

Step 8:

Use Request-Reply step to call Salesforce Bulk API 2.0 to upload the data.

Salesforce Receiver Adapter

Step 9:

The XML response returned from salesforce will contain default namespace. Use XSLT Mapping step to remove the default namespace.

<?xml version="1.0"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>

    <xsl:template match="*">

        <xsl:element name="{local-name()}">

            <xsl:apply-templates select="@* | node()"/>

        </xsl:element>

    </xsl:template>

    <xsl:template match="@*">

        <xsl:attribute name="{local-name()}">

            <xsl:value-of select="."/>

        </xsl:attribute>

    </xsl:template>

    <xsl:template match="comment() | text() | processing-instruction()">

        <xsl:copy/>

    </xsl:template>

</xsl:stylesheet>

Step 10:

Use Router step to check whether the operation was successful.

Router

Step 11:

If successful, call Local Integration Process for closing the job in Salesforce via Process Call.

Step 11.a:

Use Content Modifier to set the message body.

Content Modifier

Step 11.b:

Use Request-Reply step to call Salesforce Bulk API 2.0 to close the job.

Salesforce Adapter

Test Execution:

The sample data is shown below. Please note the data used in executing the scenario is test/ fake data.

Fixed Length file content

Result after conversion from fixed length to CSV:

CSV format

To check the job status from Salesforce, go to Setup -> Environments -> Jobs -> Bulk Data Load Jobs

Bulk Data Load Job

 

TakeAway Points:

1. Bulk API 2.0 requires (CSV) file representation for data upload.


2. The first row in a CSV file lists the field names for the sObject. Each subsequent row corresponds to a record in Salesforce.


3. Bulk API 2.0 supports several field delimiter characters: backquote (`), caret (^), comma, pipe (|), semicolon, and tab. The default delimiter is comma.


4. Records will be in a queue during data upload. Salesforce will process the records upon receiving the job close request.

Thank you for reading this blog post. Please feel free to share your feedback or thoughts or ask questions in the Q&A tag below.

QA link

Regards,

Priyanka Chakraborti

 

Previous – SAP Cloud Integration with Salesforce – Part 2