Introduction

This tutorial describes the necessary steps to access a Salesforce CRM system using a specific JDBC driver.

Note: The possibility of accessing Salesforce's own Webservice interface via HTTP/SOAP is not part of this tutorial. Also no Salesforce know-how is imparted, basic Salesforce and SQL knowledge is assumed.

Requirements:

• Access to a Salesforce test (sandbox) system, basic Salesforce knowledge

• Lobster Integration know-how

• Access to a Lobster Integration system with Salesforce license and two configured database aliases: "salesforce" and "salesforce_nobulk" (see section Salesforce via SQL - Preparations).

Phase 4 (Database nodes): Create/modify account

As a first step we want to create a new Account object in Salesforce. Assume we are exporting a company from our ERP system. This results in the following CSV file as a profile's input file:

As next step we create a profile with name "SF_account_import", imports above CSV into its source structure:

As target structure we create a new database node:

DB alias: "salesforce_nobulk"

Table/Proc.: "Account"

Now create the following field links:

At last tick the option "Activate Phase 4 (execute SQL)" in Phase 2, save the profile and start it once via manual upload of our CSV file. In the Control Center the profile should have run without errors and the newly created account should be visible within Salesforce.

Now that our new account exists, let's assume the company's address changes:

On a "regular" database node we would activate "try update before insert" (or "only update") and fill the key fields accordingly to update the correct record. The database node created by Salesforce hasn't set the property "SQL key = yes" on any field - thus we do not know, which account should be updated.

On many Salesforce systems objects receive additional customer specific fields (remember, standard objects can be manipulated), so called "external IDs". Those external ID fields are filled with unique IDs from their originating system (e.g. an ERP system). Let's extend our example file by an ERP system ID:

Of course we need an extra field in our source structure as well ("erp_customer_id"):

Within Salesforce we also have to add that field, so Account gets extended by a custom field "ERP_CUSTOMER_ID":

The selected option "External ID" is important here. This field is still missing in our target structure, so we delete the database node, import it again from Salesforce and rebuild the links.

Note: If the newly created custom field "ERP_CUSTOM_ID__c" is still not visible in our target structure, then we have become a victim of the target cache (see section Salesforce via SQL - Preparations) - a restart of the Integration Server is required!

If all fields are present we set the node's SQL mode to "try update before insert" again as we did before (this is "best practice" for SQL nodes accessing Salesforce). We now delete the previously created account "Sample Company LTD" within Salesforce.

Here comes a little "trick": On the target field "ERP_CUSTOMER_ID__c" we add an "EXT_ID" (separated by space):

After a new profile run we again have created an account "Sample Comany LTD" within Salesforce. We now modify the address in our file one more time:

With this changed file we yet again start the profile - no new account has been created, but the existing account's address has been updated.

Note: Using this special syntax <column name> EXT_ID we can tell the JDBC driver, which field it should use for identification of the desired Salesforce object. This field must be defined as an "External ID" within Salesforce.

Phase 5 (SQLBulkUnit): Add contacts

Our newly created account shall now get some contacts added. We receive the following file from our ERP system:

Within said file exists a field "erp_contact_id" - this has to be added within Salesforce (same way as before) as new custom field "ERP_CONTACT_ID" and marked as "External ID":

We create a new profile "SF_contact_import" and create the according source structure to process our contact CSV file:

On the target structure we again import a database node:

DB alias: "salesforce" (we now want to utilize the SQLBulkUnit)

Table/Proc.: "Contact"

Note: If the field "ERP_CONTACT_ID__c" is not visible in the target structure we again have become a victim of the metadata cache and have to restart the Integration Server.

Important note: The SQL mode MUST always be set to "try update before insert"! This is required by the SQLBulkUnit - if not set, we can receive errors in Phase 5 like "Malformed SQL Statement..." because the syntax "EXX_ID" is not resolved without said option.

Now create the following field links:

... and extend the SQL column name of field "ERP_CONTACT_ID__c" to "ERP_CONTACT_ID__c EXT_ID" (as shown above with account). The path of the database node is linked to source node "CON" - otherwise only the first contact will be imported.

In Phase 5 we select SQLBulkUnit. It's imperative to set "Target system is Salesforce" to true! The only remaining relevant parameter is "max. sql statements in batch" - it controls the number of records per batch for a given Salesforce bulk job. All other parameters are ignored for "Target system is Salesforce".

As final step we define a file response in Phase 6. Tick "is backup of target data" and select "Outpot of IU" in Content settings. The SQLBulkUnit - if used with Salesforce - returns the results of the bulk operation.

Now save the profile and start it once with our three-contact CSV file. We should see a successful job in the Control Center, however we will not find new contacts within Salesforce. Why?

Let's inspect the output file we received from the SQLBulkUnit (Control Center → Logs → Overview → right click on job → View output file):

Here we can find a few interesting facts:

a) The job number within Salesforce: "SalesforceJob: 7502o00000ZHb02AAD"

