CSV to SQL Server: make the pipeline reliable before making it intelligent

A small CSV can hide a big problem

Imagine sales delivers a file every morning containing cliente_id, date, currency and amount. One day names contain commas, another uses a semicolon delimiter and another repeats the previous batch. A process that only checks whether the file exists can finish successfully and produce incorrect information.

Define encoding, delimiter, quoting, headers, required fields, date format and null representation. Clarify whether amounts include tax and retain identifiers as text when leading zeros matter. Reading CSV requires a parser compatible with its contract, rather than splitting every line on commas.

Design three zones: received, validated and published

Assign a batch identifier and record its origin, receipt time, file hash and contract version. A hash detects repeated content; it does not prove that two differently ordered files represent different transactions. Agree on batch identity with the producing system and retain a retention policy.

Initially load into staging: an isolated table or space containing original values and the batch identifier. Validate there before publishing to the destination. A rejected row needs a reason and a recoverable reference; its content must have the same access and privacy controls as accepted data.

Check conversions without hiding invalid records

TRY_CONVERT returns NULL when a permitted conversion cannot be completed. This lets you count errors, provided you distinguish an allowed null from a missing required value. Microsoft warns that an explicitly forbidden conversion can still raise an error. [1]

-- Read-only example: staging.ventas already exists in the laboratory.
-- Supply @BatchId as a command parameter; do not concatenate SQL.
SELECT
    COUNT_BIG(*) AS rows_received,
    COUNT_BIG(CASE
        WHEN NULLIF(LTRIM(RTRIM(cliente_id)), '') IS NULL THEN 1
    END) AS empty_ids,
    COUNT_BIG(CASE
        WHEN importe_raw IS NULL
          OR NULLIF(LTRIM(RTRIM(importe_raw)), '') IS NULL
          OR TRY_CONVERT(decimal(18, 2), importe_raw) IS NULL THEN 1
    END) AS invalid_amounts
FROM staging.ventas
WHERE batch_id = @BatchId;

This example assumes text amounts already normalized to the agreed numeric format. It does not resolve local separators, higher precision or currency rules. Also test rounding to two decimal places: successful conversion does not prove that the result preserves required precision. This is an illustrative query, not a load executed against a customer database.

A retry must know what already happened

If the process loses its connection after writing, do not assume the destination remained untouched. Check the confirmed batch state and its keys before retrying. A strategy may combine a unique constraint, a batch ledger and a publication transaction. The right key depends on whether you receive events, balances or complete snapshots.

For transactional SQL operations, review XACT_ABORT and error handling for the design: with ON, a runtime error can cancel and roll back the complete transaction; this does not replace idempotency or resolve every compilation error. [2] Batch commits can also leave partially written data. Confirm these boundaries with disconnection and duplicate tests.

Where AI and Dova help

AI can summarize schema differences or group rejection reasons using sanitized diagnostics. It should suggest explanations and tests without inventing identifiers, filling unknown amounts or automatically applying changes. Compare every recommendation with reproducible records and the owner of the business rule.

Dova documents file and database components, including write modes and options whose effective availability depends on the connection and worker. [3] Design the Mapping, configure the Session and verify a laboratory Workflow before scheduling it. Monitor provides execution evidence; a successful state does not replace reconciliation. The operation guide emphasizes inspecting partial effects before recovery or reruns. [4]

Acceptance testing that supports real operation

• A file containing quotes, accented characters, a header and an invalid amount.
• Resubmission of the same batch and duplicate keys within the file.
• Disconnection after a partial write and controlled recovery.
• An unexpected column, date or decimal-format change.
• Comparison of rows, keys and amounts by currency, without combining currencies.

Measure freshness from the business event, rejection percentage, reconciled batches, duration and recovery time. Agree on targets with the consumer rather than promise universal latency. This discipline makes an everyday integration explainable and builds a reliable foundation for analytics and AI.