• What Is SF_TableLoader?
• Checking the Column Names of the Input table
• How to Run SF_TableLoader
• Typical SF_TableLoader Example

What Is SF_TableLoader?

Conceptually, SF_TableLoader takes as input a local SQL Server table that it designates 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).

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

Checking the Column Names of the Input table

The Input table must contain a column named ID defined as nchar(18). SF_TableLoader does not require an Error column in the Input table. Instead, it creates the Error column in the Result table. If you add an Error column in the Input table, SF_TableLoader ignores this column and writes messages to the Error column in the Result table. In addition, the Input table can contain other columns that match the fields of the Salesforce object.

For example, below is a valid definition of an Account_Load table:

    ID nchar(18)
    Name nvarchar(80)
    AnnualRevenue decimal(18, 2)

Note that in this example, the Account_Load table does not contain most of the fields of the Accountobject.

How the Input table is used depends on the operation you request. In the above example,

• When using an INSERT operation, the missing fields are loaded as null values.
• When using an UPDATE operation, the Name and AnnualRevenue fields are only ones updated on the Salesforce side.
• When using a DELETE operation, Name and AnnualRevenue fields are ignored and the objects with the ID value are deleted.

SF_TableLoader inspects each field of the Salesforce object and tries to match it to a column name in the Input table. Note that columns of the Input table that do not match a field name are ignored. When using the BulkAPI2, SF_TableLoader does not write these columns to the Result table; instead it writes only the columns sent to Salesforce.

In addition, SF_TableLoader ignores columns that match a computed field (like SystemModstamp) if they exist in the Input table. When using the SOAP API or BulkAPI, all columns of the Input table are in the Result table.

SF_TableLoader identifies column names of the Input table that do not match valid Salesforce.com column names and produces a warning message. Note that in a properly constructed Input table you may also have other columns for your own use that should be ignored as input to SF_TableLoader.

You can use SF_Generate to generate valid local table for any Salesforce object. SF_Generate automatically creates an empty local table with all the proper columns of the Salesforce object needed for that operation. See SF_Generate for more information.

How to Run SF_TableLoader

To run SF_TableLoaderfor inserting data, use the following command in Query Analyzer. Be sure your default database is Salesforce backups.

   EXEC SF_TableLoader 'INSERT', '𝘚𝘈𝘓𝘌𝘚𝘍𝘖𝘙𝘊𝘌_𝘓𝘚', '𝘈𝘤𝘤𝘰𝘶𝘯𝘵_𝘓𝘰𝘢𝘥'

where SALESFORCE_LS is the name you gave your linked server, and Account_Load is the name of the Input table.

Typical SF_TableLoader Example

Below is a sample workflow of a typical process using SF_TableLoader. The following updates the Accountobject on Salesforce.com:

1. Create the Input table if it does not exist:

   IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
   
   WHERE TABLE_NAME = 'Account_Update' AND TABLE_SCHEMA = 'dbo')
   
   BEGIN
   
   Create Table Account_Update
   
   (
   
   ID nchar(18),
   
   Name nvarchar(255),
   
   AnnualRevenue decimal(18, 0)
   
   )
   
   END
   

2. Load the Input table with records to update Salesforce:

   INSERT INTO Account_Update (ID, Name, AnnualRevenue)
   
   SELECT ID, Name, AnnualRevenue
   
   FROM Account
   

   This step is where the records that need to uploaded to Salesforce are inserted into the Input table. We recommend avoiding SELECT INTO statements as those lock the catalog when creating the Input table.

3. Update Salesforce with the records in the Input table:

   EXEC SF_TableLoader 'Update', 'SALESFORCE', 'Account_Update'
   

   For more information on the operations that can be used and the syntax of SF_TableLoader, refer to the SF_TableLoader Reference page.

4. Check the results of the load:

   SELECT Error
   
   FROM Account_Update_Result
   
   WHERE Error <\> 'Operation Successful.'
   

   The results of the load are put into an _Result table. The name of the Result table is the name of the Input table with _Result appended. Any records that were successful have “Operation Successful” written to the Error column’; otherwise, a Salesforce error is written to the Error column.