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.

Pontiac

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.

Tortoise

I'm using Asynchronous input/output, and the script is to de-serialize Json data, so I don't think the output columns have something to do with it. And I just tested, it processed 160k characters Json data fine, but it failed with the 190k characters Json. – Pontiac

This answer has helped me identify my issue. It was indeed the output column that was limited to fewer characters than what was assigned to it. Thanks! – Andino

Thanks for pointing out the output column length! After adjusting the length of a few columns the error went away and the package execution was completed successfully. – Dorsum