SQL Server to Jiandaoyun Integration Overview: MES Personnel Master Data Sync

Scenario and Value

In a manufacturing enterprise's MES Health, Safety and Environment (HSE) system, personnel master data has long lived in SQL Server, while on-site inspections, hazard reporting, and training sign-off run on Jiandaoyun forms. As soon as those two sides are maintained manually, the same issues show up: a newly onboarded HSE engineer cannot find the right department in Jiandaoyun; a departing employee's account stays enabled in Jiandaoyun for two weeks, causing workflows to be routed to the wrong person; and during the monthly safety qualification review, the personnel ledgers on both sides do not match, so IT ends up exporting Excel sheets for manual reconciliation.

After several similar projects, the conclusion is consistent: this is not a problem of one system misreporting data, but the lack of a stable, automated closed loop for personnel master data. This SQL Server and Jiandaoyun integration solution aims to use a single, independently executable sync strategy to reliably deliver personnel master data from the MES source database to Jiandaoyun, so that HSE business collaboration truly runs on a unified personnel ledger.

Integration Architecture and Data Flow

Overall this is a one-way pipeline: SQL Server is the source, Jiandaoyun is the target APaaS, and the integration middleware handles scheduling, transformation, and write-back.

┌─────────────────┐         ┌──────────────────┐         ┌─────────────────┐
│   SQL Server    │  ────►  │  Integration     │  ────►  │   Jiandaoyun    │
│  (MES HSE src)  │  pull   │  Middleware/ETL  │  write   │  (target APaaS) │
└─────────────────┘         └──────────────────┘         └─────────────────┘
       │                              │                            │
       │ Timestamp incremental        │ Code mapping               │ Form/API
       │ ModifyTime/CreateTime        │ Dept/Job/Gender/Status     │ Personnel form
       ▼                              ▼                            ▼

The data flow has three segments:

1. Source pull: On the MES HSE personnel table in SQL Server, use ModifyTime or CreateTime as the incremental watermark to periodically pull changed rows; for first-time go-live or data repair, remove the incremental condition and run a full pull.
2. Middleware transformation: Handle field alignment and code mapping, translating enum values such as department code, job code, gender, and status from the MES side to the option values of the Jiandaoyun form. This is the segment with the highest error rate and must be centrally maintained.
3. Target write: Call the form API using the authentication method configured by the Jiandaoyun app, use EmpCode as the business key for insert or update, and keep each batch under 100 records to avoid triggering platform-side rate limits.

Interface List

The whole solution contains only one strategy with no external dependencies, and can be scheduled independently.

Implementation Points

• Phased scheduling: Run incremental every 15–30 minutes; for first-time go-live, data repair, and month-end reconciliation, trigger full pulls, and schedule full tasks during off-peak business hours.
• Incremental field: Use ModifyTime as the primary watermark and CreateTime as the secondary; persist the watermark in the middleware layer to avoid missing or re-pulling rows after a restart.
• Full fallback: Keep a separate full-pull channel without incremental conditions, scheduled independently from the incremental task, so you can switch quickly when something breaks instead of modifying SQL on the fly.
• Code mapping: Centrally maintain mappings for enum values such as department, job, gender, and status. When the source adds a new enum, the corresponding option must first be created on the Jiandaoyun side; otherwise the write will fail immediately.
• Exception retry: Apply exponential backoff retry for transient errors such as network jitter and platform rate limiting; for business errors such as field overflow and missing enums, route them into an exception table for manual repair instead of infinite retry.
• Privacy handling: Mask or restrict visibility of sensitive fields such as mobile number and email as needed; store API credentials in environment variables or a key management service, never in configuration files.

Best Practices and Pitfalls

1. Centralize code mapping instead of scattering it across strategies. The most common failure we have seen is that a new department was added on the MES side but not synchronized to Jiandaoyun, causing the entire personnel sync to fail. The safe approach is to extract enum mappings as a shared dataset on the Qeasy iPaaS platform so that all related strategies share them, and alert immediately when the source enums change.
2. Schedule incremental and full pulls separately instead of mixing them in one task. In one real project, a customer controlled the incremental condition with a variable; once the parameter was not passed in, it silently degraded to an unconditional full table scan and crashed the MES database. The safe approach is to define two independent strategies, each with its own input conditions, concurrency, and logs.
3. Set batch size according to actual platform rate limits, not guesswork. The Jiandaoyun form API is quite easy to trigger rate limits on during large batch writes. It is recommended to keep each batch under 100 records and implement breakpoint resumption for failed batches.
4. Always run a full reconciliation before first go-live. Before enabling incremental, perform an end-to-end full comparison first to confirm that both sides have matching row counts and key fields, then switch to incremental; otherwise historical discrepancies will be silently masked by the incremental logic.

When to Use Qeasy

If your master data lives in traditional databases such as SQL Server while business collaboration runs on APaaS platforms such as Jiandaoyun, and you need stable, observable one-way or two-way personnel master data sync, you can directly use the Qeasy iPaaS data integration platform. It turns scheduling, code mapping, incremental watermarks, exception retry, and credential management into ready-to-use configuration items, removing the long-term maintenance cost of self-built ETL, and is especially suited for scenarios with only one or two strategies that need to run stably for two to three years.