Update Source SQL Table after Import to Dynamics CRM

After importing data to Dynamics CRM you may require the source (SQL table) to be updated with information such as the import or update date.

Data Sync is designed to allow you to extend its processes at any point, in this case we can use the 'Project Automation' feature to look back at the source SQL table and update an 'Imported' column with the date of the import.

Activate Project Automation

To activate the project automation feature go to 'View' in the menu bar and select 'Project Automation Window'.

This will open a new tab titled ' Project Automation', click on the 'Enable Project Automation' link to activate the feature.

Project Automation Code

The SQL Server provider also exposes helper functions that can be called from Project Automation to update the Data source UpdateSourceRow.

Caution: Using this method on the Target connection with a Transaction > 0 will cause a DeadLock since a SQL Transaction is in place.

UpdateSourceRow

Method UpdateSourceRow creates a SQL Command to update a single column in the Data source using the key from the DataCompareItem

bool UpdateSourceRow(string column, object value, DataCompareItemInvariant item)

Used to update the source row from project automation, for example setting a Sync flag once a record has been synchronised.

For example calling this method in the AfterUpdateItem item event to mark a record in the source as synchronised.

public override void AfterUpdateItem(object sender, DataCompareItemInvariant item, object identity)
{
    DataSourceA.UpdateSourceRow("Sync", true, item);
}