Troubleshoot Transact-SQL ingestion errors with error files

Applies to: ✅ Warehouse in Microsoft Fabric

This article describes how to troubleshoot ingestion failures in T-SQL ingestion patterns.

Ingestion into a warehouse using COPY INTO, BULK INSERT, OPENROWSET function in CTAS, INSERT, UPDATE, and MERGE statements can fail for several reasons. Source file values might not match the table schema. Required values might be missing. Ingestion options might also be misconfigured.

This troubleshooting guide uses the rejected-row diagnostics information to resolve failures, capture row-level errors, and inspect rejected rows with error metadata.

By examining the error files generated by COPY INTO and other ingestion commands, you can pinpoint exactly which rows failed to ingest and why. This information helps you identify data quality issues or adjust ingestion settings, fix the source data, and rerun the load with confidence.

Create the target table

Before running ingestion commands, create a destination table with strict types and NOT NULL constraints so you catch conversion and data-quality problems early.

1. In your Warehouse workspace, open your warehouse.

2. On the Home tab, select New SQL query.

   

3. Run the following statement:

   DROP TABLE IF EXISTS dbo.TaxiTrips;
   GO
   CREATE TABLE dbo.TaxiTrips
   (
       vendorID         int    NOT NULL,
       startLat         float  NOT NULL,
       startLon         float  NOT NULL,
       endLat           float  NOT NULL,
       endLon           float  NOT NULL,
       passengerCount   int    NOT NULL,
       tripDistance     float  NOT NULL,
       fareAmount       float  NOT NULL,
       mtaTax           float  NOT NULL,
       totalAmount      float  NOT NULL
   );
   

You can use multiple supported methods, including ingesting with COPY INTO or ingesting with Transact-SQL. Choose the ingestion method that best fits your data source, format, and automation requirements. The following COPY INTO example illustrates a common ingestion pattern for loading data from external files into a table.

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
WITH ( FILE_TYPE = 'CSV' );

This statement can fail to ingest data if the source files don't match the destination table schema. Common causes include mismatched column counts, incompatible data types, or values that can't be stored in the target table. If ingestion encounters values that can't be converted to the destination schema, the statement returns an error similar to the following:

Msg 13812, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage)
for row starting at byte offset 0, column 1 (vendorID).
Underlying data description:
file 'https://....blob.core.windows.net/Files/yellow/tripdata.csv'.

This error indicates that one or more rows can't be converted to the destination column types.

Investigate errors with MAXERRORS and ERRORFILE

Use the following options to continue ingestion when the number of row-level errors is below a defined threshold and to store diagnostic details in a specified location.

• MAXERRORS sets the maximum number of tolerated row-level failures during ingestion.
• ERRORFILE specifies where the database writes rejected rows and error details.

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
WITH (
    FILE_TYPE = 'CSV',
    MAXERRORS = 10,
    ERRORFILE = 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
);

The load operation succeeds only when the number of rejected rows is lower than MAXERRORS. When errors are captured, the ingestion operation writes:

• error.jsonl for structured diagnostics
• row.csv for rejected source rows

Locate and query rejected rows

The database writes error information to a structured folder hierarchy under the configured error location. These folders help you trace a specific execution and correlate diagnostics to one ingestion statement:

ERRORFILE/
+-- _rejectedrows/
    +-- <timestamp>/
        +-- <statement_id>/
            +-- error.jsonl
            +-- row.csv or rows.jsonl

Use OPENROWSET to read the structured diagnostics in error.jsonl so you can identify which value failed, which destination column was affected, and where the failing row originated:

SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/error.jsonl'
);

The result set typically includes one row per rejected record, for example:

The error.jsonl file contains one JSON object per line. Each object includes the properties listed in the preceding table. The following table describes each property in detail.

Review rejected rows

After you review the structured diagnostic information, you can inspect the original source data that the database couldn't ingest. The rejected rows output contains copies of the source records, preserved exactly as they appeared in the input files. The rejected rows diagnostics generate files that contain only the records that failed ingestion:

• If you ingest CSV files by using COPY INTO (FILE_TYPE = 'CSV'), the rejected output includes a row.csv file. This file matches the source file structure and contains the original CSV rows with invalid values.
• If you ingest JSONL files by using OPENROWSET(FORMAT = 'JSONL'), the rejected output includes a row.jsonl file. This file preserves the original JSON objects that caused ingestion failures.

Use these files to validate the root cause of the errors, such as malformed values, unexpected NULL values, or header rows that were incorrectly parsed as data.

SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/row.csv'
);

The row.csv schema matches the source CSV shape, and it contains only rows that failed ingestion.

Example rejected-row output:

Based on this diagnostic information, you can identify the following ingestion issues:

• The header row in the source file is mistakenly parsed as a data row. To resolve, the COPY INTO statement should use the FIRSTROW = 2 option.
• A row in the source file for the vendorID column (C1) contains NULL values, but the corresponding column in the destination TaxiTrips table is defined as NOT NULL.
• A row in the source file for the passengerCount column contains an invalid value (N/A) that can't be converted to the destination int column.

Fix ingestion issues and re-ingest data

After you identify the cause of ingestion failures, correct the issue and re-ingest the affected data. The remediation approach depends on where the error originates.

Fix the destination table schema

If the source data doesn't conform to the destination table schema, update the table definition. Common fixes include changing column data types or removing restrictive constraints such as NOT NULL.

In some scenarios, you might need to drop and recreate the destination table before re-ingesting the data.

Correct source data and re-ingest files

If ingestion fails due to invalid or inconsistent values in the source files, correct those values and re-ingest the data. For example, replace placeholder values such as N/A with empty values or valid defaults.

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/tripdata_corrected.csv'
WITH ( FILE_TYPE = 'CSV' );

When re-ingesting corrected data, use an explicit file path that points to the new file containing only corrected data, rather than a folder path that references the original files. This approach prevents re-ingesting rows that were previously loaded successfully and avoids duplicate data.

Reprocess rejected rows by using a staging table

You can load rejected rows into a staging table, fix the data by using Transact-SQL data modification statements, and then re-ingest the corrected rows.

The following CREATE TABLE AS SELECT statement loads rejected rows into a table for further processing:

CREATE TABLE TaxiTrip_RejectedRows AS
SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/row.csv'
);

After you correct the data, insert the cleaned rows into the destination table.

Related content

• Ingest data using COPY INTO
• Ingest data using Transact-SQL