Writing Back to Salesforce.com Using ETL

You can write directly to Salesforce.com objects using the ETL script command UPDATESFDC. Developers can modify all objects, including custom objects and custom fields that are allowed to be updated via the Force.com web services API. What can be modified is subject to the permissions associated with the credentials used to log into Salesforce.com via Birst.

Using UPDATESFDC

UPDATESFDC takes one argument, a map. The map object must have the following key/value pairs:

sfdc:id: The SFDC ID for the object, for example, Account.Account ID

sfdc:type: The technical name of the Salesforce object

sfcd:ConnectionName: The name of the Birst connection. The default value is "Salesforce". See Upgrading Spaces to Support Multiple Instances of Connectors.

The other key/value pairs in the map are the fields in the object to update. Any key that starts with sfdc: is ignored. UPDATESFDC cannot be used to create or delete objects, only to update existing objects.

For example, let's say you imported the Account object from Salesforce.com. Using the Birst ETL script below, you can write back to the Account object and modify the newly created column named Rating.

Important: The Salesforce.com column to be updated must already exist before you can modify it using Birst. The data type of the Salesforce.com column and the value being passed must also match.

Important Write the select statement in such a way that "sfdc:id" is unique in the result. Updating multiple fields for the same row is supported. Refer to the aforementioned example where two fields - Ownership and Industry - are being updated.

Example Select Statement:

SELECT [Account.Account ID], [Account.Account Name] FROM [Account]

Example ETL for a scripted source:

DIM [UpdateAccount] AS MAP(VARCHAR(18),NONE)

[Account ID]=[Account.Account ID]

[Account Name]=[Account.Account Name]

[Industry]=[Account.Industry]

[Ownership]=[Account.Ownership]

[Account Rating]=[Account.Account Rating]

[UpdateAccount]('sfdc:id') = [Account.Account ID]

[UpdateAccount]('sfdc:type') = 'Account'

[UpdateAccount]('sfdc:ConnectionName') = 'SFDCConn'

[UpdateAccount]('Ownership') = 'Private'

[UpdateAccount]('Industry') = 'Transportation'

UPDATESFDC [UpdateAccount]

WRITERECORD

Batch Size

Birst updates the appropriate Salesforce.com columns using batch updates. By default there are 200 updates per batch.

You can use the script parameter BatchSize to change the default. For example:

[UpdateAccount]('sfdc:BatchSize') = '50'

The batch size can be between 1 and 200.

Important: If BatchSize is set to an invalid value, such as a negative, non-integer, or >200 number, then the batch size will default to 200.