SF_TableLoader
SF_TableLoader
Usage
SF_TableLoader takes as input a SQL Server table you create called the Input table. The Input table must be created in the remote database of the remote SQL Server Instance. 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). 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 input_table_name + _Result. For example, if the Input table was named Account_Load, the Result table is named Account_Load_Result.
The Input table can contain other columns that match the fields of the Salesforce object. SF_TableLoader produces warning messages for columns that do not match a field in the Salesforce object. Non-matching columns are not considered an error because you may want to have column data in the table for reference, but the non-matching tables are to be intentionally ignored. When using BulkAPI2, the Result table only contains the columns sent to Salesforce; ignored columns are not included. When using the SOAP API or BulkAPI, all columns in the Input table are included in the Result table.
NOTE: Do not allow other applications to write to the Input table while SF_TableLoader is running.
Syntax
EXEC SF_TableLoader 𝘰𝘱𝘦𝘳𝘢𝘵𝘪𝘰𝘯[:𝘰𝘱𝘵𝘪𝘰𝘯𝘴], 𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳, load_SQL_Table, [𝘦𝘪𝘥], [optional_param], [use_remote]
where • operation[:options]: operations available to SF_TableLoader • linked_server: the linked server name • load_SQL_Table: the Input table that holds the data to load into Salesforce • [eid]: the external ID • [Optional_param]: optional parameters • [use_remote]: must be set to 1
Basic Examples
EXEC SF_TableLoader 'Update','Salesforce','MySQLTableData', '', '', 1
EXEC SF_TableLoader 'Update:Ignorenulls','Salesforce','MySQLTableData', '', '', 1
APIs Available to SF_TableLoader
There are three different API’s available from Salesforce.com that SF_TableLoader can use:
• SOAP API • BulkAPI • BulkAPI2 API
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, 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. The disadvantage of this API is that the maximum number of rows that can be sent to Salesforce at a time is 200. So, an Input table to SF_TableLoadercontaining 1000 rows leads to at least five API calls to send the data to the Salesforce.com server.
The BulkAPI is asynchronous, meaning that rows sent to Salesforce.com are queued as a job. The application must inquire about the status of the job later to retrieve the success, failure, or unprocessed results of the rows sent.
The BulkAPI2 API is asynchronous, meaning that rows sent to Salesforce.com are queued as a job. The application must inquire about the status of the job later to retrieve the success, failure, or unprocessed results of the rows sent. The advantage of the BulkAPI2 API is that Salesforce handles the batching and concurrency for you.
SF_TableLoader Operations
SF_TableLoader 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/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 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 NULL, SF_TableLoader updates that field on Salesforce.com to be NULL. You may modify this behavior by using the following value for the operation: Update:IgnoreNulls. The IgnoreNulls option tells SF_TableLoader to ignore null values in columns. However, empty string values still set the field on Salesforce.com to NULL.
• Delete: delete Salesforce objects. SF_TableLoader reads each row of the Input table and uses the ID field for the deletion. • HardDelete: hard delete Salesforce objects. SF_TableLoader reads each row of the Input table and uses the ID field for the hard deletion. • ConvertLead: converts Lead records. See Converting Leads with SF_TableLoader for more details. • UnDelete: undelete rows from the Recycle bin.
SOAP Header Syntax
EXEC SF_TableLoader 'Insert','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','OptionalSoapHdr, '', 1
or
EXEC SF_TableLoader 'Delete','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','OptionalSoapHdr', '', 1
or
EXEC SF_TableLoader 'Update','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','OptionalSoapHdr', '', 1
or
EXEC SF_TableLoader 'Upsert','𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object','eid','OptionalSoapHdr', 1
where linked_server is your linked server name, object is the object name, and eid is the external ID field name. The use_remote parameter must be set to 1.
The OptionalSoapHdr parameter is optional. You can use it to pass Salesforce.com SOAP headers for this execution only. See “Using Optional SOAP Headers” for more information. Note that this parameter can only be used with the soap switch.
Example
The following example bulk inserts rows from the table named Account_Load into the Account object at Salesforce.com using the Salesforce linked server:
EXEC SF_TableLoader 'Insert','Salesforce','Account_Load', '', '', 1
Advanced Topics
Using the SF_TableLoader Switches
There are three switches that can be used 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 BulkAPI. • bulkapi2—forces SF_TableLoader to use the BulkAPI2.
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 BulkAPI.
For example, to force SF_TableLoader to use the BulkAPI2 API:
EXEC SF_TableLoader 'Update:bulkapi2','Salesforce','User_Upd', '', '', 1
Controlling the Batch Size
SF_TableLoader with the soap switch uses a default batch size of 200 rows. When using the BulkAPI, SF_TableLoader uses a default batch size of 10,000. 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.
Note that you cannot use the batchsize option when using the bulkapi2 switch. For example, 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, then separate the options with a comma:
EXEC SF_TableLoader 'Update:IgnoreNulls,batchsize(50)','Salesforce','User_Upd', '', '', 1
Controlling the Concurrency Mode
If you are using the bulkapi switch, the default concurrency mode is parallel. To specify serial concurrency mode instead, use the serial option:
EXEC SF_TableLoader 'Update:bulkapi,serial,'Salesforce','User_Upd', '', '', 1
Note that 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_TableLoaderoperation. 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:
http://www.Salesforce.com/us/developer/docs/api/Content/soap_headers.htm
The headers are specified in the form of three values separated by commas:
• the header name • the section name • the value for the section
The entire parameter is enclosed in quotes. If you are specifying multiple SOAP Headers, 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 for these inserted Leads, add the following SOAP Header parameter:
EXEC SF_TableLoader 'Insert:soap','Salesforce','Lead_Test','AssignmentRuleHeader,useDefaultRule,true', '', 1
More 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’ Note that SOAP Headers can only be used with the soap switch of SF_TableLoader.
Using the AssignmentRuleID Option with SF_TableLoader
The BulkAPI allows you to pass an AssignmentRuleID that specifies who the owner of a Case or Lead is. You can only use the AssignmentRuleID option with the BulkAPI and if the data is being pushed to either the Case or Lead objects.
Query the AssignmentRule table to obtain the AssignmentRuleID that is to be used to specify the owner of the Case or Lead objects:
SELECT * FROM SALESFORCE_LS.CData.Salesforce.AssignmentRule
where SALESFORCE_LS is the name of your linked server.
The AssignmentRuleID option is passed after the operation in the SF_TableLoader command. To specify an AssignmentRuleID, use the assignmentruleid(𝘹𝘹) option after the operation:
EXEC SF_TableLoader 'update:bulkapi,assignmentruleid(01Q300000001Tp5EAE)',
'Salesforce', 'Lead_Update', '', '', 1
NOTE: only use the AssignmentRuleID option with the BulkAPI. If the Input table contains less than 5,000 rows, you must specify the bulkapi option after the operation. Otherwise, you do not have to specify the bulkapi option.
The BulkAPI is case-sensitive with respect to the AssignmentRuleID value; use the exact AssignmentRuleID given in the AssignmentRule table.
Notes
When individual rows of the Input table fail to complete the operation, SF_TableLoader writes the error message back to the Errorcolumn in the Result table. Thus, in a batch of 200 rows it is possible that 175 rows were successful and 25 rows failed.
The SF_TableLoader stored procedure outputs an error message in the log indicating that it failed when one or more rows failed. The correct interpretation of this error message is that at least one row of the Result table contained an error. In addition, SF_TableLoader outputs messages indicating the total number of rows processed, the number of rows that failed, the number of rows that succeeded, and the number of rows unprocessed.
For all rows that were successfully processed, SF_TableLoader writes the phrase “Operation Successful” to the Errorcolumn in the Result table. You can select successfully processed rows using the following SQL SELECT statement:
SELECT * FROM Account_Load_Result WHERE Error like '%Operation Successful%'
If you run SF_TableLoader in a job step, it fails if any rows contain an error. The rows that contain a blank error message were unprocessed by Salesforce; the error is thrown to indicate that at least one row failed.