Use of Salesforce Destination Component

Salesforce Destination Component is an SSIS data flow pipeline component that can be used to write data to your Salesforce.com instance. You may Create, Update, Delete, Upsert, Merge, or HardDelete Salesforce records using the Salesforce Destination Component. 

Salesforce Destination Component includes the following three pages to configure how SSIS writes data to Salesforce.com.

  • General
  • Columns
  • Error Handling

General page

General page allows you to specify the general settings of the destination component. 

Salesforce Destination Editor

Salesforce Connection Manager

Salesforce destination component requires a Salesforce connection in order to connect to your Salesforce.com instance. The Salesforce Connection Manager option will show all Salesforce connection managers that are available in the current SSIS package.

Batch Size

Batch Size option allows you to specify how many records you want to write to Salesforce with each service call.

When Use Bulk API option is not checked, the default value of Batch Size is 200, and the maximum allowed value is also 200. 

When Use Bulk API option is checked, the default value of Batch Size is 5,000, and the maximum allowed value is 10,000.

Action

Action option specifies how the data should be written to Salesforce.com. There are 6 action types available. 

  • Create: Create new record(s) in Salesforce
  • Update: Update existing record(s) in Salesforce
  • Delete: Delete record(s) from Salesforce
  • Upsert: Update an existing record in Salesforce if matching record can be found. Otherwise, create a new record using the information from upstream pipeline components. 
  • Merge(since v7.0) : Takes up to three records as its input and perform a Salesforce merge action on them by retaining the master record and delete other records.

  • Merge action is only possible with the following Salesforce objects:
    • Account
    • Contact
    • Lead
    • Person Account
    When Merge action is chosen, you must provide the following two input columns in order to merge the records:
    • MasterRecordId: the ID of the record that other record(s) will be merged into.
    • RecordsToMergeIds: the ID(s) of other record or records to be merged into the master record. Minimum of one, maximum of two. ID values should be separated by semicolon if input contains more than one record.
  • Hard Delete(since v8.0) : Permanently delete records from Salesforce when Bulk API enabled
  • Undelete (since v9.0): Undelete records from the Recycle Bin.
Destination Object

Destination Object option specifies which Salesforce object to write the data. When the option is clicked, SSIS Integration Toolkit will retrieve a list of all available Salesforce objects for the selected Salesforce connection.

Upsert External ID Field

Upsert External ID Field option specifies the External ID field for Upsert action. It is required when Upsert action is used. You may use the dropdown list to select an External ID field.

Use Bulk API

Use Bulk API option dictates whether you want to use Salesforce Bulk API to write to your Salesforce organization. By default, this option is not selected. When using Salesforce Bulk API, your data will be processed asynchronously, which can provide some significant performance improvement, and also using Salesforce Bulk API, you are not throttled by Salesforce daily API limits. However before v6.0, our software does not get immediate results back from Salesforce so the SalesforceRecordId field will always be empty in the Default Output when Bulk API is used. This is no longer the case after our v6.0 release.

When the option is enabled, the following bulk API related options become available for configuration.

Write batch files with UTF-8 encoding

When enabled, the batch files will be created using UTF-8 encoding, which can help improve working with non-ANSI characters in case there are any. We generally recommend turning on this option for best encoding comparability.

Bulk Data Format

There are 4 options available.

  • ZIP
  • ZIP_CSV
  • XML
  • ZIP_XML
We generally recommend the ZIP_CSV option, as the format will create the smallest batch files for the same amount of data to be processed.
Concurrency Mode

There are two options available.

  • Parallel: this means that the batch file will be processed in a paralleled fashion at the Salesforce side, this will provide some better processing performance, but it also means that the sequence of the submitted batches is not respected when they are processed by Salesforce.
  • Serial: this means that the batch file will be processed in a serial fashion at the Salesforce side.
Bulk JobId Variable

Bulk JobId Variable option allows you to specify an SSIS variable that you can write the bulk job's ID in order to track the status of the job in Salesforce later.

Remove Invalid Characters

When enabled, the Remove Invalid Characters option will remove any invalid characters from the input which can avoid an XML exception when the components tries to construct the SOAP request to be sent to Salesforce. Those invalid characters are usually not accepted by Salesforce even posted. 

Ignore Null-Valued Fields

The Ignore Null-Valued Fields option allows you to ignore any fields that have a null value. By ignoring a field, the null value will not be posted to the Salesforce server. This can help avoid the situation that you overwrite non-empty values with an empty value, if your requirement dictates so.

Send datetime values in UTC Format

Enabling this option allows you to send date time values to Salesforce in UTC format. If left unchecked, any date time value passed into Salesforce will be treated as integration user's local time.

Send empty string as Null values (since v8.1)

Enabling this option allows you to specify whether to send empty string as NULLs when writing to Salesforce. When disabled, empty strings will not be converted to NULL value, therefore will not be posted to Salesforce. This can be helpful in the case that you want to retain original values in Salesforce when the incoming value is empty (not NULL though).

Refresh Salesforce Metadata Button

