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

sql json ssis buffer script-component
user3268139 picture user3268139 · Jun 26, 2014 · Viewed 29.2k times · Source

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.

Error

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

Answer

Vikramsinh Shinde picture Vikramsinh Shinde · Jun 26, 2014

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.

Related questions

How do I query using fields inside the new PostgreSQL JSON datatype?

I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2. Specifically, given a series of JSON records: [ {name: "Toby", occupation: "Software Engineer"}, {name: "Zaphod", occupation: "Galactic President"} ] How would I write the SQL to …

Read More
Query for array elements inside JSON type

I'm trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } I would …

Read More
Most efficient conversion of ResultSet to JSON?

The following code converts a ResultSet to a JSON string using JSONArray and JSONObject. import org.json.JSONArray; import org.json.JSONObject; import org.json.JSONException; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.ResultSetMetaData; public class …

Read More