Create a load table Example (WAIT)
This example creates a load table called "Account_BulkAPIv1_Wait_Example_Insert".
WAIT in Version 2 of the Bulk API means the SQL process will remain live while it waits for the Bulk API to return all responses to the _Return table (Version 2 is not able to return Error values and Ids back to the load table for Inserts and Updates, however it will for updates, Deletes and Harddeletes.
drop table if exists Account_BulkAPIv2_Wait_Insert
select top 100
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,'DEMO__PREFIXv2' + Name as Name
,'DEMO__PREFIXv2' + AccountNumber as AccountNumber
-----
,Name as Name_Info
,AccountNumber as AccountNumber_Info
into Account_BulkAPIv2_Wait_Insert
from Account
where AccountNumber is not null
Running the example
exec ss_Loader 'BulkAPIv2Insert', 'DEMO', 'Account_BulkAPIv2_Wait_Insert','WAIT'
SQL-SALES BulkAPIv2Insert run date: 2023-12-09 ------------------
21:18:44: Using Env|Schema: DEMO|dbo
21:18:44: Starting Loader for Account batchsize 10000
21:18:44: SSId added to Account_BulkAPIv2_Wait_Insert
21:18:47: Connection method BULK & BULK API
21:18:47: Bulk API method WAIT
21:18:47: Columns checked against Salesforce metadata
21:18:48: Starting load for Account_BulkAPIv2_Wait_Insert
21:19:03: JobId: 7508d00000TtU7kAAF
21:19:03: Excluded: AccountNumber_Info is not available on object Account
21:19:03: Excluded: Name_Info is not available on object Account
21:19:03: 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
For Inserts and Version 2, no direct response of Ids and Error column values are written back to the Load table.
_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_BulkAPIv2_Wait_Insert_Return
For Version 2, this provides the created Id and/or Error reason for information purposes, but the rows do not tire back to your load table.
Create a load table Example (BACK)
This example creates a load table called "Account_BulkAPIv2_BACK_Insert".
drop table if exists Account_BulkAPIv2_BACK_Insert
select top 100
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,'DEMO__PREFIX' + Name as Name
,'DEMO__PREFIX' + AccountNumber as AccountNumber
-----
,Name as Name_Info
,AccountNumber as AccountNumber_Info
into Account_BulkAPIv2_BACK_Insert
from Account
where AccountNumber is not null
Running the example (Step 1)
exec ss_Loader 'BulkAPIv2Insert', 'DEMO', 'Account_BulkAPIv2_BACK_Insert','BACK'
SQL-SALES BulkAPIv2Insert run date: 2023-12-09 ------------------
21:25:53: Using Env|Schema: DEMO|dbo
21:25:53: Starting Loader for Account batchsize 10000
21:25:54: SSId added to Account_BulkAPIv2_BACK_Insert
21:25:57: Connection method BULK & BULK API
21:25:57: Bulk API method BACK
21:25:57: Columns checked against Salesforce metadata
21:25:57: Starting load for Account_BulkAPIv2_BACK_Insert
21:26:00: JobId: 7508d00000TtU9WAAV
21:26:00: BulkAPIv2Insert 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 write back to the Result table, you will need to run a subsequent WAIT for that to be actioned
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 _Return 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 Id and 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 BULK 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 'BulkAPIv2Insert', 'DEMO', 'Account_BulkAPIv2_BACK_Insert','JOB:WAIT','7508d00000SopjWAAR'
SQL-SALES BulkAPIv2Insert run date: 2023-12-09 ------------------
21:29:59: Using Env|Schema: DEMO|dbo
21:29:59: Starting Loader for Account batchsize 10000
21:29:59: SSId added to Account_BulkAPIv2_BACK_Insert
21:30:02: Connection method BULK & BULK API
21:30:02: Bulk API method JOB:WAIT Job = 7508d00000TtU9WAAV
21:30:02: Columns checked against Salesforce metadata
21:30:03: Starting load for Account_BulkAPIv2_BACK_Insert
21:30:06: JobId: 7508d00000TtU9WAAV, Job Complete
21:30:06: Excluded: AccountNumber_Info is not available on object Account
21:30:06: Excluded: Name_Info is not available on object Account
21:30:06: Load complete: Success:100 Failure:0
-----------------------------------------------------------------
Checking the _Return table
The success or failure errors and newly created Ids will be automatically written back to the _Return table.
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 'BulkAPIv2Insert', 'DEMO', 'Account_BulkAPIv2_BACK_Insert','JOB:BACK',' 7508d00000TtU9WAAV'
SQL-SALES BulkAPIv2Insert run date: 2023-12-09 ------------------
21:32:33: Using Env|Schema: DEMO|dbo
21:32:33: Starting Loader for Account batchsize 10000
21:32:33: SSId added to Account_BulkAPIv2_BACK_Insert
21:32:36: Connection method BULK & BULK API
21:32:36: Bulk API method JOB:BACK Job = 7508d00000TtU9WAAV
21:32:36: Columns checked against Salesforce metadata
21:32:36: Starting load for Account_BulkAPIv2_BACK_Insert
21:32:40: JobId: 7508d00000TtU9WAAV, Job Complete
21:32:40: BulkAPIv2Insert BACKGROUND completed successfully
-----------------------------------------------------------------