b) errors have been recorded for three records: "NumberRecordsFailed=3"

Obviously something went wrong. To find out the cause we inspect the bulk job within Salesforce. We can find the job using the job number from our file in Setup → Environments → Jobs → Bulk Data Load Jobs. In the IU's result file there is always "AAD" (or some other three letter combination) appended to the original job number, we therefore have to search for "7502o00000ZHb02":

Clicking the Job ID we can view its details:

Clicking on "View Result" (which triggers a download for an XML file) we can inspect the results of this job run:

As we can see, we received the same error message three times:

<fields>OwnerId</fields>

<message>Owner ID: owner cannot be blank</message>

Looks like the field OwnerId must not be empty!

Checking in Salesforce we identify that the field "Contact Owner" is a Lookup object onto User:

We thus have to provide a target field OwenerId in our profile containing the internal ID of a User object. To achieve this, we first have to add an "External ID" as custom field "ERP_PERSONAL_NR" of the User object:

To be able to identify a User via this "ERP_PERSONAL_NR" we have to set it on the User object (Service Setup → ADMINISTRATION → Users):

Now we are able to identify the desired User using "ERP_PERSONAL_NR" (as always appended by "__c" because it's a custom field):

Now we can use this select statement on the target field OwnerId to receive the Owner ID of our desired User. However, there is still room for improvement

We remember: the field "Contact Owner" of the Contact object is a Lookup object onto User (Data Type = Lookup(User)), which means that the Contact Owner possesses a lookup relationship onto the User object (see section Salesforce via SQL - Preparations). We can utilise this relationship to avoid the above select statement to access the Owner ID. For this we first require the relationship name. We can view it under "Field Name" in the field details of Contact Owner (sadly this Salesforce description is not very intuitive):

The relationship name thus is "Owner". Using the syntax "<relationshipname>.<external_id>" we can tell driver to fetch the User ID for us. For that to work we set the SQL column name of target field OwnerId to "Owner.ERP_PERSONAL_NR__c".

Note: If the lookup field is a custom field, then the relationship name is the field name plus __r, e.g. if the API name is ‘Product__c’ and the field name is ‘Product’, then the relationship name is ‘Product__r’.

Important note: In this case it is mandatory to use quotes " in the SQL column name!

... and the field value to fixed 4711. This tells the driver (free translation): "Check the relationship Owner (points to User), find out which User has an ERP_PERSONAL_NR of 4711 and put its ID into field OwnerId".

After saving a rerunning the profile we can find those three contacts within Salesforce - without using the internal User ID at all"

Important note: <external_id> must be identical to a field's API name that has been created as External ID - otherwise the lookup won't work (error messages are the consequence). The lookup also won't work if the field type is a polymorphic field (lookup to more than one object, example in Standard objects: field "Related To" of object "Event"). In this case we are forced to use a select statement.

To finish our mission we have to also add the newly created contacts to our account "Sample Company LTD". Contact owns a field AccountId, which is of type Lookup(Account). As we have just learned, "<relationshipname>.<external_id>" on the SQL column name will give us the AccountId. For the relationship name we consult the Salesforce Object Manager (Details of fiel "Account Name" in "Contact" - Field Name = "Account"). As External ID we use ERP_CUSTOMER_ID from further above. This results in the following SQL column name: "Account.ERP_CUSTOMER_ID__c" (mind the quotes!). The last missing step is to fill the target field AccountId with the ERP customer ID of our target account. The ID is already present in our input file, we therefore just have to link AccountId to the source filed erp_customer_id. After saving and one more profile rerun we should now see three contacts below account "Sample Company LTD".

Some additional hints:

Besides lookup relationships there exist master-detail relationships (not for Standard objects, as those cannot be details according to the Salesforce documentation). The above syntax works for master-detail relationships as well, with one deviation: The suffix is "__r" instead of "__c". Example:

A given object owns a field "AccountRef__c", which is of type Master-Detail(Account). Thus the correct relationship name is "AccountRef__r".

File upload

Now we want to attach a file to our account "Sample Company LTD". We create a profile "SF_file_upload" with the following CSV as input file:

<path to upload file> = Path to the file we want to upload, e.g.: ./temp/upload/sample.pdf

We create the according source structure:

On the target structure we create yet another database node:

DB Alias: "salesforce_nobulk"

Table/Proc.: "ContentVersion"

The file must be encoded in base64 and attached to field "VersionData". We use the function "read file into base64 coded byte-array(...)" for that:

Parameter a of the function is filled with the content of the source field path, which contains the path to the file on our filesystem (which of course must exist). Besides VersionData serveral other mandatory fields exist, which have to be filled:

FirstPublishLocationId is a polymorphic field, thus we cannot use the relationship name and are forced to utilize a select statement.

Save the profile (don't forget to activate Phase 4) and start it once with its input file. Now the uploaded file should be visible in the Salesforce Account "Sample Company LTD":

Done