Salesforce Connector in CloverETL

Share this article:

In an effort to constantly improve the lives of our users, we’ve enhanced our Salesforce connectivity and added a new, user-friendly (yet powerful) Salesforce connector into the CloverETL 4.3.

You can now easily read, insert, update and delete Salesforce data with CloverETL, without having to expose yourself to the nuts and bolts of the two systems talking to each other as before. All you need to know are your login credentials. Let me show you how to use this cool new feature.

With three new components at hand, SalesforceBulkReader, SalesforceBulkWriter and SalesforceWriter you no longer need to manage a jobflow of API calls yourself. The components work just like any other modern reader or writer - just a few simple controls and you’re there.

The two bulk components use Salesforce Bulk API which is great for working with batches of data, large and small. However, beware of some limitations. First, it’s not optimized for quick repeated calls. Each operation takes at least a few seconds to perform so you don’t want to use it for realtime or near real time processes. In most cases you’ll use CloverETL for moving larger datasets anyway so you should be fine in this department. Secondly, SOQL queries (Salesforce’s query language similar to SQL) are somewhat limited with this API (e.g. no support for subqueries). After all, the rationale behind all this is performance, and you’re getting plenty of it.

Also, keep in mind that using Salesforce components counts towards your API limits. Each read or write takes 7 calls (or more with data sets > 1 GB).

The third component, the SalesforceWriter, uses the standard SOAP API which has some pros and cons compared to the other one. First of all, with SOAP you're using less API calls per write from your Salesforce limit (1 for SOAP vs 7 at least with Bulk API). However, it's not designed for writing large data sets - 200 records per one write maximum. You get better support for advanced SOQL statements and mainly, support for writing Attachments.

Authentication (Security Token)

You’ll need a login, password and security token for your Salesforce instance before starting with CloverETL.

Don’t worry if you don’t have your security token now. It’s an additional secret that you use to connect external applications to Salesforce. You can easily obtain yours by navigating to your account settings.

Get Security Token:

My Settings > Personal > Reset Security Token

Salesforce connector

Salesforce connector

Using Salesforce connector in CloverETL

Just like other connectors in CloverETL, you create a connection first.
Salesforce connector

Salesforce connector

Securing your credentials: Note, that you can use parameters (or secure parameters) in this dialog. Simply type in the parameter name, e.g. ${SALESFORCE_PASSWORD}, in place of the masked values.

Read (Query)

SalesforceBulkReader component takes the previously defined connection and a SOQL query.

Salesforce connector

SalesforceBulkReader with SOQL query

There is a great SOQL Query tool as part of the Salesforce developer Workbench that let’s you select objects, fields and filters to generate queries visually right from within your SFDC instance. I recommend checking the tool out. It’s a great help if you don’t remember all the objects and fields names in your system.

Salesforce Connector

SOQL Query tool

Your query dictates the internal fields which you can use in the component’s Output Mapping parameter. If you want to create CloverETL metadata from the query, just drag and drop the fields from left to right. Nonetheless, you don’t have to set any mapping at all. In such case the component will try to map Salesforce fields to your metadata automatically by field names.

Salesforce connector

Create metadata from Salesforce query by dragging fields from left to right

Beware of compound fields, such as BillingAddress. These aren’t supported by the underlying API and thus you’ll get an error (FUNCTIONALITY_NOT_ENABLED). To read address data, you have to select individual address fields instead of the compound.

To use dates in the SOQL query, you’ll need to use the following format: CreatedDate >= 2016-07-27T00:00:00Z

Insert

In most cases you'll be using SalesforceBulkWriter for all operations that modify or insert data in your Salesforce database. If you need to write attachments, please refer to Attachments section.

To insert data, simply map your input records onto automatically generated fields in the selected target object (right panel of the Input Mapping dialog).

Salesforce connector

Propagating generated ObjectIDs is extremely easy. Notice the graph below. I just connected a writer (“Accepted”). Without any further configuration, the output metadata is propagated as an exact replica of your input, with additional “ObjectId” field that’s populated with Salesforce internal IDs of the newly created records. This is very useful for cascading writes of dependent objects (e.g write all Accounts first, then join the “accepted” stream with Contacts using your own IDs and finally use the joined Account’s ObjectId as parent in the inserted Contact).

Salesforce connector

Update

Update operation works just like Insert, you just need to make sure you map “Id” field which tells Salesforce which record to update (you’ll find it in Input Mapping option).

To simulate “UPDATE <table> WHERE <condition>” you’d use SalesforceBulkReader to fetch object Id’s (the WHERE part) and then a SalesforceBulkWriter with “Update” as Operation and properly mapped “Id”

Salesforce connector
Only fields that you actually map in Input Mapping are updated, so don’t to worry about accidentally overwriting your data.

Upsert

Update or Insert (aka “Upsert”) is a common operation where you want to insert new records and update existing ones in a single run.

You’ll need to set “Upsert external ID field” - name of a field you want to use as a key to determine whether a record needs to be updated (if the same key is found in the database) or inserted. Obviously, this does not have to be actual Id - for example, you can use email address to determine whether a record gets inserted or updated.

Delete

Deleting records follows similar methodology and configuration as Update. Only this time you’re allowed to map only the “Id” field.

By the way, if you try to delete multiple records from Salesforce web interface, you’ll quickly realize you can delete only 250 records at a time. The reason for this eludes me but having a mass delete operation in CloverETL is a blessing in this case (there’s no such limitation there).

Hard Delete

Standard delete operation does not physically remove the data from your database, it merely moves it to Recycle Bin. In order to remove data right away, without having to take out the trash, you can use Hard Delete. However, this operation requires elevated permissions - read this short article on how to set up the Bulk API Hard Delete permission for your account. This operation is only available in the SalesforceBulkWriter.

Attachments

You can use CloverETL to upload and manipulate attachments in Salesforce. In order to write or update attachments (Attachment object) you'll have to use the SalesforceWriter component (the SOAP-based one). Remember, it won't work with SalesforceBulkWriter.

You can either upload attachments from files by mapping records with filenames in them (Body_FileURL), or you can pass binary data directly through the input edge (Body). You'll need to have Salesforce ParentId of an object to associate the Attachment with.

cloveretl-salesforcewriter-attachment.png

Catching Errors

You may have noticed the SalesforceBulkWriter component has a second output port. If connected, records that are rejected from the operation will get send to this port, with additional information in the automatically added “Error” field. If you don’t connect the error port, the component will fail on the first error.
Salesforce connector

We’re releasing these Salesforce components as part of the second milestone of CloverETL 4.3 with final production release to come at the end of August/early September 2016.

Please note this functionality is not available in the Community edition.

Share this article:
Contact Us

Further questions? Contact us.

Forum

Talk to peers on our forum.

Want to keep in touch?

Follow our social media.

Topics

see all

Recent Posts