Incremental Sync

The below tutorial will cover what incremental sync mode is and how to apply it to your project.

In Incremental Mode you return a subset of data you want to check/sync against the target, for example all records created/modified today. Data Sync will then load the matching records from the target based on the key values of the source records. The same Data Compare process then runs and ADD/UPDATE actions are created as necessary.

Limitations

Incremental sync mode is limited by the following:

• Can only use a single key column, you cannot use composite keys.
• DELETE actions cannot be created, as we are only seeing a subset of the data and so there is no way to figure out delete actions.
• Source Records should be less than 100k records as we need to match the keys on the target, so this could be slow. However we use batching and multiple threads to request all of the records in parallel so this is actually quite fast.
• Data Connector must support Incremental Mode.

Supported Data Connectors

The following providers support incremental sync mode:

• Microsoft SQL Server
• ODBC Connections
• OleDb Connection
• Microsoft Access
• Microsoft SQL Compact Edition 4.0
• SqLite
• Microsoft Dynamics CRM (Entities)
• Microsoft SharePoint (Client API)
• Microsoft SharePoint Online (Office 365)
• Azure Table Storage
• Amazon S3
• Amazon SimpleDb
• Podio Items
• Simego WebStore List
• Salesforce
• ADO.NET

Enable Incremental Mode

To set the sync mode for your project to incremental, select Incremental from the Sync Mode option in the shortcut menu.

Recommendations

To help your project run smoother when using incremental sync mode follow the below recommendations:

• Ensure that the Key column in the target is an indexed field.
• Lookups may still cause issues if the Target Lookup Data Source is large, so consider using LOOKUPAINCREMENTAL and LOOKUPBINCREMENTAL. However since these round-trip on each lookup value it is recommend only when your source contains very few records.
• Use Project Automation to re-write your source filter at runtime to keep the source records small, such as the example code below.

public override void Start()
{
	DataSourceA.FetchXmlFilterExpression = DataSourceA.GetFetchFilterXmlForModifiedSince(DateTime.Today);
}

• Use Project Automation to adjust your data source properties at runtime. For example, to update the Where clause of a SQL Provider you could use code similar to that below.

public override void Start()
{
    DataSourceA.CommandWhere = string.Format("DateModified >= '{0}'", Properties[DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss")]);
}