Example 2: Salesforce <-> MSSQL contact sync
Why this example
The operations team owns Contacts in an internal MSSQL database; the sales team owns the same Contacts in Salesforce. Updates flow both ways: a customer-service rep updates a phone number in MSSQL, a sales rep updates the same Contact's address in Salesforce, and both records need to converge. A pipeline orchestrates two workflows so they run in a known order: first SF -> MSSQL, then MSSQL -> SF, both using a LastModifiedDate watermark.
Helix Beverages context: this is the recipe that finally fixed CRM and ERP drift (named problem 1 in the Helix overview). Sales reps were editing the same Contact in Salesforce that customer-success agents were editing in MSSQL, and by Friday neither side trusted the other. A 15-minute reconcile keeps both stores converged without anyone having to pick a "winner" system.
At a glance
- Connectors used: Salesforce, MSSQL
- Nodes used: Start, Source, Transform, Load, DbScript
- Schedule: every 15 minutes, orchestrated by a Pipeline
- Direction: Two-way
Canvas
Two workflows, one pipeline.
Workflow A (Salesforce -> MSSQL)
Workflow B (MSSQL -> Salesforce)
The pipeline orchestrator runs Workflow A first (on failure: stop), then Workflow B (on failure: continue), then writes the watermark to a Variable.
Step-by-step
| Workflow A | ||||
|---|---|---|---|---|
| 1 | Start | -- | -- | -- |
| 2 | DbScript: ensure staging | MSSQL DbScript | IF NOT EXISTS … CREATE TABLE dbo.ContactsStg (…). Idempotent. | -- |
| 3 | Source | Salesforce: query Contact | SELECT Id, FirstName, LastName, Phone, Email, MailingAddress, LastModifiedDate FROM Contact WHERE LastModifiedDate > \{\{var.global.lastSyncAt\}\} | SF Contact row |
| 4 | Transform | -- | Rename Id -> salesforce_id, flatten MailingAddress.\{street, city, state\} -> three columns. | MSSQL Contact row |
| 5 | Load | MSSQL: Merge | Target dbo.Contacts, merge key salesforce_id. | -- |
| Workflow B | ||||
|---|---|---|---|---|
| 1 | Start | -- | -- | -- |
| 2 | Source | MSSQL: query | SELECT contact_id, first_name, last_name, phone, email, address_street, address_city, address_state, last_modified FROM dbo.Contacts WHERE last_modified > \{\{var.global.lastSyncAt\}\} | MSSQL Contact row |
| 3 | Transform | -- | Build SF Contact shape; include mssql_contact_id__c (an external-Id custom field) so the Load can upsert without dupes. | SF Contact row |
| 4 | Load | Salesforce: Upsert | External Id field mssql_contact_id__c. | -- |
Variations
- Conflict resolution -- when both sides edit the same Contact between runs, the latter-running workflow wins. Add a Compute node before each Load that compares
LastModifiedDateand skips the row when the destination is newer. - Three-way with HubSpot -- add Workflow C (HubSpot -> MSSQL) and a Workflow D (MSSQL -> HubSpot) and grow the pipeline to four steps. Same pattern repeats.
- Bigger fields -- if the row volume per run grows past a few thousand, switch the watermark from a single timestamp to a delta-table approach (Workflow A appends to
dbo.ContactsStg, Workflow B reads from staging instead of fromdbo.Contacts).