CloverETL is now CloverDX - Learn Why

Back to CloverDX Blog on Data Integration

Salesforce Connector in CloverDX

Posted by Pavel Najvar on Aug 9, 2016 3:24:13 PM

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 CloverDX 4.3 (formerly CloverETL).

You can now easily read, insert, update and delete Salesforce data with CloverDX, 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 CloverDX 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 CloverDX.

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 CloverDX

Just like other connectors in CloverDX, 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 CloverDX 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


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 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.


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.


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.


You can use CloverDX 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.


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 CloverDX 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.

Data integration software and ETL tools provided by the CloverDX platform (formerly known as CloverETL) offer solutions for data management tasks such as data integration, data migration, or data quality. CloverDX is a vital part of enterprise solutions such as data warehousing, business intelligence (BI) or master data management (MDM). CloverDX Designer (formerly known as CloverETL Designer) is a visual data transformation designer that helps define data flows and transformations in a quick, visual, and intuitive way. CloverDX Server (formerly known as CloverETL Server) is an enterprise ETL and data integration runtime environment. It offers a set of enterprise features such as automation, monitoring, user management, real-time ETL, data API services, clustering, or cloud data integration.