Derived column truncation error

Ali Ahad 151 Reputation points

I am getting following truncation error message in the derived column transformation:

The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Outputs[Derived Column Output].Columns[DESCRIPTION]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (56) failed with error code 0xC020902A while processing input "Derived Column Input" (57). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

I am using the following expression for the derived column

(DT_STR,8000,1252)(DT_TEXT,1252)[Description]

👁 Screenshot 2026-06-02 224811

Any help would be appreciated.

Thanks,

Ali.

  1. Deepesh Dhake 165 Reputation points

    For large LOB data, the reliable options are:

    1. Keep full text (recommended) : replace the expression with:

    (DT_TEXT,1252)[Description]

    The output column becomes DT_TEXT. Map it to a varchar(max)/text destination.

    1. Allow silent truncation to 8000 chars : keep your current expression, then open the Advanced Editor then Input/Output Properties then Derived Column Output then Columns then DESCRIPTION then set TruncationRowDisposition to RD_IgnoreFailure. Truncates at 8000 bytes (= 8000 chars under codepage 1252). Use only if data loss is acceptable.
  2. Akhil Gajavelly 1,830 Reputation points • Microsoft External Staff • Moderator

    Hi @Ali Ahad ,

    The screenshot confirms that the derived column is defined as DT_STR(8000). If any value in [Description] exceeds 8,000 characters, the conversion from DT_TEXT to DT_STR(8000) will trigger the truncation error you're seeing.

    Before choosing a solution, it would be useful to determine whether any rows are actually longer than 8,000 characters. For example:

    SELECT MAX(DATALENGTH([Description])) AS MaxBytes FROM YourTable;

    If the maximum length exceeds 8,000 bytes, then:

    • Use (DT_TEXT,1252)[Description] and load into a varchar(max) destination if you need to preserve the full text.
    • Keep the current cast and set TruncationRowDisposition = RD_IgnoreFailure only if truncating values beyond 8,000 characters is acceptable.

    Could you confirm the destination column data type and whether values longer than 8,000 characters need to be retained? That will help determine the appropriate approach.

    Thanks,
    Akhil.

  3. Akhil Gajavelly 1,830 Reputation points • Microsoft External Staff • Moderator

    Hi @Ali Ahad ,

    Just following up to see if you were able to check the maximum length of the values in the Description column and verify the destination column data type.

    Based on the information provided, the truncation error is most likely caused by values exceeding the DT_STR(8000) limit during conversion. If preserving the full text is required, using a large-object data type such as varchar(max) would be the recommended approach. If truncation is acceptable, the package can be configured accordingly.

    Please let us know your findings so the community can assist further if needed.

    Thanks,
    Akhil.

Sign in to comment

1 answer

  1. AI answer

    The error occurs because the Derived Column is configured to fail on truncation and the cast from DT_TEXT to DT_STR,8000 is truncating some rows.

    In SSIS, truncation is treated separately from hard errors. When a truncation happens and the column’s TruncationRowDisposition is set to Fail Component, SSIS raises DTS_E_INDUCEDTRANSFORMFAILUREONTRUNCATION (0xC020902A) and stops the Data Flow.

    To resolve this, use one of these options:

    1. Increase or change the data type
      • If the destination can store longer text, avoid truncation by:
        • Using a Unicode/text type at the destination (e.g., DT_TEXT/NTEXT equivalent) instead of DT_STR(8000).
        • Or increasing the length of the target column so it can hold the maximum length of [Description].
    2. Allow truncation but keep the row
      • In the Derived Column editor, open the Configure Error Output dialog for the Derived Column component.
      • For DESCRIPTION, set Truncation to Ignore failure.
      • This keeps the row and silently truncates the value to 8000 characters.
    3. Redirect truncated rows for inspection
      • In Configure Error Output, set Truncation to Redirect row for DESCRIPTION.
      • Connect the error output to another destination (e.g., a table or flat file) to capture rows where truncation occurs and review them.

    These behaviors (Fail Component, Ignore Failure, Redirect Row) are the standard SSIS options for handling truncations and are what control whether the Derived Column fails when truncation happens.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer