SSIS - The value is too large to fit in the column data area of the buffer

Question

I'm passing a column of Json data to the script component to process. It went fine until I had a Json data that contains over 600,000 length, then the follow error occurs.

I did change the MaxBuffer size to 10MB, and my data is only around 600K but it still doesn't work, please advice.

Answer 1

There are few things you need to check -

If you are using string that is assigned to output column, go to the property of the output column and set it's length to a higher value. Set the size of the string in the output column to be bigger than that of the original string. Also compare the sizes of the input and output columns of the script task ( right click -> show advanced editor… ) and find input columns that are greater than the output columns. Pay attention to column size in the Outputs section of the Script Component.

If you are having parent-child packages, then please note Pipeline Buffers are not shared between child and parent packages, so you need to set properties accordingly.

To check where truncation happening, try implementing DoesNotFitBufferException.

If you are using SQL Server 2005, then it's worth to have look to this thread.