ERP-to-Microsoft 365 Integration

Maintenance, purchasing and other business processes run in SharePoint, and they need current reference data from the ERP on IBM i. Without an integration, information such as vendors, tooling, buyers and requisitioners gets re-keyed by hand or maintained twice — which means it is wrong somewhere almost immediately.

This is a scheduled .NET 8 console application, not a Windows service. It starts, does the work, reports, and exits under Task Scheduler. For a job that runs on an interval, a resident service is more state to manage and more ways to be silently dead.

How a run works

1. Execute the configured Db2 query
2. Read the destination SharePoint list
3. Compare source and destination state
4. Create records that are new
5. Update records that changed
6. Handle records present in the destination but no longer in the source, according to configured policy

Nothing that has not changed is written. That matters both for API budget and because a write that does nothing still shows up as a modification in the destination’s history.

Implementation details worth naming

Onboarding is configuration

Adding another synchronized dataset is approximately a configuration block and a .sql query file. No new application, no new deployment shape, no new logging convention.

One configuration, several ERP libraries

The same logical ERP structure exists across multiple libraries on the IBM i. Rather than duplicating near-identical configuration per library, one entry fans out across them.

This creates a subtle hazard. If several libraries feed a single destination list, a query scoped to one library sees every record belonging to the others as missing from the source — and a naive reconcile reads missing from source as delete. So each run carries a scope discriminator, and records outside its scope are invisible to it: never matched, never treated as orphans. Key uniqueness then only has to hold within a scope, which is just as well, because the same identifier legitimately exists in more than one library.

Legacy IBM i normalization

Real-world handling that a tidy example never mentions:

• CHAR columns arrive padded to their declared width, so values must be trimmed or every single run reports spurious changes
• legacy numeric date columns in a packed CYYMMDD-style format need converting in the query rather than being patched up afterwards
• library-specific differences in the same logical table
• execution as a service account, which is the constraint behind the next point
• DSN-less ODBC configuration — a user DSN lives in one person’s registry hive, so a scheduled task running as a service account cannot see it and fails with a data-source error. Naming the driver and host directly in the connection string removes that dependency entirely.

Change detection that fails toward doing more work

A run can skip reading a destination when both the source state and the destination’s change information agree that nothing has changed. Every uncertain path resolves toward reconciling anyway: no stored state, unreadable state, a rejected change token, any failure during the previous run, or simply enough time having passed — all of these force a full reconcile.

Designed that way, a skip can only ever be unnecessary work avoided, never necessary work missed. That asymmetry is the whole reason the optimization is safe to have.

Destination types, read from the destination

Source values are coerced against the destination list’s actual column definitions rather than assuming everything is text. A numeric flag from the source lands correctly in a yes/no column because the writer looked up what that column really is. A value that genuinely cannot be coerced logs a warning and writes null rather than failing the whole batch.

The delete guardrail

A query that fails a filter clause returns zero rows. A naive mirror would faithfully conclude the source is empty and clear the destination.

So planned deletions are checked before they run: if they exceed a configured share of the destination and a minimum count, the deletion phase is skipped and the run is marked failed. The guardrail exists because the failure it prevents is unrecoverable in practice, and the cost of a false alarm is a failed run and a human looking at it.

There is a dry-run mode as well, which reports the exact create, update and delete plan and writes nothing. It is the first thing to run against a production destination.

Orphan policy

What to do with destination records no longer in the source is configuration, not a hard-coded assumption:

• ignore — right when people also maintain records by hand
• delete — the destination becomes a true mirror
• flag — mark the record inactive and leave it in place

Flagging is what the vendor data uses. The query selects active vendors, so a vendor going inactive in the ERP drops out of the result, is picked up as an orphan, and becomes IsActive = false in SharePoint. Nothing is ever destroyed, and the history survives.