SSIS on SQL Server 2008R2 "The value is too large to fit in the column data area of the buffer."

Question

Post reply

SSIS on SQL Server 2008R2 "The value is too large to fit in the column data area of the buffer."

gbritton1

SSCertifiable

Points: 6522
More actions
July 28, 2014 at 1:23 pm

#296750

Hi -- stuck on this problem. I have a package which takes in records like:
Type Data
H .....
D ..... lots of data ...
T .....
The flow is like this:
FlatFile input -> Conditional Split (based on first column) to three script components, one for the H (header), one for the detail (D) and one for the trailer (T)
This *was* working until I got a new requirement to be able to handle longer detail records. So, starting at the Flat File input right through to the SQL Server table, I changed every reference for the Data field to the new (longer) length. Since then, the package fails when I send longer (but within the new limit) input rows. I get this sort of error:
Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Parse Detail columns" (112) failed with error code 0x80131600 while processing input "Input 0" (114). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure
I've checked, double-checked, triple-checked then checked again that I have matching column lengths for the Data column from the Flat File input through to the SQL Server table. Each DataFlow task has the same length (DT_STR length 400) which matches SQL Server exactly.
I can't get past this problem. I've googled 'til the comes came home but no luck.
BTW the script component fails just before it starts executing. I inserted a Console.Writeline("here we are"); as the first stmt in the ProcessInput method, but never saw a line on the console. It just didn't get there.
Has anyone else run into this? If so, how did you solve it?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

I would copy your .NET code, delete the script component and add it back again.

You never know 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Check to make sure they didn't alter your row terminators. IE: CR/LF became LF. I usually see this when an entire document loads up as a single line.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I like Koens suggestion.

I have seen that error due to receiving too many columns for the defined script component or for receiving data too large for the output defined in the script component.

One other possible contender is if there is a derived column transformation after the script component. I just had one blow up with errors in the script component but the real problem was in the derived column metadata.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gbritton1 SSCertifiable Points: 6522 More actions · Answer 4

Koen Verbeeck (7/28/2014)
I would copy your .NET code, delete the script component and add it back again.
You never know 🙂

I went even further: I disabled the current dataflow then created a new one using the same building blocks (I created them again from the toolbox). I copied the few lines of C# that do the work in the script component to the new script component in the dataflow. Then, I ran the package using the new dataflow task.

Alas I received the same error 🙁

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

Evil Kraig F (7/28/2014)
Check to make sure they didn't alter your row terminators. IE: CR/LF became LF. I usually see this when an entire document loads up as a single line.

That's a good thought. I checked it out, both the successful inputs and failed inputs terminate their lines with a single LF (No CR)

gbritton1 SSCertifiable Points: 6522 More actions · Answer 6

SQLRNNR (7/28/2014)
I like Koens suggestion.
I have seen that error due to receiving too many columns for the defined script component or for receiving data too large for the output defined in the script component.
One other possible contender is if there is a derived column transformation after the script component. I just had one blow up with errors in the script component but the real problem was in the derived column metadata.

In my case, the script component receives just one column. I've included screenshots of the input and output columns

Also there is no DC transform after the script. It goes straight to an RC and that's it.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Can you right-click on the arrow before the script component, go to metadata and check the data type of the Data column.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gbritton1 SSCertifiable Points: 6522 More actions · Answer 8

Koen Verbeeck (7/29/2014)
Can you right-click on the arrow before the script component, go to metadata and check the data type of the Data column.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Is it possible to post the .NET code?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gbritton1 SSCertifiable Points: 6522 More actions · Answer 10

Koen Verbeeck (7/29/2014)
Is it possible to post the .NET code?

Sure! Though note that the DataFLow task fails just before this code begins to execute:

/* Microsoft SQL Server Integration Services Script Component

* Write scripts using Microsoft Visual C# 2008.

* ScriptMain is the entry point class of the script.*/

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

public override void PreExecute()

{

base.PreExecute();

/*

Add your code here for preprocessing or remove if not needed

*/

}

public override void PostExecute()

{

base.PostExecute();

/*

Add your code here for postprocessing or remove if not needed

You can set read/write variables here, for example:

Variables.MyIntVar = 100

*/

}

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

const int MinExpectedNumberOfFields = 2;

const int MaxExpectedNumberOfFields = 3;

string[] values = Row.Data.Split(new char[] { '\t' }, MaxExpectedNumberOfFields);

// Check that correct number of values received

if (values.Length < MinExpectedNumberOfFields || values.Length > MaxExpectedNumberOfFields)

{

bool pbCancel = false;

this.ComponentMetaData.FireError(100,

"Process Detail Row",

String.Format("Incorrect number of fields. Expected: {0} to {1}, received: {2}",

MinExpectedNumberOfFields,

MaxExpectedNumberOfFields,

values.Length),

"No Help Available", 0, out pbCancel);

return;

}

Row.ErrorCode = values[0];

Row.ErrorMessage = values[1];

Row.LoggingActivityRecord = values.Length >= 3 ? values[2] : null;

}

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

Instead of the console line, try a message box perhaps?

Does everything work if you get rid of the script component?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gbritton1 SSCertifiable Points: 6522 More actions · Answer 12

Koen Verbeeck (7/29/2014)
Instead of the console line, try a message box perhaps?
Does everything work if you get rid of the script component?

I added calls to MessageBox before each statement in the script source. This finally exposed the problem:

I had been focused on the Data column, since that is what was increased. However, the sample data included the ErrorMessage column as well (See the source from the previous post). This column (not the data column) has now exceeded the specifications.

Koen, zonder uw hulp had ik deze fout helemaal gemissed!

Hartelijk bedankt!!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

gbritton1 (7/29/2014)
Koen Verbeeck (7/29/2014)
Instead of the console line, try a message box perhaps?
Does everything work if you get rid of the script component?
I added calls to MessageBox before each statement in the script source. This finally exposed the problem:
I had been focused on the Data column, since that is what was increased. However, the sample data included the ErrorMessage column as well (See the source from the previous post). This column (not the data column) has now exceeded the specifications.
Koen, zonder uw hulp had ik deze fout helemaal gemissed!
Hartelijk bedankt!!

Glad you found the issue 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP