SSIS Fast Load and the Silent Truncation Trap

Why This Pattern Matters

Fast load is the default and the right choice for OLE DB destinations in most ETL packages. It batches inserts, plays to the strengths of SQL Server bulk operations, and is dramatically faster than row by row. There is one trap that catches teams off guard: in fast load mode, a value that exceeds the destination column width is silently truncated and the package still reports success. Row by row mode pushes the offending row to the error pipeline. The trade off is performance versus visibility.

For Biml generated projects, the implication is the same as for hand authored ones, with the added wrinkle that a generator producing hundreds of fast load packages may hide truncation across the whole project unless the generator template plans for it.

Reproducing the Issue

A small test isolates the behavior. Create a target table with a tight column width and an error capture table that can hold the full value:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.WidthTest;
DROP TABLE IF EXISTS dbo.WidthTest_error;

CREATE TABLE dbo.WidthTest
(
  WidthTestKey INT IDENTITY(1,1),
  WidthTestLabel VARCHAR(5)
);
GO

CREATE TABLE dbo.WidthTest_error
(
  WidthTestKey INT,
  WidthTestLabel VARCHAR(1000)
);
GO

A simple OLE DB Source feeds three rows of varying length:

SELECT '123'       AS WidthTestLabel UNION ALL
SELECT '12345'                       UNION ALL
SELECT '123456789';

The destination is the 'WidthTest' table.

Fast Load Result

With fast load enabled, the package shows three rows transferred and reports success. Querying 'WidthTest' shows three rows present, but the third row has been silently truncated to '12345'. The original '123456789' is gone. The data flow designer issues a truncation warning at design time, but at runtime no error is raised, no row is redirected, and the package finishes green.

The error redirect option for truncation is greyed out in the OLE DB Destination editor when fast load is in use. This is by design: at the destination level, the bulk insert mechanism reports the batch as a unit and individual row failure information is not available for the truncation case.

Row by Row Result

Switching the destination Data Access Mode to non fast load (table or view, no Fast Load) and wiring the error output to 'WidthTest_error' produces the expected outcome: two rows reach the destination, one row reaches the error table with the full original value preserved. The package still succeeds, but the truncated row is visible.

The Trade Off

Fast load is faster, often dramatically so. Row by row catches truncation. The options when designing a load are:

1. Accept fast load and live with silent truncation
2. Use row by row and absorb the performance cost
3. Tighten the OLE DB Source output buffer width to match the destination, so the source reports truncation before the destination sees it
4. Use derived columns or conditional splits in the data flow to validate width before insert
5. Use fast load and cover truncation with automated tests on the loaded data

Option three is appealing because the source pipeline can redirect truncation errors. It works when the generator can determine source and destination widths from the same metadata.

Option five is the typical choice for high volume migrations where performance is the priority. The packages keep using fast load and a separate test pass compares loaded values against source lengths.

Implications for Biml Generators

A Biml template that defaults every OLE DB Destination to fast load needs an answer for truncation built in. Two options:

• Generate fast load packages and a parallel set of validation queries derived from the same metadata. The validators check that no destination column shorter than the source got rows in.
• Generate fast load by default and switch to row by row for tables flagged in metadata as needing per row error redirect, for example tables where source widths exceed destination widths.

Both options keep the framework template simple and put the policy in metadata.

When to Use What

The default for new projects should be fast load with width validation, not row by row. Fast load almost always wins on performance and modern frameworks have other ways to catch truncation. Row by row is appropriate for small lookup loads, error pipeline development, and any case where individual row validation matters more than throughput.

The trap is assuming that a green SSIS package means the data is intact. With fast load, that assumption does not hold for column width.