Nightly SAP Sync That Times Out

Separate orchestration from page processing. A scheduled event creates a SyncRun record and enqueues page jobs or starts a worker microflow that processes one page at a time. Each page job calls SAP with a timeout above the observed 40-second maximum, maps the page, upserts the products and commits before fetching the next page. Do not keep 45,000 products or nine pages of XML in one runtime transaction; commit per page so memory is released and a failure does not roll back the whole night. Use the SAP material number as the business key and perform an idempotent upsert: retrieve by material number, create if absent, change if present. Set task queue concurrency to one for this integration so two runs cannot overlap and so SAP is not hit in parallel. Record page number, record count, start and end time, and status on SyncRun. At the end, mark products not seen in the run as inactive only after a successful full run, not after a partial failure. With nine pages at 40 seconds, the calls alone take about six minutes; the risk is not total elapsed time but memory and one bad record aborting everything. The orchestrator should enforce a hard stop and alert if the run is not complete by 05:30.

Treat record-level validation as data quality, not as a batch failure. Configure the import mapping with custom error handling or map into a staging entity first, then validate each product in a microflow. If UnitOfMeasure is empty, do not create or update the product master record. Instead, create an IntegrationError row with the SAP material number, page number, raw XML or a bounded payload excerpt, error type, timestamp and SyncRun reference. Commit the error row and continue with the next product. Keep a separate count on SyncRun for received, created, updated, skipped and failed. At the end of the run, produce a report grouped by error type and expose it to the business, not just to the administrator. Send one summary notification rather than one email per bad record, and include a CSV export for the SAP team. If the same material number fails three nights in a row, escalate it. Do not silently default the unit of measure, because that corrupts downstream pricing and inventory. Also validate that the error table does not grow without retention; archive or delete resolved errors after an agreed period. This approach lets 97% of the catalog sync while giving the business an actionable exception queue.

The restarts point to accumulated objects and large XML strings, not to the database size. Process one page at a time and scope variables inside the page worker so the previous page can be garbage collected. Avoid retrieving all products into a list for comparison; use indexed retrieve by material number. If the import mapping produces a large intermediate list, map in chunks or use a staging table with a bounded page. Do not log full XML for every product; log only failures with a size limit. Check the task queue settings and the scheduled event overlap: if a run takes longer than expected and the next scheduled event starts, two runs can double memory. Use a lock or check for an active SyncRun before starting. Increase the timeout to 60 seconds rather than retrying a 30-second timeout repeatedly, because a retry after a timeout may create duplicate work. Monitor heap, garbage collection and active SyncRun duration. If memory still spikes, move the XML parsing to a Java action that streams or releases the document after mapping, but only after measuring. A 2 GB heap should handle 5,000 records per page if the page is committed and discarded; it will not handle nine pages plus 45,000 domain objects in one transaction.