Search

SF_BulkOps

SF_BulkOps

Usage

SF_BulkOps takes as input a local SQL Server table you create that is designated as the input table. The Input table name must begin with a valid Salesforce object name followed by an underscore and suffix. For example, Account_Load and Account_FromWeb are valid Input table names. XXX_Load is not a valid Input table name (XXX is not a valid Salesforce.com object).

The input table must contain a column named ID defined as nvarchar(18) and a column named Error defined as nvarchar(255). In addition, the Input table can contain other columns that match the fields of the Salesforce object. SF_BulkOps produces warning messages for all columns that do not match a field in the Salesforce.com object. Non-matching columns are not considered an error because you may want to have column data in the table for reference but that should be intentionally ignored.

Do not allow other applications to write to the Input table while SF_BulkOps is running.

NOTE: There are two APIs available from Salesforce.com that applications can use to push data: the Web Services API or the Bulk API. You can use either API with SF_BulkOps with the Web Services API being the default.

The Web Services API is synchronous, meaning that for every 200 rows that are sent to Salesforce, an immediate response is sent indicating the success or failure of those 200 rows. SF_BulkOps has traditionally used the Web Services API. The disadvantage of this API is that the maximum number of rows that can be sent to Salesforce at a time is 200. So if the Input table to SF_BulkOps contains 1000 rows, there will be at least five API calls to send the data to the Salesforce.com server.

The Bulk API is asynchronous, meaning that rows sent to Salesforce.com are queued as a job. The job is executed at some time in the future. The advantage of the Bulk API is that up to 10,000 rows can be sent in a single request or API call. An Input table of 5000 rows would require a single API call to send the data, along with API calls to retrieve the status at some point in the future.

Notes

  • You can use the SF_Generate stored procedure to quickly build Input tables for SF_BulkOps.

  • SF_BulkOps automatically determines which API to use, providing the best performance based on the size of the Input table. An Input table that contains less than 5,000 rows uses the SOAP API; an Input table of 5,000 rows or more uses the Bulk API.

SF_BulkOps Operations

SF_BulkOps can perform 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.

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

  • Upsert—reads each row of theInput 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.

  • Delete—reads each row of the Input table and uses the ID field to delete an object in Salesforce.

  • HardDelete:BulkAPI—deletes objects in Salesforce using the Bulk API. In addition, the deleted records are not stored in the Recycle Bin.

  • ConvertLead—converts Lead records.

  • UnDelete—undeletes rows from the Recycle bin. You can identify deleted rows using a query against the _QueryAll table:

       SELECT ID FROM Salesforce**…Account_QueryAll WHERE IsDeleted='True'

  • IgnoreFailures—specifies the percent of records in a BulkOps Input table to allow to fail, without failing the BulkOps job.

For each row in the Input table for which the operation fails, the Errorcolumn contains the error message.

Syntax

 EXEC SF_BulkOps 'Insert','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','OptionalSoapHdr'

Or

 EXEC SF_BulkOps 'Delete','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','OptionalSoapHdr'

Or

 EXEC SF_BulkOps 'Update:BulkAPI','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','OptionalSoapHdr'

Or

 EXEC SF_BulkOps 'Upsert','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','eid', ,'OptionalSoapHdr'

where linked_server is the name of your linked server, object is the name of the object, and eid is the name of the external ID field.

The OptionalSoapHdr parameter can be used to pass Salesforce SOAP headers for this execution only. See Using Optional SOAP Headers.

Example

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

 EXEC SF_bulkops 'Insert','Salesforce','Account_Load'

Controlling the batch size

SF_BulkOps uses a batch size of 200 rows (Web Services API) or 10,000 (Bulk API). You may need to reduce the batch size to accommodate APEX code on the Salesforce.com server. To specify a different batch size, use the batchsize(xx) option after the operation.

For example, to set the batch size to 50:

 EXEC SF_Bulkops 'Update:batchsize(50)','Salesforce','User_Upd'

If you also use the IgnoreNulls option, then separate options with a comma:

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

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:

 EXEC SF_Bulkops 'Update:bulkapi,serial,'Salesforce','User_Upd'

Using Optional SOAP Headers

The Salesforce API allows you to pass additional SOAP Headers that alter the behavior of the Sf_Bulkops operation. The SOAP Headers are described in detail in the Salesforce.com API documentation: http://www.Salesforce.com/us/developer/docs/api/Content/soap_headers.htm

The headers are specified as three values separated by commas. The first value is the header name, the next value is the section name and the last value is the value for the section. The entire parameter is enclosed in quotes. 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 for these inserted Leads you would add the following SOAP Header parameter:

 EXEC SF_bulkops
 'Insert','Salesforce','Lead_Test','AssignmentRuleHeader,useDefaultRule,true'

Examples of SOAP Headers

Trigger auto-response rules for leads and cases:

 'EmailHeader,triggerAutoResponseEmail,true'

Changes made are not tracked in feeds:

 'DisableFeedTrackingHeader,disableFeedTracking,true'

SOAP Headers cannot be used with the bulkapi switch.

Using IgnoreFailures Option

Used to specify the percent of records in the Input table to allow to fail, without failing the BulkOps job. Use the following command to allow up to 20 percent of the records in the Opportunity_Load to fail without the BulkOps job failing:

 EXEC SF_BulkOps 'Insert:IgnoreFailures(20)', 'Salesforce', 'Opportunity_Load'

Note that you can use the IgnoreFailures option with the SF_BulkOps BulkAPI switch.

Notes

When individual rows of the Input table fail to complete the operation, SF_BulkOps writes the error message back to the Error column of that row and continues processing the next row. Thus, in a batch of 200 rows it is possible that 175 rows were successful and 25 rows failed.

The SF_BulkOps stored procedure outputs an error message in the log indicating SF_BulkOps failed when one or more rows failed. The correct interpretation of this error message is that at least one row of the Input table contained an error. In addition, SF_BulkOps outputs messages indicating the total number of rows processed the number of rows that failed and the number of rows that succeeded.

For all rows that were successfully processed, SF_BulkOps writes ‘Operation Successful” to the Errorcolumn. You can successfully select processed rows using the followingT:

 SELECT * FROM Account_Load WHERE Error like '%Operation Successful%'

This technique works for the bulkapi switch as well.

If you run SF_BulkOps in a job step, the job step fails if one or more rows contain an error. The rows that contain a blank error message were still successful; the failure is thrown to indicate to the operator that at least one row failed.

NOTE: Consider using SF_TableLoader instead of SF_BulkOps. SF_TableLoader performs much better.