Salesforce Lookup

The Salesforce Lookup processor performs lookups in a Salesforce object and passes the lookup values to fields. Use the Salesforce Lookup to enrich records with additional data.

For example, you can configure the processor to use an account_number field as the field to look up account name values in a Salesforce object, and pass the values to a new account_name output field.

When you configure the Salesforce Lookup processor, you define connection information that the processor uses to connect to Salesforce, including the Salesforce API version.

You configure the query to define the data to look up in Salesforce. If the processor uses the SOAP API or uses the Bulk API version 39.0 or later, you can configure the processor to retrieve deleted records from the Salesforce recycle bin.

and specify the output fields to write the lookup values to. You can optionally define a default value to use for fields with missing values. You can also configure the processor to locally cache the lookup values to improve performance.

The processor generates Salesforce field attributes that provide additional information about each field.

You can optionally use an HTTP proxy to connect to Salesforce.

Lookup Cache

To improve pipeline performance, you can configure the Salesforce Lookup processor to locally cache the values returned from a Salesforce object.

The processor caches values until the cache reaches the maximum size or the expiration time. When the first limit is reached, the processor evicts values from the cache.

You can configure the following ways to evict values from the cache:
Size-based eviction
Configure the maximum number of values that the processor caches. When the maximum number is reached, the processor evicts the oldest values from the cache.
Time-based eviction
Configure the amount of time that a value can remain in the cache without being written to or accessed. When the expiration time is reached, the processor evicts the value from the cache. The eviction policy determines whether the processor measures the expiration time since the last write of the value or since the last access of the value.
For example, you set the eviction policy to expire after the last access and set the expiration time to 60 seconds. After the processor does not access a value for 60 seconds, the processor evicts the value from the cache.

When you stop the pipeline, the processor clears the cache.

Salesforce Attributes

The Salesforce Lookup processor generates Salesforce field attributes that provide additional information about each field. The origin receives these details from Salesforce.

Salesforce attributes include a user-defined prefix to differentiate the Salesforce attributes from other attributes. By default, the prefix is "salesforce.". You can change the prefix that the processor uses and you can configure the processor not to create Salesforce attributes.

You can use the record:fieldAttribute or record:fieldAttributeOrDefault functions to access the information in the attribute.

The Salesforce Lookup processor can provide the following Salesforce field attributes:

Salesforce Field Attribute Description
<Salesforce prefix>salesforceType Provides the original Salesforce data type for the field.
<Salesforce prefix>length Provides the original length for all string and textarea fields.
<Salesforce prefix>precision Provides the original precision for all double fields.
<Salesforce prefix>scale Provides the original scale for all double fields.
<Salesforce prefix>digits Provides the maximum number of digits for all integer fields.

For more information about field attributes, see Field Attributes.

Changing the API Version

Data Collector ships with version 39.0 of the Salesforce Web Services Connector libraries. You can use a different Salesforce API version if you need to access functionality not present in version 39.0.

  1. On the Salesforce tab, set the API Version property to the version that you want to use, for example 36.0.
  2. Download the relevant version of the following JAR files from Salesforce Web Services Connector (WSC):
    • WSC JAR file - force-wsc-<version>.0.0.jar

    • Partner API JAR file - force-partner-api-<version>.0.0.jar

    Where <version> is the API version number, for example, 36.

    For information about downloading libraries from Salesforce WSC, see https://developer.salesforce.com/page/Introduction_to_the_Force.com_Web_Services_Connector.

  3. In the following Data Collector directory, replace the default force-wsc-39.0.0.jar and force-partner-api-39.0.0.jar files with the versioned JAR files that you downloaded:
    $SDC_DIST/streamsets-libs/streamsets-datacollector-salesforce-lib/lib/
  4. Restart Data Collector for the changes to take effect.

Configuring a Salesforce Lookup

