Loading Data

Overview

Data loading is supported via SOAP, Bulk v1 and Bulk v2 APIs. A unified interface is provided by the procedure SLAM_load which requires an @api parameter. Alternatively, users can directly call the various SLAM_load_<api_name>. All operations are logged to SLAM_Activity_Log

General Requirements for Loading Data

The input table should have an Id column defined as an NCHAR(18) or NVARCHAR(18) to receive the Salesforce Id returned by Salesforce.

important

When loading data to Salesforce, if the SLAM_Message column exists, Speediful ignores any records in the input table where SLAM_Message = 'Operation Successful.'. This allows the same input table to be re-run after a partial failure as it will simply ignore any rows that were previously successful. This approach makes it simple for users to correct data issues in-situ without risk of creating duplicate records, while also keeping Salesforce data processing volumes efficient

Effect of Load Operations

Every data load operation results in the following columns being added to the input table:

• SLAM_Message - the end result of the operation: 'Operation Successful.' or an error message return by Salesforce
• SLAM_unique_id (as needed) - a generated unique identifier if there is no available key column in the input table

The two SLAM_* fields are not loaded into the Salesforce object, but as responses are received from Salesforce, the SLAM_* fields are populated and the Id column is written with the Salesforce Id from the response.

Field Mapping

Fields are automatically mapped to Salesforce when the SQL column name matches an eligible field API name. Eligible fields will depend on the operation being performed (insert, update, delete, etc). Fields that exist in the input table but do not exist in the Salesforce target will be ignored and logged to the program's output without raising an error.

A common problem is that Salesforce fields do not get populated as expected even if they exist in the input table. This is usually caused by incomplete field level security configuration, where the user performing the data load does not have edit access to the fields that need to be populated.

Dual Relationship Syntax Support

Two relationship naming styles are supported for upsert operations:

• RelationshipName.Field (Salesforce standard style)
  • The RelationshipName is defined within the Salesforce API and can be found in the [part.RelationshipName] column of the sObject_fields_* table
  • Examples: Owner.Username and Contact_Lookup__r.External_Id__c
• FieldApiName.Field (DBAmp compatibility style)
  • This naming style is supported as a developer aid/convenience and is automatically translated into the RelationshipName style supported by Salesforce
  • Examples: OwnerId.Username and Contact_Lookup__c.External_Id__c

Client-side Disconnections

SOAP operations require a continuous, uninterrupted connection with the Salesforce server while data is transferred in batches. Disconnections for any reason (server crash, network interruption, etc) can result in an inconsistent state between the database and Salesforce for the in-progress batch. A manual true-up of data would be required in this scenario.

Salesforce has designed the Bulk APIs to be asynchronous, which means that once the dataset has been transferred to Salesforce, an ongoing connection with Salesforce is not required. If a disconnection occurs after the data has been transferred to Salesforce, the result of the completed Salesforce processing can be retrieved using SLAM_download_job

Dry Run

Use @dry_run = 1 to preview how Speediful will map fields between your SQL table and the Salesforce object without sending any data to Salesforce. This is useful for verifying field mapping before committing to a load operation, especially when working with a new object or table for the first time.

When dry run is enabled, Speediful will:

1. Connect to Salesforce and retrieve the object metadata
2. Map the SQL columns to Salesforce fields
3. Print a field mapping report
4. Exit without loading any data

The field mapping report organizes fields into the following categories:

Category	Description
Unmapped (FLS Issue)	SQL columns that match a Salesforce field but cannot be written due to field-level security restrictions on the connected user
Ignored (No matching Salesforce custom field)	SQL columns whose naming pattern suggests they are Salesforce custom fields (e.g. ending in __c) but do not match any custom field on the target object. This may indicate a typo, a field that has been removed, or a field from a different object
Ignored	SQL columns that do not match any eligible Salesforce field for this operation (e.g. formula fields, system fields, or columns not on the object)
Mapped	SQL columns that are successfully mapped to writable Salesforce fields. These fields will be included in the load
Unmapped	Salesforce fields that exist on the object but are not present in your SQL table

EXEC dbo.SLAM_load
    @sObject = 'Account',
    @table = 'Account_insert',
    @api = 'bulk',
    @operation = 'insert',
    @dry_run = 1

tip

Use dry run before your first load to a new object to verify that field-level security is correctly configured and that your SQL column names match the Salesforce API field names.

A brief overview of the APIs

• Bulk v1 API provides the highest level of control over large data loads, including batch sizes and serial vs parallel processing. Bulk v1 supports hardDelete. Note that Salesforce specifically has not validated Bulk v1 for use with custom address fields and therefore you should look to Bulk v2 in those situations

• Bulk v2 API provides a simplified approach to large data loads, allowing Salesforce to take a determining role in the orchestration of the job. Bulk v2 supports hardDelete

• SOAP API is suited to smaller volumes of records and provides additional controls via header options (see SOAP Headers section). SOAP is the only API to support undelete