Bulk API Delta Replication

ss_DeltaB provides incremental replication of Salesforce data into an existing SQL Server replica using Salesforce Bulk API 2.0.

The core Delta functionality is the same as ss_Delta. SQL-Sales determines the point from which the Salesforce object was last successfully replicated and retrieves subsequent changes, applying Inserts, Updates and Deletes to the existing SQL replica.

For example you may wish to establish a replication with ss_ReplicaB but subsequently maintain with ss_Delta (i.e. and not ss_DeltaB) if you know the source Salesforce Object has relatively slowly moving data and the Bulk API is not whooly appropriate for deltas after that point) – or simply use ss_DeltaB if you wish consistency, the choice is yours.

Bulk API 2.0 differs from the conventional Salesforce REST/SOAP query APIs in that queries are processed asynchronously by Salesforce. SQL-Sales therefore provides several execution methods which control how a Bulk API Query job is submitted, monitored and retrieved.

ss_DeltaB can be particularly useful where a significant volume of changes has occurred since the previous replication.

Prerequisites

1. A working Environment and the SQL Sales Daemon is running (see Environment Setup)
2. SQL Server
3. Compiled stored procedure ss_DeltaB (see Database Enabling)
4. A prior full replication of the object, unless @AutoReplica = ‘Full’ is being used

ss_Log replication log

Replication is logged in table ss_Log, which is initially created by a full replication and maintained by subsequent ss_DeltaB runs.

Note, the table is created within the schema from which you are running ss_DeltaB, hence if you ran with uat.ss_DeltaB, the resultant entry for the given object will be to table uat.ss_Log.

Field	Purpose
ReplicaName	The Salesforce object name or a Custom table name if one has been specified
ObjectName	Source Salesforce Object of this replication log
CustomReplica	Indicates if the Replica is Custom, i.e. ReplicaName is a custom table name
TableCreatedDate	Serves no functional purpose, is for information purposes only
MaxSystemDate	Used by ss_DeltaB to determine the starting point for subsequent delta replication
LogDate	Last log datetime
TopRow	Captures any Top commands
Subset	Captures any Column Subset or Exclude commands
Status	Possible values include FAILURE, FULL and DELTA
WhereInput	Captures any Where clause commands applied to the replication
Detail	Success or Failure detail

ss_Log_Working detailed replication log

For more granular detail of every replication run, eachstep is logged in ss_Log_Working.

This can be helpful in monitoring longer-running processes involving a significant number of changed rows, although this is generally less common for Delta replication than for a full replication.

Field	Purpose
Environment	The specified Salesforce Environment, defined in the SQL Sales Configuration tool
Task	ss_DeltaB
ObjectName	Source Salesforce Object
PreProcessPoint	The process which is about to be started
Detail1	The contents of parameter @Special1
Detail2	The contents of parameter @Special2
Detail3	The contents of parameter @AutoReplica
Detail4	Logs progress information associated with the Delta replication

ss_DeltaB requires information from the previous replication in ss_Log in order to determine the appropriate Delta scope. If a suitable prior replication is not available, @AutoReplica = 'Full' can be used to allow SQL-Sales to fall back to a full replication.

Parameters

Parameter	Purpose
@Env	SQL Sales Environment Name
@ObjectName	Salesforce Object Name, for example Account.
@AutoReplica	Optional. No by default. Full allows SQL-Sales to fall back to a full replication where a Delta cannot safely or appropriately be performed.
@BAPIMethod	Optional Bulk API execution method. Valid methods are WAIT, WAIT(n), BACK, GET and CHECK(n). Default is WAIT.
@BAPIJobId	Optional Bulk API JobId. Normally SQL-Sales automatically identifies the appropriate outstanding job. This parameter can be used to specify a particular JobId where required.
@Special1	Optional. Supports Subset(field1,field2,field3), Exclude(field1,field2,field3) and Table:CustomTableName.
@Special2	Optional. Supports a SOQL Where clause, for example Where StageName = ‘Closed Won’.

