SQL Sales has a powerful yet simple to use feature supporting the following data operations, designed to make working with Salesforce very straightforward for those familiar and comfortable using SQL Server. ss_loader works with the standard web services SOAP API, including loading Files & Notes. It also supports both Version 1 and Version 2 of the Bulk API. These Salesforce APIs do not support all data operations, the supported set, per API are as below:
SOAP or REST API | BULK API v1 | BULK API v2 |
Insert | BulkAPIv1Insert | BulkAPIv2Insert |
Update | BulkAPIv1Update | BulkAPIv2Update |
Delete | BulkAPIv1Delete | BulkAPIv2Delete |
Undelete | n/a | n/a |
Upsert | BulkAPIv1Upsert | BulkAPIv2Upsert |
n/a | BulkAPIv1Harddelete | BulkAPIv2Harddelete |
Note, The Salesforce REST API does not natively support Undelete, however the SQL-Sales Managed Package solution (when available) will provides a custom api to achieve the Undelete operation.
Provide your data in a SQL table and point to Salesforce via a configured Environment, choosing one of the supported operations. Success is logged in the required Error field, or any failures passed back via the Salesforce api are also provided in the Error field.
In the case of Inserts, the newly created Id is helpfully populated into the Id column. Similarly in the case of Upsert-Inserts – i.e. if no match is found via the specified External Id and an Insert occurs, the new Id will also be provided in the Id column.
Note, Insert and Update operations writing back Id and Error values to the Load table are fully supported in the SOAP API. This is similarly supported in Version 1 of the Bulk API via the WAIT method. It is not possible with Version 2 of the Bulk API, although alternative support is available via the _Return table.
All other operations (Update, Delete, Undelete) for the SOAP API and Update, Delete, Harddelete for both versions of the Bulk API fully support writing back to the Error column
Parameter | Purpose |
@Operation | Operation: Insert, Update, Upsert, Delete, Undelete, BulkAPIv1Insert, BulkAPIv1Update, BulkAPIv1Delete, BulkAPIv1Upsert, BulkAPIv1Harddelete, BulkAPIv2Insert, BulkAPIv2Update, BulkAPIv2Delete, BulkAPIv2Upsert, BulkAPIv2Harddelete
Define a custom batchsize (for example 25) in this form: Insert(25), Update(25), Upsert(25), Delete(25), Undelete(25)
See Upsert section for special options concerning the External Id |
@Env | SQL Sales Environment Name |
@TableName | SQL table you have defined and created. There are certain rules to be adhered to in the structure (mainly that an Id is present, defined as nchar(18) and an Error column, defined as nvarchar(255).
The name of the table is significant as the left section of the name up to the first underscore should exactly match the Salesforce object you are performing the load against.
For example table "Account_TestLoad_Insert" will be recognised by SQL Sales as being a payload for the "Account" object. Similarly "TestObject__c_TestLoad_Update will be recognised by SQL Sales as being a payload for the "TestObject__c" object.
Only alphanumeric and underscore characters are supported in the table name. |
@Special1 | Optional, in simple usage, null
Options For Bulk API V1 operations: WAIT:SERIAL WAIT:PARALLEL BACK:SERIAL BACK:PARALLEL JOB:WAIT:SERIAL JOB:WAIT:PARALLEL JOB:BACK:SERIAL JOB:BACK:PARALLEL
For Bulk API V2 operations: WAIT BACK JOB:WAIT JOB:BACK |
@Special2 | Used to receive a (known) Job Id for when @Special1 is one of:
For when Bulk API V1 operations was specified as one of: JOB:WAIT:SERIAL JOB:WAIT:PARALLEL JOB:BACK:SERIAL JOB:BACK:PARALLEL
Or for when Bulk API V2 operations was specified as one of: JOB:WAIT JOB:BACK |
This is a summary of what you can expect with regards how the Load table is supported in relation to which Method you use, which Bulk API Version and which Operation.
Method | Writes to Load table | Writes to Return table |
WAIT:SERIAL (Version 1) | Yes for all operations | Yes for all operations |
WAIT:PARALLEL (Version 1) | Yes for all operations | Yes for all operations |
BACK:SERIAL (Version 1) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) |
BACK:PARALLEL (Version 1) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) |
JOB:WAIT:SERIAL (Version 1) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations |
JOB:WAIT:PARALLEL (Version 1) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations |
JOB:BACK:SERIAL (Version 1) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations |
JOB:BACK:PARALLEL (Version 1) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations |
WAIT (Version 2) | Yes for Update, Delete, Harddelete No for Insert, Upsert | Yes for all operations |
BACK (Version 2) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations |
JOB:WAIT (Version 2) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations |
JOB:BACK (Version 2) | Yes for Update, Delete, Harddelete (once the JOB-BACK run has completed in Salesforce and you’ve followed up with a WAIT) No for Insert, Upsert | Yes for all operations |
This is a summary of what you can expect with regards the write-back to the Error column and the newly created Ids for Insert & Upsert.
Operation | SOAP or REST API | Bulk API V1 | Bulk API V2 |
Insert BulkAPIv1Insert BulkAPIv2Insert | Yes | Yes (via WAIT) | No (but does write to _Return table) |
Update BulkAPIv1Update BulkAPIv2Update | Yes | Yes | Yes |
Delete BulkAPIv1Delete BulkAPIv2Delete | Yes | Yes | Yes |
Undelete | Yes | n/a | n/a |
Upsert BulkAPIv1Upsert BulkAPIv2Upsert | Yes | Yes (via WAIT) | No (but does write to _Return table) |
BulkAPIv1Harddelete BulkAPIv2Harddelete | n/a | Yes | Yes |
SSId
By default, SQL Sales will add a primary key of SSId, defined as an integer identity(1,1). This key is how SQL Sales processes the data at the back end and maintains data integrity as it performs the various loads. You can provide you own SSId field if that works better for your use case, however it must be defined as an integer identity(1,1). If it is not, the following error message will be thrown and the process will terminate:
SQL-SALES insert run date: 2023-11-04 ---------------------------
20:15:03: Using Env|Schema: DEMO|dbo
20:15:03: Provided Load Table: Account_TestLoad_Insert (for schema: dbo) contains an integer field called SSId which is not defined as an identity column
-----------------------------------------------------------------
Metadata checks
It is quite normal to want or need to have additional columns in your load table, that assist in what you're trying to achieve. SQL Sales will perform a validation of each column against the available columns for the given load object, if the column either doesn't exist or can't be operated against due to the User permissions of the Username defined in the Environment configuration, then SQL Sales will simply ignore that column in the given data operation (and report back that it has been ignored).
In the example below, the Column "HelpderData" has been included in the build of the load table:
drop table if exists Account_TestLoad_Insert
create table Account_TestLoad_Insert
(Id nchar(18)
,Error nvarchar(255)
,Name nvarchar(255)
,HelperData nvarchar(255))
insert Account_TestLoad_Insert
(Name
,HelperData)
select 'Test1 for SQL-Sales','Info1'
union select 'Test2 for SQL-Sales','Info2'
The Insert will run as normal, but note the exclusion information message on the output report:
SQL-SALES insert run date: 2023-11-04 ---------------------------
20:17:59: Using Env|Schema: DEMO|dbo
20:17:59: Starting Loader for Account batchsize 200
20:17:59: SSId added to Account_TestLoad_Insert
20:18:02: Connection method BULK & SOAP API
20:18:02: Columns checked against Salesforce metadata
20:18:04: Excluded: HelperData is not available on object Account
20:18:04: Load complete: Success:2 Failure:0
-----------------------------------------------------------------
Batchsize
Note by default, with the SOAP API, without specifying a batchsize, 200 is used, the maximum for the SOAP api.
Customise this by passing in the operation in the format:
<operation>(<batchsize>)
for example: Insert(25) - repeating the example:
exec ss_Loader 'Update(25)','DEMO','Account_TestLoad_Update'
As you’ll discover in the later BULK API sections, you can also specify a custom batchsize (from default 10000) with the Version 1 Bulk API, for example:
exec ss_Loader 'BulkAPIv1Update(5000)','DEMO','Account_TestLoad_Update',’WAIT:SERIAL’
The Version 2 Bulk API does not support batchsize (in practical terms this means Salesforce will generally apply a batchsize of 10000).