Finding the One Row That Broke Your Data Load

A data load error becomes easier to fix when you can identify the original row and the rule it violated. Preserve the input before conversion so the failure does not erase its own evidence.

Keep the Raw Value and Its Location

A message about converting text to a number does not tell you which source value caused it. Land suspicious fields as text when the source format is unreliable. Retain a batch identifier and a stable source row reference.

For a file, that reference might be a logical record number rather than a physical line number. Quoted fields can contain line breaks. Keep enough source context to find the record again without guessing.

CREATE TABLE #RawLoad
(
    SourceRowId int PRIMARY KEY,
    AmountText nvarchar(50) NULL,
    DateText nvarchar(50) NULL
);
INSERT #RawLoad VALUES
 (1, N'12.50', N'2026-01-01'),
 (2, N'twelve', N'2026-01-02'),
 (3, N'', N'2026-02-30'),
 (4, NULL, N'2026-01-04');

The example intentionally contains several kinds of invalid input. Run the following checks in the same session. The aim is to report each failing record, not to stop at the first error message.

Separate Missing From Unconvertible

TRY_CONVERT returns NULL when a supported conversion fails. A source NULL also produces NULL, so distinguish those cases before labeling the rejection. Normalize empty text explicitly when your contract treats it as missing.

SELECT SourceRowId, AmountText,
       CASE WHEN NULLIF(LTRIM(RTRIM(AmountText)), N'') IS NULL
            THEN N'Missing amount'
            WHEN TRY_CONVERT(decimal(12,2), AmountText) IS NULL
            THEN N'Invalid amount' END AS rejection_reason
FROM #RawLoad
WHERE NULLIF(LTRIM(RTRIM(AmountText)), N'') IS NULL
   OR TRY_CONVERT(decimal(12,2), AmountText) IS NULL;

Some explicitly disallowed conversions still raise errors, so TRY_CONVERT is not a universal exception handler. Match the target type, precision, and scale to the real load. A successful conversion can still round a value or violate a business rule.

Test Each Field With Its Actual Rules

Use a defined date format rather than relying on the session's regional settings. Then apply business checks separately from type conversion. A valid date can still be outside the allowed reporting period.

SELECT SourceRowId, DateText
FROM #RawLoad
WHERE NULLIF(LTRIM(RTRIM(DateText)), N'') IS NULL
   OR TRY_CONVERT(date, DateText, 23) IS NULL;
SELECT SourceRowId, AmountText
FROM #RawLoad
WHERE TRY_CONVERT(decimal(12,2), AmountText) < 0;

Also inspect target constraints, string lengths, and duplicate keys. The conversion may be valid while the INSERT fails for another reason. Keep the original error number and the target object alongside your row-level findings.

Narrow Large Inputs With Stable Boundaries

When a tool reports only a failing batch, divide that fixed batch into smaller ranges. Test each range and continue with the failing portion. This can be useful when a row-level error output is unavailable.

DECLARE @FirstRow int = 1, @LastRow int = 2;
SELECT SourceRowId, AmountText, DateText
FROM #RawLoad
WHERE SourceRowId BETWEEN @FirstRow AND @LastRow
ORDER BY SourceRowId;

Use a stable source snapshot and key-based ranges. Arbitrary TOP queries without ordering do not define repeatable halves. If several rows are bad, finding one does not establish that the rest are valid.

Do not replay destructive target operations just to locate the failure. Use a staging or validation path whenever possible. If the behavior depends on the target, reproduce it in an approved test copy.

Redirect Errors Without Hiding Them

Many load tools can redirect rejected rows to a separate output. Configure that route to retain identifiers, raw values, and the failing field or rule. A rejection count without the corresponding records is weak evidence.

Avoid silently replacing failed conversions with NULL and then loading them as acceptable values. That changes an explicit failure into missing information downstream. Decide whether the batch stops or valid rows continue under a documented partial-load policy.

The accepted and rejected outputs should reconcile with the input. Store the rule version when validation changes over time. Otherwise, a later retry may appear inconsistent even though the policy changed.

Report a Repairable Problem

A useful error report names the batch, source row, field, rule, and responsible owner. Include a protected sample only when needed to diagnose the issue. Sensitive values do not belong in widely distributed logs.

After correction, retry the identified records through the normal validation path. Ensure that already accepted rows are not duplicated. The final check is a reconciled load, not merely a quieter error log.

A useful load error is not just a message, it is a row and a reason.

This post was rewritten from scratch in September 2026. The original, published on 2010-11-30, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.