Skip to main content
ss_Delta

Replicating ss_Delta exporting Salesforce data Delta

Updated over 3 months ago

ss_Delta is a Stored Procedure you execute within your enabled database.

Prerequisites

  1. A working Environment and the SQL Sales Daemon is running (see Environment Setup)

  2. SQL Server

  3. Compiled stored procedure ss_Delta (see Database Enabling)

ss_Log replication log

Replication is logged in table “ss_Log” which is automatically initially created by a prior ss_Replica runs and maintained for all subsequent ss_Delta runs.

Note, the table is created within the schema to which you are running ss_Delta, hence if you ran with uat.ss_Delta, 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, created as a replica table in the given enabled Database

ObjectName

Source Salesforce Object of this replication log (will be the same as ReplicaName if no customisation has occurred with the “Table:” switch

CustomReplica

Indicates if the Replica is Custom (i.e. not a basic replication of a Salesforce Object Name but ReplicaName is a custom table name

TableCreatedDate

Serves no functional purpose, is for information purposes only

MaxSystemDate

This is how the ss_Delta process determines how to delta replicate

LogDate

Last log datetime

TopRow

Captures any Top commands, for example 'Account:Top100'

Subset

Captures any Column Subset commands, for example 'Subset(Name,BillingCountry)'

Status

Possible values:

FAILURE (indicates a failure has occurred, see Detail field)

FULL (indicates a Full replication occurred, via ss_Replica)

DELTA (indicates a partial replication occurred)

WhereInput

Captures any Where clause commands injected into the replication

Detail

Success or Failure detail

ss_Log_Working detailed replication log

For more granular detail of every replication run, each step is logged in “ss_Log_Working” which can be helpful in monitoring longer running processes of millions of rows although for ss_Delta runs that is a less common scenario, unless significant changes have occured on the given Salesforce Object – if so it may be more efficient in that situation to run ss_Replica. The BULK connection method will deliver the data payload in chunks, which are logged and so can be monitored to check progress.

Field

Purpose

Environment

The specified Salesforce Environment, defined in the SQL Sales Configuration tool

Task

“ss_Delta”

ObjectName

Source Salesforce Object of this replication log (will be the same as ReplicaName if no customisation has occurred with the “Table:” switch

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 the current Chunk, if a Salesforce Id, this is the latest Id the process has got to

Note:

  1. The table is created within the schema to which you are running ss_Delta, hence if you ran with uat.ss_Delta, the resultant entry for the given object will be to table uat.ss_Log / uat.ss_Log_Working.

  2. ss_Delta will not run if no prior entry exists in the ss_Log table (unless ‘Yes’ was passed to parameter @AutoReplica).

  3. If an entry does exist in the ss_Log table but the last run was greater than 7 days ago, ss_Delta will not run, however if input parameter @AutoReplica = Yes then in this event, the ss_Delta run will failover to running ss_Replica, passing in the same input parameters as provided in the ss_Delta execution.

Parameters

Parameter

Purpose

@Env

SQL Sales Environment Name

@ObjectName

Salesforce Object Name (for example Account)

Can be accompanied with :TopXXX where XXX is a number

exec ss_Delta 'DEMO', 'Account:Top10’

For example Account:Top10

@Special1

Optional, in simple usage, null

Options

Subset(field1,field2,field3) – where field1 etc are Salesforce api fieldnames for the given object. For example in the case of the object being Account, this could be:

Subset(Name,Phone,BillingCity)

Table:<custom table name> - for example 'Table:OppTest'

No special delimiter is required between Subset(x,y,z) and Table:xxx, for example leave nothing or a space:

‘Subset(Name,Phone,BillingCity) Table:AccountTest'

@Special2

Optional, in simple usage, null

Where <SOQL> - for example ‘Where StageName = ‘’Closed Won’’’

@AutoReplica

Yes = if the given Salesforce object’s meta data definition is identified to be different to the local definition (in the prior replicated SQL table), if “Yes” is set in this parameter then ss_Replica will automatically run to fully replicate. If no value is passed, (by default the setting is “No”), which instructs ss_Delta to ignore any potentially new fields or different field definitions for existing fields.

Any Failure scenarios will also failover to a full replication via ss_Replica if this parameter is set to ‘Yes’, for example:

Objects that cannot technically be delta replicated

If the given Object has not yet been fully replicated via ss_Replica

If the entry in ss_Log is not present (yet the SQL table does exist)

If the last replication was > 7 days ago

AutoReplica

Yes = if the given Salesforce object’s meta data definition is identified to be different to the local definition (in the prior replicated SQL table), if “Yes” is set in this parameter then ss_Replica will automatically run to fully replicate. If no value is passed, (by default the setting is “No”), which instructs ss_Delta to ignore any potentially new fields or different field definitions for existing fields.

Any Failure scenarios will also failover to a full replication via ss_Replica if this parameter is set to ‘Yes’, for example:

  • Objects that cannot technically be delta replicated

  • If the given Object has not yet been fully replicated via ss_Replica

  • If the entry in ss_Log is not present (yet the SQL table does exist)

  • If the last replication was > 7 days ago

Input parameters provided to ss_Delta will be passed through to ss_Replica on an AutoReplica failover

For example taking this fairly involved example of a ss_Replica:

exec ss_Replica 'DEMO', 'Account','Subset(Name) Table:CustomAcc','Where Name like ''%plc%'''

The subsequent ss_Delta of:

exec ss_Delta 'DEMO', 'Account','Subset(Name) Table:CustomAcc','Where Name like ''%plc%''',’Yes’

In the event of any of the conditions for a failover being met, will have @Special1 and @Special2 passed through to the called ss_Replica, i.e. for this example:

@Special1 = 'Subset(Name) Table:CustomAcc'

@Special2 = 'Where Name like ''%plc%'''

Table ss_AutoReplica

On deployment table ss_AutoReplica is populated with Object Names of Objects that must be fully replicated (i.e. where it is not appropriate to Delta Replicate). If you run ss_Delta with @AutoReplica = Yes then any Object in this table will automatically be fully replicated, hence you can leverage this table to suit your own requirements if you require a given Object to always fully replicate, provided it is present in table ss_AutoReplica and parameter @AutoReplica = Yes.

Field

Purpose

ObjectName

For the given Object, will force a full replication, via ss_Replica, if parameter @AutoReplica = Yes.

Simple Example

exec ss_Delta 'DEMO', 'Account'

Specifying Batchsize

By default ss_Delta will use a batchsize of 2000 (the maximum possible via the Salesforce SOAP api). Should you wish to use a smaller batchsize, specify this by passing in the custom batchsize when passing in the Objectname as below:

exec ss_Delta 'DEMO', 'Account(25)'

Subset Example

exec ss_Delta 'DEMO', 'Account','Subset(Name,BillingCountry)'

Valid fields (i.e. that exist on the specified object and for which you have permission to query, will only be returned, although note some basic fields to support the replication logic to function correctly will be imposed (for example Id, isDeleted; SystemModStamp).

Use subset with caution as it will only delta maintain the subset of columns specified, which when run against a fully replication table, can leave fields not in the subset scope, stale. Generally you would use the same subset scope of fields in the initial ss_Replica and all subsequent ss_Delta runs.

Note, for larger sets of subset fields, you may prefer to input in a list form, example shown below, which is an acceptable input method to the @Special1 input parameter:

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

Or

exec ss_Delta 'DEMO', 'Opportunity'
,'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 ss_Replica full rebuild (i.e. the local replica table), will be notified in the output report when running ss_Delta. In the example below the field "TestCheckbox__c" has been identified as being newly available. ss_Delta makes no attempt to merge this into the local, as there will inevitably be rows in the local replica that fall outside the delta scope of the current delta replication. It is therefore recommended to run a full ss_Replica at the next available opportunity. Unless you have run ss_Delta with @AutoReplica = ‘Yes’ in which case ss_Replica will have been automatically run and you wouldn’t be encountering this information message

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!

Combined Subset, Batchsize and Top check example

exec ss_Delta 'DEMO', 'Account(25):Top100','Subset(Name,BillingCountry)'

Custom Replica Example

exec ss_Delta 'DEMO', 'Opportunity','Table:OppTest'

In this example, Salesforce Opportunity data will be delta replicated to the prior created via ss_Rplica “OppTest” SQL table. This will exist entirely independently of any potential “Opportunity” replica existing in the same deployed schema in your given Database.

For example running

exec ss_Delta 'DEMO', 'Opportunity','Table:OppTest'
exec ss_Delta 'DEMO', 'Opportunity'

will maintain two separate replica tables of Salesforce Opportunity data, one called Opportunity, the other called OppTest. Note, the “Table:” switch must be applied consistently on each subsequent ss_Delta run.

Where Clause Example

exec ss_Delta 'DEMO', 'Opportunity','Table:OppTest','Where StageName = ''Closed Won'''

In this example, a where clause has been added, which will be applied to the delta replica. It is entirely optional to apply this to a custom table or against the full SF object name

Note, any custom Replica created with a Where clause must continue to have that same identical Where clause applied any subsequent ss_Delta calls, otherwise the Delta logic will be compromised as there will be no consistency between the scope of the original ss_Replica and the delta data retrievals via ss_Delta.

In this example, a where clause has been added to the base Salesforce object replica (i.e. no custom table usage), the clause Type = New Customer has been passed in

exec ss_Replica 'DEMO', 'Opportunity',null,'Where Type = ''New Customer'''
exec ss_Delta 'DEMO', 'Opportunity',null,'Where Type = ''New Customer'''

Did this answer your question?