Data truncation occurs when incoming data exceeds a destination column or configured field length. Mistake #22 is treating it as harmless and discarding the failing rows: the default SSIS behavior fails the component instead, while ignoring failures can silently lose data. section 6.1
Use an explicit handling rule for every data flow:
- Choose separate behavior for errors and truncations on the source and destination Error Output pages. section 6.1
- Keep failing rows visible when they require investigation instead of silently ignoring them. section 6.1
- Route rejected rows to an error table when the load must continue and failures must remain reviewable. section 6.1
Retaining load failures
A staging load can store rejected-row details in staging.ImpressionLoadFailures, including the source output, ErrorCode, and ErrorColumn, rather than throwing the evidence away. section 6.1
Listing 6.2 Creating an error table
CREATE TABLE staging.ImpressionLoadFailures ( FlatFileSourceErrorOutputColumn VARCHAR(MAX) NOT NULL, ErrorCode VARCHAR(MAX) NOT NULL, ErrorColumn VARCHAR(MAX) NOT NULL ) ;
Use the error-table definition as a concrete pattern for preserving truncation and other load-failure details. section 6.1
