Create a load table (WAIT)
(Refer to previous sections for details on SERIAL and PARALLEL options as well as WAIT and BACK methods).
This example creates a load table called "Account_BulkAPIv1_Wait_Serial_Update". The Id and Error columns are mandatory. Generally, an update payload would be built up from querying or working with prior replicated data for the affected Object by using ss_Replica / ss_Delta. All load operations require the Error column, whether success or failure, to guide and inform you.
drop table if exists Account_BulkAPIv1_Wait_Serial_Update
select top 100
convert(nchar(18),Id) as Id
,convert(nvarchar(255),null) as Error
,Id as AccountNumber
,AccountNumber as AccountNumber_Orig
into Account_BulkAPIv1_Wait_Serial_Update
from Account
where AccountNumber is null
order by createddate desc
Running the example
exec ss_Loader 'BulkAPIv1Update', 'DEMO', 'Account_BulkAPIv1_Wait_Serial_Update','WAIT:SERIAL'
SQL-SALES BulkAPIv1Update run date: 2023-12-09 ------------------
09:00:16: Using Env|Schema: DEMO|dbo
09:00:16: Starting Loader for Account batchsize 10000
09:00:16: SSId added to Account_BulkAPIv1_Wait_Serial_Update
09:00:19: Connection method BULK & BULK API
09:00:19: Bulk API method WAIT:SERIAL
09:00:19: Columns checked against Salesforce metadata
09:00:19: Starting load for Account_BulkAPIv1_Wait_Serial_Update
09:00:34: JobId: 7508d00000TtQsPAAV
09:00:34: Excluded: AccountNumber_Orig is not available on object Account
09:00:34: Load complete: Success:100 Failure:0
-----------------------------------------------------------------
Note the Job Id for your submission is returned in the output for your reference, see also the log table ss_BulkAPILog.
_Return helper table
The batch to which a given row has been allocated is logged in the helper table created in the run that is your load table + “_Return”, using the above example it is:
Account_BulkAPIv1_Wait_Serial_Update_Return
As with the Update load table itself, the Error column is provided, paired with the originally submitted SSId.
ss_BulkAPILog table
The Job Id is also preserved in the ss_BulkAPILog table, written on each submission.
Checking the load table
The success or failure errors for each row will be automatically written back to the Error column as with the SOAP API Update operation.
Create an Update load table Example (BACK)
(Refer to previous sections for details on SERIAL and PARALLEL options)
This example creates a load table called "Account_BulkAPIv1_BACK_Serial_Update". The Id and Error columns are mandatory. All load operations require the Error column, whether success or failure, to guide and inform you.
exec ss_Delta 'DEMO', 'Account'
drop table if exists Account_BulkAPIv1_BACK_Serial_Update
select top 100
convert(nchar(18),Id) as Id
,convert(nvarchar(255),null) as Error
,Id as AccountNumber
,AccountNumber as AccountNumber_Orig
into Account_BulkAPIv1_BACK_Serial_Update
from Account
where AccountNumber is null
order by createddate desc
Running the example (Step 1)
exec ss_Loader 'BulkAPIv1Update', 'DEMO', 'Account_BulkAPIv1_BACK_Serial_Update','BACK:SERIAL'
SQL-SALES BulkAPIv1Update run date: 2023-12-09 ------------------
09:09:32: Using Env|Schema: DEMO|dbo
09:09:32: Starting Loader for Account batchsize 10000
09:09:32: SSId added to Account_BulkAPIv1_BACK_Serial_Update
09:09:35: Connection method BULK & BULK API
09:09:35: Bulk API method BACK:SERIAL
09:09:35: Columns checked against Salesforce metadata
09:09:35: Starting load for Account_BulkAPIv1_BACK_Serial_Update
09:09:48: JobId: 7508d00000TtR82AAF
09:09:48: BatchId: 7518d00000dBxFdAAK CreatedDate: 2023-12-09 09:09:36
09:09:48: BulkAPIv1Update BACKGROUND completed successfully
-----------------------------------------------------------------
Note the Job Id for your submission is returned in the output for your reference, see also the log table ss_BulkAPILog.
Note unlike the WAIT method, simply running BACK will not populate the _Result table nor write back Error data to your load table, see the next set of instructions for how to do this, however the Batch Id(s) are included in the output dump for information purposes
ss_BulkAPILog table
The Job Id is also preserved in the ss_BulkAPILog table, written on each submission.
Running the example (Step 2 Option 1)
At any time after you have run the initial BACK, you can retrieve processed rows to your load table by reverting back to using the WAIT method (either in SERIAL or PARALLEL mode). This is achieved by passing in the known Job Id into the @Special2 input parameter.
Note, when using “Option 1” if you have attempted to return processed rows “too soon” and some rows for a given Batch or Batches are not yet processed by Salesforce, SQL Sales will not be able to return an Error value hence caution should be exercised and for you to check your load table.
Alternatively, you can use “Option 2” to check the status of your Job by submitting a followup BACK request, alongside your known Job Id. This will instruct SQL Sales to check all related Batches and return the status of the Job Id. When the Job is Closed, no further processing will occur by Salesforce and you can now run with WAIT to return all Id and Error values.
exec ss_Loader 'BulkAPIv1Update', 'DEMO', 'Account_BulkAPIv1_BACK_Serial_Update','JOB:WAIT:SERIAL','7508d00000TtR82AAF'
SQL-SALES BulkAPIv1Update run date: 2023-12-09 ------------------
09:13:41: Using Env|Schema: DEMO|dbo
09:13:41: Starting Loader for Account batchsize 10000
09:13:41: SSId added to Account_BulkAPIv1_BACK_Serial_Update
09:13:44: Connection method BULK & BULK API
09:13:44: Bulk API method JOB:WAIT:SERIAL Job = 7508d00000TtR82AAF
09:13:44: Columns checked against Salesforce metadata
09:13:44: Starting load for Account_BulkAPIv1_BACK_Serial_Update
09:13:48: JobId: 7508d00000TtR82AAF, Job Closed
09:13:49: Excluded: AccountNumber_Orig is not available on object Account
09:13:49: Load complete: Success:100 Failure:0
-----------------------------------------------------------------
_Return helper table
The batch to which a given row has been allocated is logged in the helper table created in the run that is your load table + “_Batch”, using the above example it is:
Account_BulkAPIv1_BACK_Serial_Update_Return
As with the Update load table itself, the Error column is provided, paired with the originally submitted SSId.
Checking the load table
The success or failure errors for each row will be automatically written back to the Error column as with the SOAP API Update operation.
Running the example (Step 2 Option 2)
You can keep submitting with BACK and the known Job Id until the Status shows that the Job has Closed (this will work with either SERIAL or PARALLEL). Once the Job is Closed you can run as with Option 1.