By clicking Refresh Salesforce Metadata button, the component will retrieve the latest metadata from Salesforce and update each field. This feature works by performing the following three actions.

  • Update any existing object fields to the latest metadata
  • Add any new object fields that have recently been created in Salesforce
  • Remove any object fields that have recently been deleted from Salesforce

After clicking this button, you will receive the following screen once the refresh is done.

Refresh Metadata

Map Unmapped Fields Button

By clicking this button, the component will map any unmapped Salesforce fields by matching their names with the input columns from upstream components. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish the association between input columns and unmapped Salesforce fields.

After clicking this button, you will receive the following message.

Map Unmapped Fields

Clear All Mappings Button

By clicking this button, the component will reset all your mappings in the destination component.

Columns page

The Columns page of Salesforce Destination Component allows you to map input columns to Salesforce fields. 

In Columns page, you will see a grid that contains five columns as below.

  • Input Column: You can select an input column from upstream component for the corresponding Salesforce field.
  • Destination Salesforce Field: The Salesforce field that you are writing data. 
  • Text Lookup: This is a feature that we introduced since v3.0. This option is only available for Salesforce lookup fields. When this option is selected, the component can perform lookup based on a text value of the target object. For further information about how to use Text Lookup feature, please refer to Working with the Text Lookup Feature section below.
  • Data Type: The target Salesforce field's data type.
  • Unmap: This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Salesforce Destination Editor

Working with Text Lookup Feature

The Text Lookup Feature allows you to perform lookup based on the text values of the target object. To configure Text Lookup feature, click the ellipse button in Text Lookup column which is available in Salesforce destination component's mapping page. You will be presented with the following screen.

Text Lookup Screenshot

You can configure the Text Lookup setting by toggling Use Text Lookup option. When you have checked Use Text Lookup option, you will see a list of the target objects for the involved lookup field, and you can configure a different lookup strategy for each target object.

Choose Target Field(s): Using this option, you can specifically choose which text field should be used for the lookup purpose. When you choose this option, you will be presented with a list of the target objects for the lookup field, and you can choose to use different lookup strategy for each target object. In this list you will be able to see the following options.

  • Opt Out?: When chosen, the lookup object will be excluded from being used for text lookup purpose.
  • Target text/integer field: You can choose a text or integer field that will be used for the lookup purpose.
  • Optional default value (if no match): When specified, the component will use this default value to perform the lookup should the input value lookup fail.

Note: In order for Text Lookup feature to work efficiently, we populate an internal cache when a lookup is first requested. The cache is created by querying data from the target object, which would consume some Salesforce API calls.

In addition to the above settings, Text Lookup feature also offers the following additional advanced options:.

  • Ignore case: When chosen, the Text Lookup will perform a case-insensitive lookup. For instance, "ABC Company" will be treated the same as "abc company".
  • Report error on duplicates: When chosen, the Text Lookup feature will report an error when a duplicate is encountered at the time the lookup cache is populated.
  • Skip record if lookup fails: When enabled, the Text Lookup feature will skip the row when the lookup fails. Note that when this option is enabled, the skipped rows will not be sent to either the Default Output or the Error Output of the destination component.
  • Cache Strategy: You can choose from one of the two options:
    • Full Cache: When chosen, the component will populate a full cache of all records from the lookup object before the lookup. This is generally the preferred option for text lookup feature, particularly when the number of records in lookup entity is small.
    • Partial Cache: When chosen, the component will gradually build up lookup cache as the data load progresses. This is the preferred option when the number of records in lookup objects is significantly large. For instance, if you have more than a few hundreds of thousands of records in lookup entity, and you are only processing a few dozens of records for your primary object, Partial Cache mode would provide better performance in this kind of case.

Note: Prior to v4.0, if duplicates are found when populating the lookup cache, the first one will be chosen.

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen. 

Salesforce Destination Editor

There are three options available. 

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When Redirect rows to error output option is selected, rows that failed to write to Salesforce will be redirected to the "Error Output' of the Destination component. In the error output, you can see the following columns:

  • ErrorCode: Contains the error code that is reported by Salesforce.com server or the component itself.
    • Note: the ErrorCode can shift for the same error between Salesforce API versions.
  • ErrorColumn: Contains the name of the column that is causing the error. Note that this column is not always populated.
  • ErrorMessage: Contains the error message that is reported by Salesforce.com server or the component itself

Note: Use extra caution when selecting the Ignore error option, since the component will remain silent for any errors that have occurred. 

In the Error Handling page, there is also an option that can be used to enable or disable the following output fields for the destination component.

  • SalesforceRecordId: Contains the newly created Salesforce record's ID, which you can use to write to log or further process using additional data flow components.
  • IsNew: Contains value to indicate whether it is a newly created Salesforce record, or an existing one. This is useful when you use Upsert action.

Note: If you don't plan to use any of those fields for any further processing, it is generally recommended to turn them off, so you don't get any warning from SSIS by complaining that those fields are never used, and it should also provide a slightly better performance by doing so.

In addition to the above settings, the Error Handling page also offers the following an Additional Outputs option since our v6.0 release.

The "Skipped Rows" output can be useful when the Text Lookup is set up to Skip Records if Lookup Fails or the error-handling mechanism is chosen as “Ignore error” in the Error Handling page.