Simple Example

exec ss_DeltaB 'DEMO', 'Account'

By default ss_DeltaB uses the WAIT Bulk API execution method.

Bulk API Execution Methods

ss_DeltaB uses Salesforce Bulk API 2.0 Query jobs to retrieve the records within the required Delta scope.

The optional @BAPIMethod parameter controls how SQL-Sales submits, waits for, and retrieves the Bulk API Query job.

If no method is specified, WAIT is used by default.

Method	Purpose
WAIT	Submits the Bulk API Query job and waits synchronously until Salesforce completes it.
WAIT(n)	Resilient wait mode. Submits and records the JobId, then checks the job every n seconds until complete. Valid values are 1–3600 seconds.
BACK	Submits the Bulk API Query job in the background and returns control immediately. The job can subsequently be retrieved using GET or CHECK(n).
GET	Checks the latest outstanding Delta Bulk API Query job once. If complete, the results are retrieved and the Delta applied. If not yet complete, control is returned without waiting.
CHECK(n)	Checks the latest outstanding Delta Bulk API Query job and, if it is not complete, checks again every n minutes until complete. Valid values are 1–59 minutes.

WAIT

WAIT is the default method and requires no additional parameter.

exec ss_DeltaB 'DEMO', 'Account'

The following is equivalent:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'WAIT'

SQL-Sales determines the required Delta scope, submits the Bulk API Query job and maintains the synchronous process until Salesforce completes the job.

The returned data is then processed and the appropriate Inserts, Updates and Deletes are applied to the existing SQL replica.

WAIT(n)

WAIT(n) provides a more resilient synchronous method. The Bulk API JobId is recorded immediately after submission and SQL-Sales then checks the job until Salesforce reports that it is complete.

The value of n is the number of seconds between subsequent checks and can be between 1 and 3600.

For example, to check every 10 seconds:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'WAIT(10)'

SQL-Sales performs an immediate first check after submitting the job. The specified delay is only applied if Salesforce reports that the job is not yet complete.

Because the JobId is recorded immediately, an interrupted WAIT(n) Delta can subsequently be recovered using GET or CHECK(n).

For example, to check a larger Delta job every five minutes:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'WAIT(300)'

WAIT(n) specifies the interval in seconds, whereas CHECK(n) specifies the interval in minutes.

BACK

BACK determines the required Delta scope, submits the Bulk API Query job to Salesforce and then returns control to SQL Server without waiting for Salesforce to complete the job.

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'BACK'

The Bulk API JobId is recorded by SQL-Sales so that the job can subsequently be retrieved.

For example, a Delta job can first be submitted (as above) and subsequently retrieved with:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'GET'

or monitored until completion with:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'CHECK(5)'

This allows the Salesforce processing stage to occur independently of the process which originally submitted the Delta job.

GET

GET checks the latest applicable outstanding Delta Bulk API Query job previously recorded by SQL-Sales.

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'GET'

If Salesforce reports that the job is complete, SQL-Sales retrieves the results and applies the Delta changes to the existing replica.

If the job is not yet complete, GET returns control without waiting. The same command can therefore be run again later.

If there is no applicable outstanding job for the specified object and replica table, SQL-Sales reports that there is no outstanding job and no Delta action is performed.

CHECK(n)

CHECK(n) provides the automated equivalent of repeatedly running GET.

The valueof n specifies the number of minutes between checks and can be between 1 and 59.

For example:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'CHECK(5)'

SQL-Sales checks the latest applicable outstanding Delta job. If the job has not completed, SQL-Sales waits five minutes and checks again.

Once Salesforce reports that the job is complete, the results are retrieved and the Delta changes are applied to the existing SQL replica.

A typical detached Delta can therefore be performed as:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'BACK'

followed later by:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'CHECK(5)'

