Create a load table Example (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_Delete". The Id and Error columns are mandatory. Generally, a delete 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.
exec ss_Delta 'DEMO', 'Account'
drop table if exists Account_BulkAPIv1_Wait_Serial_Delete
select top 100
convert(nchar(18),Id) as Id
,convert(nvarchar(255),null) as Error
,Name as Name_Info
into Account_BulkAPIv1_Wait_Serial_Delete
from Account
order by createddate desc
Running the example
exec ss_Loader 'BulkAPIv1Delete', 'DEMO', 'Account_BulkAPIv1_Wait_Serial_Delete','WAIT:SERIAL'
SQL-SALES BulkAPIv1Delete run date: 2023-12-09 ------------------
09:25:17: Using Env|Schema: DEMO|dbo
09:25:18: Starting Loader for Account batchsize 10000
09:25:18: SSId added to Account_BulkAPIv1_Wait_Serial_Delete
09:25:20: Connection method BULK & BULK API
09:25:20: Bulk API method WAIT:SERIAL
09:25:20: Columns checked against Salesforce metadata
09:25:21: Starting load for Account_BulkAPIv1_Wait_Serial_Delete
09:25:35: JobId: 7508d00000TtRBUAA3
09:25:35: 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 + “_Batch”, using the above example it is:
Account_BulkAPIv1_Wait_Serial_Delete_Return
As with the Delete 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 Delete operation.
Create a 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_Delete". 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_Delete
select top 100
convert(nchar(18),Id) as Id
,convert(nvarchar(255),null) as Error
,Name as Name_Info
into Account_BulkAPIv1_BACK_Serial_Delete
from Account
order by createddate desc
Running the example (Step 1)
exec ss_Loader 'BulkAPIv1Delete', 'DEMO', 'Account_BulkAPIv1_BACK_Serial_Delete','BACK:SERIAL'
SQL-SALES BulkAPIv1Delete run date: 2023-12-09 ------------------
19:35:22: Using Env|Schema: DEMO|dbo
19:35:22: Starting Loader for Account batchsize 10000
19:35:22: SSId added to Account_BulkAPIv1_BACK_Serial_Delete
19:35:25: Connection method BULK & BULK API
19:35:25: Bulk API method BACK:SERIAL
19:35:25: Columns checked against Salesforce metadata
19:35:26: Starting load for Account_BulkAPIv1_BACK_Serial_Delete
19:35:39: JobId: 7508d00000TtTgVAAV
19:35:39: BatchId: 7518d00000dC1LzAAK CreatedDate: 2023-12-09 19:35:29
19:35:39: BulkAPIv1Delete 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 'BulkAPIv1Delete', 'DEMO', 'Account_BulkAPIv1_BACK_Serial_Delete','JOB:WAIT:SERIAL','7508d00000TtTgVAAV'
SQL-SALES BulkAPIv1Delete run date: 2023-12-09 ------------------
19:41:47: Using Env|Schema: DEMO|dbo
19:41:47: Starting Loader for Account batchsize 10000
19:41:48: SSId added to Account_BulkAPIv1_BACK_Serial_Delete
19:41:50: Connection method BULK & BULK API
19:41:50: Bulk API method JOB:WAIT:SERIAL Job = 7508d00000TtTgVAAV
19:41:50: Columns checked against Salesforce metadata
19:41:51: Starting load for Account_BulkAPIv1_BACK_Serial_Delete
19:41:54: JobId: 7508d00000TtTgVAAV, Job Closed
19:41:54: 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_Delete_Return
As with the Delete 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 Delete 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.