Fix it now
A value in the pipeline would not convert into the type the data flow expects. The published message is DTS_E_FAILEDCOLUMNDATACONVERSIONSTATUS, and it names the column, a status value and a status text – the status text is the part that tells you what went wrong.
SELECT MAX(LEN(SomeText)) AS max_chars,
MAX(DATALENGTH(SomeText)) AS max_bytes
FROM staging.Source;
- Read the component and the column the message names, then read the status text it quotes. That sentence, not the status number, is the diagnosis.
- For a flat file source, open the connection manager’s Advanced page and check OutputColumnWidth for that column. Flat file columns take their width from the file they were configured against and keep it.
- Check the type as well as the width. A
DT_STRcolumn cannot carry characters outside its code page;DT_WSTRcan. - To see the offending row rather than guess, set the component’s error output to Redirect Row and land the rejected rows, with ErrorCode and ErrorColumn, in a table.
- Refresh the source component after any metadata change and open each downstream component to confirm it picked the change up.
ErrorColumn is set to zero when the failure affects the whole row rather than one column – a failed lookup, for instance – so do not go hunting for a column with lineage ID 0.
If the widened column or the corrected code page clears it, you are finished. If the failure is at the destination rather than in the pipeline, the next section separates the two.
Why it happens
A data flow is strongly typed end to end. Every column carries a type, a length and, for non-Unicode strings, a code page, and the pipeline validates all of that before a single row moves. Sources capture their metadata when you configure them: a flat file source samples the file, a database source reads the schema. From then on the package carries its own copy. If the file starts containing longer values, or a table column is widened underneath it, the package does not notice until a row will not fit.
0xC02020A1 is the conversion itself failing. The published message form is Data conversion failed. The data conversion for column "..." returned status value N and status text "...", and the status text is where the useful sentence lives – typically that the text was truncated, or that one or more characters had no match in the target code page. Microsoft publishes the message form but no table mapping the status numbers to meanings, so read the text rather than decoding the number.
Two sibling codes are easy to confuse because they differ by one digit. 0xC020902A is DTS_E_INDUCEDTRANSFORMFAILUREONTRUNCATION – the component failed because truncation occurred and the truncation row disposition says fail on truncation. 0xC0209029 is DTS_E_INDUCEDTRANSFORMFAILUREONERROR, the same sentence for the error disposition rather than the truncation one. Neither is a conversion failure in itself; both mean a disposition turned a row-level problem into a component failure, which is exactly what Redirect Row exists to change.
The source column metadata is narrower than the data
You have this one if A flat file source, and the failing column holds values that have grown since the package was built.
- Open the flat file connection manager, go to Advanced, select the column and set OutputColumnWidth to a realistic value.
- Refresh the source component so the change propagates, then open every downstream transformation and confirm it reports no metadata warning.
- Where the file format is agreed with a supplier, set the widths from that agreement rather than from a sample of one file.
Changing a width in the connection manager does not always update every downstream component. Opening each one after the change is the only reliable check.
A code page problem rather than a length problem
You have this one if The failures are on rows containing accented or non-Latin characters, while ordinary rows load fine, and the status text mentions characters with no match in the target code page.
- Change the source column to a Unicode string type so the characters survive the pipeline intact.
- If the destination column is non-Unicode, decide honestly whether it should be. Converting into a code page that cannot represent the data loses information rather than shortening it.
- Where a conversion is genuinely required, add a Data Conversion transformation and map it explicitly instead of relying on an implicit one.
- For a flat file, confirm the code page on the connection manager matches the encoding the file was actually written in.
The destination column is the narrow one
You have this one if The data loads into staging without complaint and fails going into the final table, or 0x80040E21 appears at the destination.
- Compare the pipeline column definition with the table column definition side by side: type, length, precision, scale and nullability.
- Widen the table column, or shorten the value in the pipeline deliberately if that is genuinely acceptable.
- Check for a numeric precision or scale mismatch, which produces the same class of failure with no string involved.
0x80040E21 is the provider’s own text – Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. It names nothing, so treat it as a prompt to compare the two definitions field by field.
Fast load hides which row failed
You have this one if The destination reports a batch failure with no indication of the offending row.
- Set the destination’s error output to Redirect Row and route the rejected rows to a table or file for inspection.
- Temporarily reduce the maximum insert commit size so failures are isolated to smaller batches, then restore it once you have found the row.
- As a diagnostic step, land the data into a staging table whose columns are all generously sized Unicode strings, then find the bad values with T-SQL.
Full reference
Which code points where
| Code | Symbolic name | What it tells you |
|---|---|---|
| 0xC02020A1 | DTS_E_FAILEDCOLUMNDATACONVERSIONSTATUS | The conversion for a named column failed, with a status value and a status text |
| 0xC020902A | DTS_E_INDUCEDTRANSFORMFAILUREONTRUNCATION | Truncation occurred and the truncation row disposition specifies failure on truncation |
| 0xC0209029 | DTS_E_INDUCEDTRANSFORMFAILUREONERROR | The same, for the error disposition rather than the truncation one |
| 0xC0202009 | DTS_E_OLEDBERROR | An OLE DB error occurred; the provider’s own code is in the message |
| 0x80040E21 | Provider text, quoted by Microsoft | Multiple-step OLE DB operation generated errors. No work was done |
Error outputs, and what they give you
Every error output carries two extra columns alongside the data. ErrorCode identifies the error; ErrorColumn holds the lineage identifier of the column that caused it. Microsoft documents that ErrorColumn is set to zero when the error condition affects the whole row rather than one column – a failed lookup is the example given – which is worth knowing before you spend twenty minutes looking for a column that does not exist.
| Disposition | Behaviour |
|---|---|
| Fail Component | The data flow task fails. This is the default for both errors and truncations |
| Ignore Failure | The condition is ignored and the row goes to the normal output |
| Redirect Row | The row goes to the error output, with ErrorCode and ErrorColumn attached |
Ignore Failure on a truncation is not a fix. It sends a shortened value down the normal output with nothing to mark it, so the load succeeds and the data is quietly wrong. If you are going to accept truncation, do it explicitly with a derived column and a documented rule.
Sizing columns without breaking the pipeline
- Buffer memory is allocated from the declared widths, not from the data, so setting every column to a huge width slows the whole data flow on every row.
- Size to the agreed file format with sensible headroom rather than to the widest value in one sample.
- Prefer
DT_WSTRoverDT_STRwhere the source encoding is uncertain; it removes the code page question entirely. - Where a supplier changes the format, treat the column widths as part of the interface and change them deliberately, not when the load next fails.
Finding the bad value in one pass
- Add an error output on the failing component and set it to Redirect Row.
- Send it to an OLE DB destination pointing at a table of the same columns, all wide Unicode strings, plus ErrorCode and ErrorColumn.
- Run the package. The rejected rows land with their values intact.
- Look at the value. In most cases the row explains the whole failure at a glance, which is faster than reasoning about metadata.
When the metadata is right and it still fails
- Check for a Lookup transformation with no match output configured; a failed lookup fails the row without naming a column.
- Check derived columns whose expression result type is inferred and narrower than the inputs.
- Check that a Data Conversion transformation is not converting to a type narrower than the destination expects.
- Where the source is a database, check for a view or a function between the table and the package whose types differ from the base table’s.
- Re-open the package after any change to a shared connection manager; metadata warnings are shown per component, not centrally.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
0xC02020A1 |
DTS_E_FAILEDCOLUMNDATACONVERSIONSTATUS: the conversion for the named column failed, returning a status value and a status text. Read the status text | Microsoft Learn |
0xC020902A |
DTS_E_INDUCEDTRANSFORMFAILUREONTRUNCATION: truncation occurred and the truncation row disposition specifies failure on truncation | Microsoft Learn |
0xC0202009 |
DTS_E_OLEDBERROR: an OLE DB error occurred, with the provider’s own error code carried in the message | Microsoft Learn |
0x80040E21 |
The provider’s own text: multiple-step OLE DB operation generated errors, no work was done. Commonly a type or width mismatch at the destination | Microsoft Learn |
Confirm the fix worked
- The package runs end to end and the destination row count matches the source row count.
- The error output table is empty after the run.
- The longest and the most heavily accented values in the destination are intact rather than shortened or replaced.
- Every component in the data flow opens without a metadata warning.
- A deliberately over-long test value is still rejected, which proves the width you set is the one in force.
Questions people ask about this
Why does the package work on one file and fail on the next?
Because the metadata was captured from the first file. A flat file source takes its column widths from what it was shown when configured, and a later file with longer values breaks that assumption.
Should I just set every column to a huge width?
It removes this error and costs you buffer memory on every row, which slows the whole data flow. Size columns to the agreed format with headroom instead.
How do I find out which value failed?
Set the component’s error output to Redirect Row and land the rejected rows with ErrorCode and ErrorColumn in a table. Guessing from the column list is slower and often wrong.
What does the status value in the message mean?
Microsoft publishes the message form – a status value and a status text – but no table mapping the numbers. The status text is the sentence that tells you whether it was a truncation or a code page problem, so read that.
Does this ever indicate a licensing problem?
No. It is a metadata mismatch inside your own package. No edition of SQL Server converts a value that will not fit.