ss_BulkAPILog

SQL-Sales automatically maintains ss_BulkAPILog to track Bulk API jobs used by ss_ReplicaB and ss_DeltaB.

The table is created within the deployed SQL-Sales schema.

For example, when executing:

exec uat.ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'BACK'

the Bulk API job information is maintained in:

uat.ss_BulkAPILog

The log enables SQL-Sales to identify outstanding jobs when GET or CHECK(n) is subsequently executed.

Field	Purpose
ObjectName	Source Salesforce Object
TableName	Target SQL replica table
Operation	Bulk API operation associated with the job
Method	Method/lifecycle status associated with the job
JobId	Salesforce Bulk API JobId
BatchId	Batch identifier where applicable
BatchCreatedDate	Batch creation datetime where applicable
LogDate	SQL-Sales log datetime

For normal operation it is not necessary to manually obtain or pass the Salesforce JobId as SQL-Sales uses the log to identify the appropriate outstanding job.

Specifying a Bulk API JobId

An optional @BAPIJobId parameter is available where it is necessary to explicitly specify a particular Salesforce Bulk API Query JobId.

For example:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'GET',
    @BAPIJobId = '750XXXXXXXXXXXXXXX'

This is primarily intended for recovery, diagnostic or other exceptional scenarios. In normal use, GET and CHECK(n) automatically use the applicable outstanding JobId recorded in ss_BulkAPILog.

AutoReplica

@AutoReplica controls whether ss_DeltaB is permitted to fall back to a full replication when a Delta cannot safely or appropriately be performed.

By default:

@AutoReplica = 'No'

Alternatively:

@AutoReplica = 'Full'

allows SQL-Sales to perform a full replication where required.

Examples of conditions which may require a full replication include:

• The Salesforce object’s metadata definition has changed compared with the existing local replica.
• The object cannot technically be Delta replicated.
• The object has not previously been fully replicated.
• The required ss_Log information is not available.
• The previous replication is too old to safely perform the requested Delta.
• Another failure condition is encountered for which SQL-Sales determines that a full replication is required.

Input parameters provided to ss_DeltaB are passed through to the full replication where applicable.

For example, given a replica created with:

exec ss_ReplicaB 'DEMO', 'Account',
    @Special1 = 'Subset(Name) Table:CustomAcc',
    @Special2 = 'Where Name like ''%plc%'''

a subsequent Delta can be run with:

exec ss_DeltaB 'DEMO', 'Account',
    @AutoReplica = 'Full',
    @Special1 = 'Subset(Name) Table:CustomAcc',
    @Special2 = 'Where Name like ''%plc%'''

If an AutoReplica condition is encountered, the same replica definition is therefore available to the full replication process.

Table ss_AutoReplica

On deployment, table ss_AutoReplica is populated with Object Names of objects that must be fully replicated, i.e. objects for which it is not appropriate to Delta replicate.

If you run ss_DeltaB with:

@AutoReplica = 'Full'

then an object present in this table will be handled through full replication.

You can therefore leverage this table for your own requirements if you require a given object to always fully replicate.

Field	Purpose
ObjectName	For the given Object, forces full replication when @AutoReplica = 'Full'.

Subset or Exclude Example

exec ss_DeltaB 'DEMO', 'Account',
    @Special1 = 'Subset(Name,BillingCountry)'

exec ss_DeltaB 'DEMO', 'Account',
    @Special1 = 'Exclude(Name,BillingCountry)'

Valid fields — i.e. fields which exist on the specified object and for which you have permission to query — will only be returned, although some basic fields required for the Delta replication logic to function correctly will be imposed, for example Id, IsDeleted and SystemModstamp.

Use Subset with caution as it will only Delta maintain the subset of columns specified. When run against a fully replicated table, fields outside the subset scope can therefore become stale.

Generally, the same subset scope should be used in the initial ss_ReplicaB and all subsequent ss_DeltaB runs.

