Build a recoverable SQL Server bulk-import pipeline

A fast bulk load is only one part of a reliable import. The difficult questions arrive afterward: which rows were rejected, whether the same file already ran, and whether users can see half of a dataset. A staging design makes those questions explicit instead of burying them in one large INSERT.

Preserve the input before interpreting it

Give each import a stable batch identifier and retain the source file's identity, content hash, arrival time, and parser version. A filename alone is not sufficient because a supplier can reuse it with different content. Preserve raw fields and a source record identifier so an error can be traced back to what actually arrived.

Load into an isolated staging area rather than the live business table. BULK INSERT reads a path accessible to the SQL Server host or its configured data source, not a path on the analyst's laptop. Confirm the file-access identity and format contract before measuring throughput. Quoted delimiters, embedded newlines, encoding, and empty fields need representative test files.

Do not confuse physical text lines with logical CSV records when quoted fields can contain newlines. If precise source positions matter, use a parser that records a stable logical record number. A staging identity value is not automatically proof of the original file order.

The example starts after parsing and uses raw strings to show the validation boundary. It does not implement a complete CSV parser.

DECLARE @Raw TABLE
(
    SourceRow int PRIMARY KEY,
    CustomerText nvarchar(100),
    DateText nvarchar(100),
    AmountText nvarchar(100)
);
INSERT @Raw VALUES
(1, N'42', N'20260601', N'125.50'),
(2, N'bad', N'20260602', N'20.00'),
(3, N'43', N'20260230', N'15.00'),
(4, N'44', N'20260603', N''),
(5, N'45', N'20260604', N'-7.00');

SELECT r.*,
    TRY_CONVERT(int, NULLIF(LTRIM(RTRIM(CustomerText)), N'')) AS CustomerId,
    TRY_CONVERT(date, NULLIF(LTRIM(RTRIM(DateText)), N''), 112) AS InvoiceDate,
    TRY_CONVERT(decimal(19,4), NULLIF(LTRIM(RTRIM(AmountText)), N'')) AS Amount
INTO #Parsed
FROM @Raw AS r;

SELECT SourceRow, CustomerId, InvoiceDate, Amount,
    CASE
        WHEN CustomerId IS NULL OR CustomerId <= 0 THEN N'Invalid customer'
        WHEN InvoiceDate IS NULL THEN N'Invalid date'
        WHEN Amount IS NULL OR Amount <= 0 THEN N'Invalid amount'
        ELSE N'Accepted'
    END AS ValidationResult
FROM #Parsed
ORDER BY SourceRow;

DROP TABLE #Parsed;

Only row 1 is accepted. Row 2 has an invalid customer identifier, row 3 an impossible date, row 4 a blank amount, and row 5 a negative amount. NULLIF prevents a blank numeric field from being treated as a useful number. Style 112 expresses the chosen date contract, YYYYMMDD, without relying on session language.

Make rejection a first-class result

TRY_CONVERT separates many conversion failures from batch execution failure, but a successful conversion is not complete validation. This example accepts positive identifiers without proving that the customer exists. Join to the authorized customer set before publication. It also permits decimal rounding to the target scale; if excess fractional precision is forbidden, validate the lexical value or compare against a wider accepted representation.

The CASE returns the first error per row for readability. A production error table can store several reasons for the same record. Keep batch ID, source record ID, field, error code, and original value, subject to the retention and access rules appropriate for that data. A message saying only 'import failed' is costly to support.

Check duplicates both within the batch and against the destination's business key. Decide whether repeated rows mean rejection, replacement, or a deliberate aggregate. Do not use DISTINCT simply to make a unique constraint stop failing. It can conceal a supplier problem or discard a meaningful difference.

Reconcile counts across stages: raw records, parser failures, validation rejections, accepted records, and published records. These categories need a precise definition to avoid double counting rows with several errors. Store the summary with the batch so operations can verify completeness without reopening the file.

Publish with a defined recovery boundary

Choose whether the business requires all-or-nothing publication or permits partial acceptance. For all-or-nothing, finish validation before opening the short transaction that applies accepted data and marks the batch published. Destination constraints remain the final defense against changes that occurred after validation.

Enforce uniqueness for the batch or business operation identity so a retry cannot publish the same input twice. If a connection drops after commit, the next attempt should inspect the recorded outcome. The publication marker and destination changes must commit together.

Large imports may require bounded chunks, but chunking changes the recovery contract. Record completed chunks and make each repeatable, or keep data invisible until a final batch-state transition that every reader consistently respects. A visibility flag ignored by one reporting query is not an isolation boundary.

Benchmark parsing, validation joins, log growth, index maintenance, and publication together. Keep rejected data long enough for correction and replay, then remove it according to an explicit policy. A pipeline is successful when it can explain its outcome and resume safely, not merely when its fastest stage loads rows quickly.

Technical references: Microsoft Learn: BULK INSERT · Microsoft Learn: TRY_CONVERT.