ss_Delta is a Stored Procedure you execute within your enabled database.
Prerequisites
A working Environment and the SQL Sales Daemon is running (see Environment Setup)
SQL Server
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:
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.
ss_Delta will not run if no prior entry exists in the ss_Log table (unless ‘Yes’ was passed to parameter @AutoReplica).
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'''