For larger sets of subset fields, you may prefer to input the fields in list form:

exec ss_DeltaB 'DEMO', 'Opportunity', @Special1 = 'Subset(
AccountId
,Amount
,CampaignId
,CloseDate
,Name
,NextStep
,OrderNumber__c
,OwnerId
,Pricebook2Id
,Probability
,StageName
,SystemModstamp
,TotalOpportunityQuantity
,TrackingNumber__c
,Type)'

Or:

exec ss_DeltaB 'DEMO', 'Opportunity',
    @Special1 = 'Subset(AccountId,Amount,CampaignId,CloseDate,Name,NextStep,OrderNumber__c,OwnerId,Pricebook2Id,Probability,StageName,SystemModstamp,TotalOpportunityQuantity,TrackingNumber__c,Type)'

Schema | Metadata change

Any changes to Salesforce since the last full replication of the local replica table are identified when running ss_DeltaB.

For example:

21:43:49: New Column: TestCheckbox__c has subsequently been created/made visible in SF since the last full replication, therefore excluded from delta scope: ss_replica recommended!

Without @AutoReplica = ‘Full’, ss_DeltaB does not attempt to merge a newly available field into the existing replica table. There may be existing rows outside the current Delta scope for which no value for the new field has been retrieved, so doing so could result in an incomplete local representation.

A new full replication is therefore recommended at the next appropriate opportunity.

When:

@AutoReplica = 'Full'

is specified, SQL-Sales can instead automatically perform the required full replication when the relevant condition is detected.

The existing conventional Delta documentation describes this same metadata-change principle.

Custom Replica Example

exec ss_DeltaB 'DEMO', 'Opportunity',
    @Special1 = 'Table:OppTest'

In this example, Salesforce Opportunity data will be Delta replicated into the previously created OppTest SQL replica table.

This exists independently of any separate Opportunity replica in the same deployed schema.

For example:

exec ss_DeltaB 'DEMO', 'Opportunity',
    @Special1 = 'Table:OppTest'

exec ss_DeltaB 'DEMO', 'Opportunity'

maintains two separate replica tables of Salesforce Opportunity data, one called Opportunity and the other OppTest.

The Table: switch must be applied consistently on each subsequent ss_DeltaB run.

Where Clause Example

exec ss_DeltaB 'DEMO', 'Opportunity',
    @Special1 = 'Table:OppTest',
    @Special2 = 'Where StageName = ''Closed Won'''

Syntax must be in SOQL format.

In this example, a WHERE clause has been added which will be applied to the Delta replication. It is entirely optional to apply this to a custom table or against the full Salesforce object name.

Any custom Replica created with a Where clause must continue to have that same identical Where clause applied to subsequent ss_DeltaB calls. Otherwise the Delta logic will be compromised as there will be no consistency between the scope of the original full replication and subsequent Delta data retrievals.

For example, a base Salesforce object replica can initially be created with:

exec ss_ReplicaB 'DEMO', 'Opportunity',
    @Special2 = 'Where Type = ''New Customer'''

and subsequently maintained with:

exec ss_DeltaB 'DEMO', 'Opportunity',
    @Special2 = 'Where Type = ''New Customer'''

The existing Delta documentation likewise requires the WHERE clause to remain consistent between the original Replica and subsequent Delta operations.

Choosing an execution method

For most normal Delta replications, the default (WAIT) is sufficient:

exec ss_DeltaB 'DEMO', 'Account'

For a synchronous Delta where immediate recording of the Bulk API JobId and resilient polling is desirable:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'WAIT(10)'

For a particularly large Delta where you want Salesforce processing to be detached from the submitting SQL process:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'BACK'

and later:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'CHECK(5)'

GET is useful where you simply want to check once and return immediately if the job is not yet complete:

exec ss_DeltaB 'DEMO', 'Account',
    @BAPIMethod = 'GET'