Friday, January 31, 2014

SSIS Error Message "the value is too long to fit in the data area of the buffer"

I recently hit this issue while doing some custom script task coding for an SSIS package, and it took me a while to figure out.  I found that this specific bug can happen from two situations.

1.  The definition of a file has the input definition that is higher than the output definition in the  file connection manager.  This causes a failure in a Dataflow Source component.  

2.  The actual buffer inside of a script task is too large.

For the first issue, this can be solved by making sure that the output size is the same as the input size.

For the second part, lets describe what is causing the issue.  When using a script task it has a finite amount of bytes that the input buffer can hold.  I have no idea what this finite set is, but it is rather large (but not as large as one would hope).  Because it has a finite buffer amount, it matters how much data you try to push through it.

In the scenario I hit the issue, I was trying to put 10 NTEXT columns into the script task, manipulate them, and output nvarchar(4000) size columns.  The input buffer was getting overflowed because NTEXT is relatively unbounded, and the data source I was using had several of the 10 columns with many thousands of characters in them.  

The trick to fixing this is to create multiple script tasks to do your manipulation.  Yes, this will look a bit odd, but it is the best way to approach the solution.

So, instead of using one, easily maintained and monolithic script task, use 2 of them and do half the work in one, and half the work in the other.  For me, this meant doing 5 columns in one task and 5 columns in the next task.



No comments:

Post a Comment