Search

SF_TableLoader

SF_TableLoader

Usage

The SF_TableLoader stored procedure takes as input an SQL Server table that you create, which is referred to as the Input table. This table must be created in the remote database of the remote SQL Server instance. The name of the Input table must begin with a valid Salesforce object name, followed by an underscore and a suffix. For example, Account_Load and Account_FromWeb are valid Input table names, but XXX_Load is not a valid Input table name because XXX is not a valid Salesforce object.

The Input table must contain a column named ID that is defined as type NVARCHAR(18). An Error column is not required in the Input table. SF_TableLoader handles the Error column for you. The results are written to a Result table instead of back to the Input table. The Result table is named InputTableName + _Result. For example, if you have an Input table that is named Account_Load, the Result table is named Account_Load_Result.

The Input table can include additional columns that correspond to fields in the Salesforce object. SF_TableLoader generates warning messages for any columns that do not match a field in the Salesforce object. These nonmatching columns are not treated as errors because they can be included for reference purposes, but they are intentionally ignored during processing.

When you use Bulk API 2.0, the Results table includes only the columns that are sent to Salesforce; ignored columns are not included. When you use the SOAP API or Bulk API, all columns in the input table are included in the Results table.

Note: Do not allow other applications to write to the Input table while SF_TableLoader is running.

Syntax

 EXEC SF_TableLoader 'operations:options', 'linked-server', 'load-SQL-table', 'eid', 'optional-param', 'use-remote'

In this syntax:

  • The operation:options parameter specifies operations that are available to SF_TableLoader.

  • The linked-server parameter specifies the name of your linked server.

  • The load-SQL-table parameter specifies the Input table that holds the data to load into Salesforce.

  • The eid parameter specifies the external identifier (Id).

  • The optional-param parameter specifies optional parameters.

  • The use-remote parameter must be set to 1.

Examples

The following statements execute the SF_TableLoader stored procedure to update Salesforce records, with the second statement using the IgnoreNulls option to prevent null values from overwriting existing data.

 EXEC SF_TableLoader 'Update','Salesforce','MySQLTableData', '', '', 1

 EXEC SF_TableLoader 'Update:Ignorenulls','Salesforce','MySQLTableData', '', '', 1

APIs Available to SF_TableLoader

The SF_TableLoader stored procedure can use any of three different Salesforce APIs:

  • SOAP API

  • Bulk API

  • Bulk API 2.0

SF_TableLoader automatically determines which API to use, providing the best performance based on the Input table.

Differences Between the Three APIs

The SOAP API is synchronous, which means that Salesforce returns an immediate response for each batch of up to 200 rows, indicating whether those rows succeeded or failed. This behavior provides immediate feedback but limits throughput, because no more than 200 rows can be sent in a single request. As a result, an Input table that contains 1000 rows requires at least five API calls to send all data to Salesforce.

The Bulk API is asynchronous, which means that rows sent to Salesforce are processed as a queued job. The application must later check the status of the job to retrieve the results, including which rows succeeded, failed, or were not processed.

The Bulk API 2.0 is also asynchronous, and it follows the same job-based processing model as the Bulk API. The application must query the job status to obtain the results of the submitted rows. The advantage of Bulk API 2.0 is that Salesforce manages batching and concurrency automatically, which simplifies implementation and can improve performance.

SF_TableLoader Operations

The SF_TableLoader stored procedure supports these operations:

  • Insert—Reads each row of the Input table, matches the columns to the fields of the Salesforce object, and attempts to insert the new object into Salesforce.

    Note: SF_TableLoader attempts to insert or upsert all rows of the load table, regardless of any existing values in the ID and Error columns.

  • Upsert—Reads each row of the Input table, matches the columns to the fields of the Salesforce object, and attempts to upsert the new object into Salesforce using the specified external ID field.

  • Update—Reads each row of the Input table, maps the columns to the fields of the Salesforce object, and attempts to update an object in Salesforce by using the ID column of the Input table.

    Note: The Input table should only contain columns for those fields that you want to update. If the data in a column is an empty string or is null, SF_TableLoader updates that field on Salesforce.com to be null. You can modify this behavior by using the following value for the operation: Update:IgnoreNulls. The IgnoreNulls option specifies that SF_TableLoader should ignore null values in columns. However, empty string values still set the field on Salesforce.com to null.

  • Delete—Deletes Salesforce objects. The SF_TableLoader stored procedure reads each row of the Input table and uses the ‘ID’ field for the deletion.

  • HardDelete—Performs a hard delete of Salesforce objects. The SF_TableLoader stored procedure reads each row of the Input table and uses the ‘ID’ field for the hard deletion.

  • ConvertLead—Converts lead records. For details, see Converting Leads.

  • UnDelete—Restores rows from the Recycle bin.

SOAP Header Syntax

The following syntax examples demonstrate how to execute the SF_TableLoader stored procedure with optional Salesforce SOAP headers. The OptionalSoapHdr parameter enables you to pass SOAP headers for a single execution when using the SOAP API.

Example 1:

 EXEC SF_TableLoader 'Insert', 'linked-server', 'object', 'OptionalSoapHdr', '', 1

Example 2:

  EXEC SF_TableLoader 'Delete', 'linked-server', 'object', 'OptionalSoapHdr', '', 1

Example 3:

 EXEC SF_TableLoader 'Update', 'linked-server' ,'object', 'OptionalSoapHdr', '', 1

Example 4:

 EXEC SF_TableLoader 'Upsert', 'linked-server','object','eid', 'OptionalSoapHdr', 1

In these examples:

  • The linked-server parameter specifies the name of your linked server.

  • The object parameter specifies the object name.

  • The eid parameter specifies the ID field name.

  • The use-remote parameter must be set to 1.

  • (Optional) The OptionalSoapHdr passes Salesforce.com SOAP headers for the current execution only. See Using Optional SOAP Headers for more information. This parameter can be used only with the soap switch.

