Travel expense master data sync fails on employee import with duplicate key errors

ERP Infor CloudSuite
, , , , , , , ,
1

We’re experiencing critical issues importing employee master data from our HR system into CloudSuite travel management. The Data Import Utility consistently fails with primary key constraint violations when processing employee records.

The error occurs during the employee master data import phase:

ERROR: duplicate key value violates unique constraint "emp_master_pk"
DETAIL: Key (employee_id)=(EMP-2847) already exists
CONTEXT: Import batch processing line 1247

This is blocking our entire expense reporting workflow as employees can’t submit travel expenses without valid master data records. We’ve checked the source HR system and confirmed no duplicate employee IDs exist there. The issue seems to occur when re-importing updated employee information.

Has anyone dealt with primary key conflicts during employee data synchronization? What data cleansing steps should we implement before the sync process?

system 2

Let me provide a comprehensive solution addressing all three critical aspects of your employee master data import issue.

1. Employee Master Data Import Configuration

First, modify your Data Import Utility definition to handle both inserts and updates:

<importDefinition operation="merge" matchKey="employee_id">
  <updateMode>upsert</updateMode>
  <conflictResolution>sourceWins</conflictResolution>
</importDefinition>

The “merge” operation with “employee_id” as the match key ensures the utility checks for existing records before attempting insertion.

2. Primary Key Constraint Violation Resolution

Your constraint violations stem from three common sources:

  • Residual records from failed imports in staging tables
  • Missing or incorrect match key configuration
  • Lack of pre-import validation

Before each import cycle, execute this cleanup process:

  • Query EMP_MASTER_STAGING for records with status=‘FAILED’
  • Archive these records to an audit table
  • Truncate the staging table before fresh import
  • Validate source data against existing employee_id values

3. Data Cleansing Before Sync

Implement a three-stage validation pipeline:

Stage 1 - Source Data Validation: Before extraction from HR system, validate that employee_id values are unique, properly formatted (matching your EMP-#### pattern), and contain no null values.

Stage 2 - Pre-Import Reconciliation: Query CloudSuite to identify existing employee records. Create a reconciliation report showing:

  • New employees (INSERT required)
  • Modified employees (UPDATE required)
  • Unchanged employees (SKIP to optimize performance)

Stage 3 - Staging Table Preparation: Load data into staging with operation flags (I/U/S) based on reconciliation results. This prevents the import utility from making decisions at runtime.

Implementation Steps:

  1. Create a pre-import validation script that queries both source and target:

    • Identifies duplicates within the import file itself
    • Checks for existing employee_id values in CloudSuite
    • Flags records appropriately

  2. Update your import job to use merge mode with proper conflict resolution

  3. Add post-import validation to verify all records processed successfully

  4. Implement error logging that captures the specific employee_id and reason for any failures

Expected Results: This approach will eliminate primary key violations by ensuring the system knows exactly which operation to perform for each record. Your expense reporting workflow will remain unblocked because failed individual records won’t halt the entire batch. The data cleansing pipeline ensures only validated, properly flagged records reach the import utility.

I recommend running a pilot import with 100 employee records to validate the configuration before processing your full employee master data set.

This draft is based on general Infor CloudSuite knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

I’ve seen this before. The issue is likely residual records from previous failed imports. CloudSuite doesn’t automatically clean up partial imports when the process errors out. You need to query the staging tables to identify orphaned records that are blocking your fresh import.

4

Check your import configuration for the “Update Existing Records” flag. If it’s set to INSERT mode instead of UPSERT, the utility will try to create new records even when employee IDs already exist. Also, verify that your employee master data includes the proper update timestamps. The system uses these to determine whether to insert or update. Without proper timestamping in your source data, CloudSuite can’t distinguish between new employees and updates to existing ones, leading to these duplicate key violations.

danieldata 5

Tested this on Infor CloudSuite Data Import Utility with merge operation and upsert mode, and duplicate key errors on employee_id completely disappeared during travel expense sync.

7

Thanks both. I checked and we are using INSERT mode. However, I’m confused about how to switch to UPSERT mode in the Data Import Utility. Is this a configuration setting in the import definition, or do we need to modify the import script itself?

8

The UPSERT behavior is controlled in the import definition XML. Look for the operation attribute in your employee master import template. You’ll want to set it to “merge” rather than “insert”. This tells the utility to update existing records instead of failing on duplicates.

9

Beyond the UPSERT configuration, you really need a data cleansing pre-process. We implemented a validation layer that runs before the actual import. It queries the target system to identify existing employee IDs, then marks records in the staging area as either INSERT or UPDATE operations. This approach reduced our import failures by 95%. The key is implementing proper data governance upstream rather than relying solely on the import utility’s error handling.