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

Question

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

hegdesuchi

Hall of Fame

Points: 3047
More actions
August 12, 2015 at 12:25 pm

#299762

Hi,
I am trying to execute my SSIS Package which has 9 sequence containers. Each container does below tasks.
1. Truncate table
2.Data flow task- Read the data from source table into Script task which calculates hash value and outputs the value-lkp transformer-destination
3. sql task(updating table).
I am facing below error in script task.
Error: Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
Also below error:
Error: System.IndexOutOfRangeException: Index was outside the bounds of the array.
I went through some forums and blogs and tried changing defaultbuffersize to max in data flow task, changed the output column length to 1000 but nothing seems to be working.
NOTE: When I execute individual container it works all fine and I have result. When I run as a package I get above error.
Can you please let me know where am I going wrong? I am naive in VB.net/C#.
exact Code I am using is in below link.
[/url]
I have given output length for hashvalue as 1000 .

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

This error means that there is a datatype mismatch or that the source columns have gotten out of alignment with the output columns (destination).

If none of that resolves it, then something has gotten funky with the metadata in the package.

Deleting the offending object and recreating it will often fix that.

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

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 2

Hi,

Thank you for the reply.. The Error I was getting earlier seems to have solved. But now I am getting below Errors.

Error: The buffer manager cannot extend the file "C:\Users\}.tmp" to length 2030084 bytes. There was insufficient disk space.

Error: A buffer failed while allocating 7453916 bytes.

Error: The system reports 90 percent memory load.

There are 30064300032 bytes of physical memory with 2868932608 bytes free.

There are 4294836224 bytes of virtual memory with 93224960 bytes free. The paging file has 47133245440 bytes with 6751088640 bytes free.

How do I go ahead?:-(

pietlinden SSC Guru Points: 63421 More actions · Answer 3

Error: The buffer manager cannot extend the file "C:\Users\}.tmp" to length 2030084 bytes. There was insufficient disk space

Bigger hard drive? Space on another network drive?

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 4

Hi,

thank you for your reply.

We do not have permission to do that.. so I have to find an alternative way to do this.

I am planning to create a hashkey in Stored procedure and have merge Statements to update/insert records.and then execute the Sproc using sql execute task.