Example

The following example bulk inserts rows from the Account_Load table into the Account object at Salesforce.com by using the Salesforce linked server:

EXEC SF_TableLoader 'Insert', 'Salesforce', 'Account_Load', '', '', 1

Advanced Topics

Using the SF_TableLoader Switches

You can use any of three switches to force SF_TableLoader to use a specified Salesforce API:

  • soap—Forces SF_TableLoader to use the SOAP API.

  • bulkapi—Forces SF_TableLoader to use the Bulk API.

  • bulkapi2—Forces SF_TableLoader to use the Bulk API 2.

By default, if the number of rows in the Input table is less than 5,000, SF_TableLoader uses the SOAP API. If the number of rows in the Input table is greater than 5,000, it uses the Bulk API.

For example, to force SF_TableLoader to use Bulk API 2, submit a statement similar to the following:

EXEC SF_TableLoader 'Update:bulkapi2','Salesforce','User_Upd', '', '', 1

Controlling the Batch Size

When you use the soap switch, SF_TableLoader uses a default batch size of 200 rows. When you use the bulkapi switch, SF_TableLoader uses a default batch size of 10,000 rows. You might need to reduce the batch size to accommodate Apex code that runs on the Salesforce server.

To specify a different batch size, include the batchsize(n) option after the operation.

Note: The batchsize(n) option cannot be used with the bulkapi2 switch. The following example uses the soap switch with batchsize(n) to set the batch size to 50:

EXEC SF_TableLoader 'Update:soap,batchsize(50)', 'Salesforce', 'User_Upd', '', '', 1

If you also use the IgnoreNulls option, separate the options with a comma, as shown below:

EXEC SF_TableLoader 'Update:IgnoreNulls,batchsize(50)', 'Salesforce', 'User_Upd', '', '', 1

Controlling the Concurrency Mode

If you use the bulkapi switch, the default concurrency mode is parallel. To specify serial concurrency mode instead, use the serial option, as shown below:

EXEC SF_TableLoader 'Update:bulkapi,serial', 'Salesforce', 'User_Upd', '', '', 1

Note: You cannot specify the concurrency mode when you use the soap or bulkapi2 switches.

Using Optional SOAP Headers

The SOAP API allows you to pass additional SOAP headers that alter the behavior of the SF_TableLoader operation. You must provide the soap switch to use the optional SOAP Headers. The SOAP headers are described in detail in the Salesforce.com API documentation .

The headers are specified in the form of three values that are separated by commas:

  • the header name

  • the section name

  • the value for the section

The entire parameter is enclosed in quotes. If you specify multiple SOAP headers, you must separate them with a semicolon. The Salesforce.com API is case-sensitive with respect to these values; use the exact token given in the Salesforce.com documentation.

For example, to use the default assignment rule when you insert Lead records, add the SOAP header parameter 'AssignmentRuleHeader,useDefaultRule,true', as shown below:

 EXEC SF_TableLoader 'Insert:soap', 'Salesforce', 'Lead_Test', 'AssignmentRuleHeader,useDefaultRule,true', '', 1

The following examples demonstrate additional SOAP headers that you can use to control Salesforce behavior during execution.

  • Trigger auto-response emails for Lead and Case records with this SOAP header:

    'EmailHeader,triggerAutoResponseEmail,true'

  • Disable feed tracking so that changes are not recorded in feeds with this SOAP header:

    'DisableFeedTrackingHeader,disableFeedTracking,true'

Note: You can use SOAP headers only with the soap switch in SF_TableLoader.

Using the assignmentruleid Option with SF_TableLoader

The Bulk API allows you to pass an assignmentruleid option that specifies the owner of a Case or Lead. You can use the assignmentruleid( ) option only with the Bulk API and when data is pushed to either the Case or Lead objects.

Query the AssignmentRule table to obtain the assignment rule Id that should be used to specify the owner of the Case or Lead objects:

 SELECT * FROM SALESFORCE_LS.CData.Salesforce.AssignmentRule

In this statement, SALESFORCE_LS is the name of your linked server.

The assignmentruleid option is passed after the operation in the SF_TableLoader command. To specify the assignment rule Id, use the assignmentruleid( ) option after the operation, as shown in this example:

EXEC SF_TableLoader 'update:bulkapi,assignmentruleid(01Q300000001Tp5EAE)', 
  'Salesforce', 'Lead_Update', '', '', 1

Note: Only use the assignmentruleid( ) option with the Bulk API. If the Input table contains less than 5,000 rows, you must specify the bulkapi switch after the operation. Otherwise, you do not have to specify the bulkapi switch.

The Bulk API is case-sensitive with respect to the assignmentruleid( ) value. Use the exact assignment rule Id that is specified in the AssignmentRule table.

Notes

  • When individual rows in the Input table fail to complete the operation, SF_TableLoader writes the corresponding error message to the Error column in the Result table. In a batch of 200 rows, for example, some rows can succeed while others fail.

  • The SF_TableLoader stored procedure logs an error message when one or more rows fail. This message indicates that at least one row in the Result table contains an error. The procedure also logs the total number of rows that processed, along with the number of rows that succeeded, failed, or were unprocessed.

  • For all rows that process successfully, SF_TableLoader writes the phrase Operation Successful to the Error column in the Result table. You can query successfully processed rows by using the following SQL statement:

    SELECT * FROM Account_Load_Result WHERE Error LIKE '%Operation Successful%'

  • When SF_TableLoader is executed within a job step, the step fails if any rows contain an error. Rows that contain a blank error message are not processed by Salesforce, and the failure indicates that at least one row in the batch encountered an error.