Troubleshoot truncate metadata issues in SSIS

SSIS PowerPack Troubleshooting
,
1

Problem

When loading data in SSIS (ZappySys source components and Upsert Destination), you may see one of these errors:

Source component truncation error:

[CSV Source (Web API or File) [2]] Error: System.Exception: Truncation error with column [ColumnName] (Read full >> copy to notepad)

Error: The value is too large to fit in the column data area of the buffer. >> Source: ColumnName, Actual Length: 100, Value: XXXXXXXX

---> Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

Destination conversion error:

[Upsert Destination (Insert, Update, Delete) [206]] Error: System.Exception: Exception has been thrown by the target of an invocation. 
The given value 'XXXXXXXX' of type String from the data source cannot be converted to type nvarchar for Column 3 [ColumnName].

Cause

Both errors are usually caused by metadata mismatch:

  1. Source column length is too small for actual incoming data.
  2. Source/destination data type does not match the expected type.
  3. Metadata was scanned with a narrow sample and produced a smaller string length.
  4. Destination nvarchar(n) size is smaller than incoming text.

Solution

Use one of these two methods to fix source truncation errors.

Method 1: Increase length/datatype in source component

  1. In Data Flow, double-click the source component that failed.
  2. Go to the Columns tab.
  3. Increase column length and/or change data type for affected columns (for example, ColumnName).
  4. After adjusting metadata, enable Lock for columns you changed so future refresh does not undo your manual settings.
  5. Optional: Click Refresh Columns and rescan using a wider Metadata Scan Mode.
    CSV source - Column tab configuration
    CSV source - Column tab configuration790×593 85.8 KB

Method 2: Redirect error rows (or ignore)

  1. Drag the red error output arrow from the source component to another downstream component.
  2. When prompted, set Truncation Action to Redirect to capture failing rows.
  3. If you do not need those rows, you can set Truncation Action to Ignore.
  4. For more information, check our article about handling metadata Issues
    Redirect rows
    Redirect rows480×197 10.8 KB

Solution (Upsert Destination)

  1. Check the target table column definition (ColumnName) and confirm datatype/size.
  2. Increase destination length if source values are larger.
  3. If required, adjust source output datatype to match destination.
  4. Refresh metadata/mappings in Upsert Destination after schema changes.
  5. Retest with sample rows, then run full load.

Example SQL to increase destination column size:


ALTER TABLE dbo.YourTableName

ALTER COLUMN ColumnName NVARCHAR(200);

Best practice

To avoid recurring issues:

  1. Use wider metadata scan mode when source data is variable.
  2. Lock important source columns after manual datatype/length changes.
  3. Keep source and destination data types aligned.
  4. Redirect error output during testing to quickly isolate bad rows.

Need more help?

If the issue persists, contact ZappySys Support: