truncation error in ssir

Archit Puri 0 Reputation points
2026-07-02T10:15:49.17+00:00

I applied the Lookup as you suggested and the duplicate issue is resolved. However, I'm still getting truncation warnings at the OLE DB Destination, and some records are not getting inserted into RepoStyleTransactionMembers. I'm sharing the data flow screenshot. Could you please guide me what I should check next?

SQL Server Integration Services

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. kagiyama yutaka 3,925 Reputation points
    2026-07-02T10:56:58.71+00:00

    I think the fix is checking that each SSIS column length matches the RepoStyleTransactionMembers column length, and adjusting the table column when the incoming value is longer. SSIS truncation is raised only when the value exceeds the destination size.

    Was this answer helpful?

    1 person found this answer helpful.
  2. Deepesh Dhake 245 Reputation points
    2026-07-02T19:04:25.4133333+00:00

    The truncation warnings and the missing rows are probably two separate issues.

    Truncation warnings are just warnings, rows still pass through, only truncated. They come from a string column being wider in the pipeline than in RepoStyleTransactionMembers. Check column lengths in the Advanced Editor (Input and Output Properties) against the table, especially the columns your new Lookup introduces.

    Missing rows are a different problem, likely a NULL in a NOT NULL column, a numeric precision mismatch, or a constraint violation (PK/unique/FK). Wire the destination's error output to capture ErrorColumn and ErrorCode to see exactly which rows fail and why. If you're using fast load, one bad row can fail a whole batch, so switching to non-fast-load temporarily helps isolate it.

    Was this answer helpful?

    0 comments

  3. AI answer

    2026-07-02T10:16:15.88+00:00

    Truncation warnings at the OLE DB Destination mean that at least one data flow column is longer than the corresponding destination column, or the component is configured to treat truncation as an error.

    Next checks and actions:

    1. Confirm which column is truncating
      • In the OLE DB Destination Editor, open the Error Output page.
      • For each input column, set Error (and, if applicable, Truncation) to Redirect row instead of Fail component.
      • Connect the error output to a separate destination (e.g., a table or flat file) so that the rows and their values can be inspected.
      • This lets the error output capture row-level errors and shows which column and data caused the truncation.
    2. Decide how to handle truncation
      • For each column, choose one of the supported options:
        • Fail Component: Data Flow fails on truncation (default).
        • Ignore Failure: Truncation is ignored and the row continues.
        • Redirect Row: Row goes to error output for separate handling.
      • Configure this per column in the Configure Error Output dialog or the destination’s Error Output page.
    3. Align data types and lengths
      • Compare the data flow column lengths with the target table column definitions.
      • If the destination column is too short for the incoming data and truncation is not acceptable, increase the destination column length.
      • If truncation is acceptable, keep the current length and set the truncation disposition to Ignore Failure or Redirect Row so the main flow does not fail.
    4. Check upstream transformations
      • If a Derived Column or other transformation is creating longer strings (for example, concatenations), ensure the resulting data type/length matches or fits into the destination column.
      • If needed, explicitly cast to a shorter type and then configure truncation handling as above.
    5. Use error outputs consistently
      • Ensure that any component with an error output (including the OLE DB Destination) has its error output connected if rows are being redirected; otherwise, warning DTS_W_REDIRECTWITHNOATTACHEDERROROUTPUTS indicates redirected rows are being lost.

    By redirecting and capturing error rows, then aligning column lengths and truncation behavior, the truncation warnings can be controlled and the missing inserts into RepoStyleTransactionMembers identified and resolved.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.