Received an error in an SSIS Package with a Script Transformation. The error was “The value is too large to fit in the column data area of the buffer.” and Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException.
The Script Component in the Data Flow was taking a large column of data (about 180 characters in width) and parsing it to multiple output columns, similar to:
Column A: DT_STR(10)
Column B: DT_STR(2)
Column C: DT_STR(8)
and so on..
Well, SSIS doesn’t like that. At least not in SQL Server 2005 SP3. Even though you may be truncating the string values appropriately, SSIS doesn’t like that in the Buffer.
The workaround: SET ALL OUTPUT COLUMNS TO THE MAXIMUM WIDTH OF YOUR INPUT. In my case that was 180. So we’d have:
Column A: DT_STR(180)
Column B: DT_STR(180)
Column C: DT_STR(180)
When that component is done doing its magic, you can reduce the output in another component.
