Create a load table Example (WAIT)
This example creates a load table called "Account_BulkAPIv2_Wait_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_BulkAPIv2_Wait_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_BulkAPIv2_Wait_Update
from Account
where AccountNumber not like '0%'
order by createddate desc
Running the example
exec ss_Loader 'BulkAPIv2Update', 'DEMO', 'Account_BulkAPIv2_Wait_Update','WAIT'
SQL-SALES BulkAPIv2Update run date: 2023-12-09 ------------------
21:34:15: Using Env|Schema: DEMO|dbo
21:34:15: Starting Loader for Account batchsize 10000
21:34:15: SSId added to Account_BulkAPIv2_Wait_Update
21:34:18: Connection method BULK & BULK API
21:34:18: Bulk API method WAIT
21:34:18: Columns checked against Salesforce metadata
21:34:18: Starting load for Account_BulkAPIv2_Wait_Update
21:34:32: JobId: 7508d00000TtUCQAA3
21:34:33: Excluded: AccountNumber_Orig is not available on object Account
21:34:33: 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.
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 a load table Example (BACK)
This example creates a load table called "Account_BulkAPIv2_BACK_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_BulkAPIv2_BACK_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_BulkAPIv2_BACK_Update
from Account
where AccountNumber not like '0%'
order by createddate desc
Running the example (Step 1)
exec ss_Loader 'BulkAPIv2Update', 'DEMO', 'Account_BulkAPIv2_BACK_Update','BACK'
SQL-SALES BulkAPIv2Update run date: 2023-12-09 ------------------
21:37:02: Using Env|Schema: DEMO|dbo
21:37:03: Starting Loader for Account batchsize 10000
21:37:03: SSId added to Account_BulkAPIv2_BACK_Update
21:37:05: Connection method BULK & BULK API
21:37:05: Bulk API method BACK
21:37:05: Columns checked against Salesforce metadata
21:37:06: Starting load for Account_BulkAPIv2_BACK_Update
21:37:09: JobId: 7508d00000TtUCpAAN
21:37:09: BulkAPIv2Update 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 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. 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 'BulkAPIv2Update', 'DEMO', 'Account_BulkAPIv2_BACK_Update','JOB:WAIT',' 7508d00000TtUCpAAN'
SQL-SALES BulkAPIv2Update run date: 2023-12-09 ------------------
21:38:53: Using Env|Schema: DEMO|dbo
21:38:53: Starting Loader for Account batchsize 10000
21:38:53: SSId added to Account_BulkAPIv2_BACK_Update
21:38:56: Connection method BULK & BULK API
21:38:56: Bulk API method JOB:WAIT Job = 7508d00000TtUCpAAN
21:38:56: Columns checked against Salesforce metadata
21:38:57: Starting load for Account_BulkAPIv2_BACK_Update
21:39:00: JobId: 7508d00000TtUCpAAN, Job Complete
21:39:00: Excluded: AccountNumber_Orig is not available on object Account
21:39:00: Load complete: Success:100 Failure:0
-----------------------------------------------------------------
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. Once the Job is Closed you can run as with Option 1.
exec ss_Loader 'BulkAPIv2Update', 'DEMO', 'Account_BulkAPIv2_BACK_Update','JOB:BACK','7508d00000TtUCpAAN'
SQL-SALES BulkAPIv2Update run date: 2023-12-09 ------------------
21:41:16: Using Env|Schema: DEMO|dbo
21:41:16: Starting Loader for Account batchsize 10000
21:41:16: SSId added to Account_BulkAPIv2_BACK_Update
21:41:19: Connection method BULK & BULK API
21:41:19: Bulk API method JOB:BACK Job = 7508d00000TtUCpAAN
21:41:19: Columns checked against Salesforce metadata
21:41:20: Starting load for Account_BulkAPIv2_BACK_Update
21:41:24: JobId: 7508d00000TtUCpAAN, Job Complete
21:41:24: BulkAPIv2Update BACKGROUND completed successfully
-----------------------------------------------------------------