Logging Error Key in Fabric to CRM sync

Hi AmeyaYadav​,

As far as I know, the Copy activity does not hand back the key of the one row that failed on an upsert. Its output gives you counts (rows read, rows copied, rows skipped) and, on failure, the error message, but not the record identifier. So the usual approach is to narrow the failure down to a small batch and then log the batch.

Step 1: log the failure itself. Add a Script or Stored procedure activity on the Copy activity's On fail path:

INSERT INTO etl.SyncErrorLog (RunId, BatchKeyFrom, BatchKeyTo, ErrorMessage, LoggedAtUtc)
VALUES (
    '@{pipeline().RunId}',
    '@{item().KeyFrom}',
    '@{item().KeyTo}',
    '@{replace(activity('Copy_Upsert').error.message, '''', '''''')}',
    SYSUTCDATETIME()
);

Step 2: make the batch small enough to be useful. Instead of one big copy, use a Lookup to produce key ranges (for example 500 rows each) and a ForEach that runs the Copy for each range. The failure above then tells you which range failed.

-- Lookup activity: one row per batch
SELECT MIN(CustomerKey) AS KeyFrom, MAX(CustomerKey) AS KeyTo
FROM (
    SELECT CustomerKey, (ROW_NUMBER() OVER (ORDER BY CustomerKey) - 1) / 500 AS BatchNo
    FROM dbo.CrmSyncSource
) t
GROUP BY BatchNo;

-- Copy source query inside the ForEach
SELECT * FROM dbo.CrmSyncSource
WHERE CustomerKey BETWEEN @{item().KeyFrom} AND @{item().KeyTo}

Step 3 (only for failed batches): isolate the record. Re-run the failed range with a batch size of 1, either with a second ForEach over the keys in that range or by running the same Copy with the write batch size set to 1, so the error message points at a single key. If you want to avoid the extra loop, a common alternative is to validate the data before the sync (required fields, lookup values that must exist in CRM) in a query that writes bad rows to the log table, and only upsert the clean rows.

It's also worth checking the Copy activity's fault tolerance and logging options on the Settings tab, to see whether your CRM sink supports skipping bad rows and writing them to a log location. I haven't verified which sinks support it, so test it before relying on it.

So my overall approach would be:

Lookup -> key ranges (e.g. 500 rows)
ForEach range -> Copy (Upsert)
  On fail -> insert RunId + key range + error message into the log table
Failed range -> rerun at batch size 1 to find the exact key
Optionally -> pre-validate rows and log invalid ones before the upsert

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

With a Microsoft Fabric Copy Data activity using Upsert, you normally can't retrieve the key of the exact failed source record directly from the activity's failure output. The failure output gives you the activity-level error, but not necessarily the business key of the row that caused the CRM Upsert to fail.

Best option if supported by your CRM sink: enable fault tolerance/session logging

Configure Copy Data to skip incompatible rows and enable logging. Microsoft documents that Copy activity can continue after incompatible rows and write information about skipped data to its session log.

Check that your particular CRM connector supports this behavior before designing around it.