Configure a Salesforce Lookup processor to perform lookups in a Salesforce object.

  1. In the Properties panel, on the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Required Fields Fields that must include data for the record to be passed into the stage.
    Tip: You might include fields that the stage uses.

    Records that do not include all required fields are processed based on the error handling configured for the pipeline.

    Preconditions Conditions that must evaluate to TRUE to allow a record to enter the stage for processing. Click Add to create additional preconditions.

    Records that do not meet all preconditions are processed based on the error handling configured for the stage.

    On Record Error Error record handling for the stage:
    • Discard - Discards the record.
    • Send to Error - Sends the record to the pipeline for error handling.
    • Stop Pipeline - Stops the pipeline. Not valid for cluster pipelines.
  2. On the Salesforce tab, configure the following properties:
    Salesforce Property Description
    Username Salesforce username in the following email format: <text>@<text>.com.
    Password Salesforce password.
    Tip: To secure sensitive information such as usernames and passwords, you can use runtime resources or Hashicorp Vault secrets. For more information, see Using Runtime Resources or Accessing Hashicorp Vault Secrets.
    Auth Endpoint Salesforce SOAP API authentication endpoint. Enter one of the following values:
    • login.salesforce.com - Use to connect to a Production or Developer Edition organization.
    • test.salesforce.com - Use to connect to a sandbox organization.

    Default is login.salesforce.com.

    API Version Salesforce API version to use to connect to Salesforce.

    Default is 39.0. If you change the version, you also must download the relevant JAR files from Salesforce Web Services Connector (WSC).

    SOQL Query SOQL query to use to look up data in Salesforce. Use the following syntax for the query:
    SELECT <field1 name>, <field2 name> FROM <object name> WHERE <field3 name> <operator> <expression>
    For example, to use the account number field in the record to look up the account name field in the Salesforce Account object, use the following query:
    SELECT Name FROM Account WHERE AccountNumber = '${record:value('/account_number')}'

    If you specify SELECT * FROM <object> in the SOQL query, the processor expands * to all fields in the Salesforce object that are accessible to the configured user. Note that the processor adds components of compound fields to the query, rather than adding the compound fields themselves. For example, the origin adds BillingStreet, BillingCity, etc., rather than adding BillingAddress. Similarly, it adds Location__Latitude__s and Location__Longitude__s rather than Location__c.

    Include Deleted Records Determines whether the SOQL query also retrieves deleted records from the Salesforce recycle bin.

    The query can retrieve deleted records when the stage uses the Salesforce SOAP API or the Bulk API version 39.0 or later. Earlier versions of the Bulk API do not support retrieving deleted records.

    Field Mappings Use to override the default field mappings. By default, Salesforce fields are written to Data Collector fields of the same name.
    Enter the following:
    • Salesforce Field - Name of the Salesforce field that contains the lookup value. Enter a field name or enter an expression that defines the field name.
    • SDC Field - Name of the field in the record that receives the lookup value. You can specify an existing field or a new field. If the field does not exist, Salesforce Lookup creates the field.
    • Default Value - Optional default value to use when the query does not return a value for the field. If the query returns no value and this property is not defined, the processor sends the record to error.
    • Data Type - Data type to use for SDC Field. Required when you specify a default value. The processor uses the Salesforce data type by default.

    Use the Add icon to create additional field mappings.

    Enable Local Caching Specifies whether to locally cache the returned values.
    Maximum Entries to Cache Maximum number of values to cache. When the maximum number is reached, the processor evicts the oldest values from the cache.

    Default is -1, which means unlimited.

    Eviction Policy Type Policy used to evict values from the local cache when the expiration time has passed:
    • Expire After Last Access - Measures the expiration time since the value was last accessed by a read or a write.
    • Expire After Last Write - Measures the expiration time since the value was created, or since the value was last replaced.
    Expiration Time Amount of time that a value can remain in the local cache without being accessed or written to.

    Default is 1 second.

    Time Unit Unit of time for the expiration time.

    Default is seconds.

  3. On the Advanced tab, configure the following properties:
    Advanced Property Description
    Create Salesforce Attributes Adds Salesforce field attributes to fields. The origin creates Salesforce attributes by default.
    Salesforce Attribute Prefix Prefix for Salesforce attributes.
    Use Proxy Specifies whether to use an HTTP proxy to connect to Salesforce.
    Proxy Hostname Proxy host.
    Proxy Port Proxy port.
    Proxy Requires Credentials Specifies whether the proxy requires a user name and password.
    Proxy Username User name for proxy credentials.
    Proxy Password Password for proxy credentials.
    Tip: To secure sensitive information such as usernames and passwords, you can use runtime resources or Hashicorp Vault secrets. For more information, see Using Runtime Resources or Accessing Hashicorp Vault Secrets.