Microsoft.SqlServer.Dts.Pipeline.D oesNotFitBufferException
Hi I have a SSISproject that has one parent package and three child packages. When I run the project on my development machine in debug mode it works fine. Also if i run the packages using dtexec on my development machine it still works fine. However the problem comes in when I tryandrun the project using dtexec on thestaging serveri get the following error: Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer. does anyone have any idea how to fix this please? thanks G
June 15th, 2006 2:14pm

Probably the data on staging server is different from the data on development machine? You are trying to put data into buffer that is bigger than the buffer column size allocated for this data. E.g. script component builds some string, gets a string of length 60, and then tries to put it into STR column of length 50 - you'll get this exception as a result. You need to adjust the column size to match expected data, or cleanup the input data to avoid the overflow. You probably don't see it on development machine, because your test data is different and this condition never occurs. http://msdn2.microsoft.com/en-us/microsoft.sqlserver.dts.pipeline.doesnotfitbufferexception.aspx
Free Windows Admin Tool Kit Click here and download it now
June 16th, 2006 8:19am

thanks for the response but I am not sure that that is the problem since I am using the exact same data and exact same packages on both machines?
June 19th, 2006 11:32am

Hi After some testing I have found that whenI run the child packages in series as apposed to in parallel it all works fine. This should do for the mean time but if you have any idea why this is could u please let me know. Thanks for the help. Gary
Free Windows Admin Tool Kit Click here and download it now
June 19th, 2006 12:45pm

Hi again I have a second package that has the same setup but manipulates a different data set and I am getting this same error but this time I can't find a way around it. The data and the packages are exactly the same on both servers.On the development server the packages run with no problem but on the staging server I get buffer memory issues. Anyone know why this is maybe? It seems that the column width is too wide for the buffer so the buffer is overflowing into a second buffer which is being used by the other child package. Any ideas? thanks G
June 28th, 2006 12:22pm

Pipeline Buffers are not shared between child and parent packages. Could you describe what the packages are doing? What component reports this error? Do both dev and staging machine have the same architecture (32-bit, or 64-bit)?
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2006 8:29pm

Hi In the parent package data is extracted from multiple flat files and bulk inserted into multiple database table. Following this 4 child packages are initiated using the execute package task. within each child package a data flow is started. In the data flow information is extracted from a table (populated in the parent package). The data is transformed using a script component. A row count is taken and then the data is inserted into a second database table. After some investigation. I have discovered: -on the development server the entire package works perfectly with no errors -on the staging server each child package works when it is the only one executed from the parent package. -on the staging server when I run two child packages it seems the first child package works fine but in the second package to be executedthe script component does not get executed (I inserted a MessageBox and ComponentMetaData.FireInformation and there was no output). This is the error message in the log when running all 4 child packages together: -------------------------------------- OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:12:57 PM,2006/06/29 02:12:57 PM,0,0x,The value is too large to fit in the column data area of the buffer. Record ID - Merchant Number (ODS: QMAKBACCTNBR) : 00000000000000000002063 MerchantNumber (ODS: QMAKBACCTNBR)OnInformation,,,Transform Data Into MerchantStatementsMonthly,,,2006/06/29 02:15:07 PM,2006/06/29 02:15:07 PM,1074016264,0x,Post Execute phase is beginning. OnInformation,,,ODS To DWH MerchantStatementsMonthly,,,2006/06/29 02:15:07 PM,2006/06/29 02:15:07 PM,1074016264,0x,Post Execute phase is beginning. OnInformation,,,TrOnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:03 PM,2006/06/29 02:23:03 PM,-1073450910,0x,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(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:03 PM,2006/06/29 02:23:03 PM,-1073450910,0x,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(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:06 PM,2006/06/29 02:23:06 PM,-1073450974,0x,The ProcessInput method on component "Script Component" (10641) failed with error code 0x80131600. 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. OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:06 PM,2006/06/29 02:23:06 PM,-1073450974,0x,The ProcessInput method on component "Script Component" (10641) failed with error code 0x80131600. 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. OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1073450975,0x,Thread "WorkThread0" has exited with error code 0x80131600. OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1073450975,0x,Thread "WorkThread0" has exited with error code 0x80131600. OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:09 PM,2006/06/29 02:23:09 PM,-1073450952,0x,The PrimeOutput method on component "Q19400D" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:09 PM,2006/06/29 02:23:09 PM,-1073450952,0x,The PrimeOutput method on component "Q19400D" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. ---------------------------------------------------------------- The development server is 32 bit and the staging server is 64bit but they are both runnning 32 bit operating systems. Any help would be appreciated. thanks G
June 29th, 2006 3:27pm

Hi We had somewhat the some problems as described above. We are implementing a datawarehouse-solution using sql2005and an 64bit windows 2003 server. We use workflow packages that executes childpackages simultaniously.Errors concerning memory and buffers popped up at random in different childpackages. We installed SP1 and the Cumulative hotfix package (build 2153). This helped a little but didn't solve all the problems. We are testing fortwo days now and it seems that changing the SSISpackage-property MaxConcurrentExecutables from -1 to 1 in the workflow package solves the remaining memory and buffer problems. Setting this property prohibits concurrent execution of childpackages. It seems buffermanagement on 64bit servers is still a bit buggy. Hope this helps in your case too. Jan
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2006 3:02pm

Hi Thanks for the help. I narrowed the problem down to the script components in the child packages so I deleted them and recreated them and it seems to work fine. No sure why this is. Any one know?
July 10th, 2006 4:24pm

Strange. Did you create them in a beta version? Do you have a copy of the old package? Maybe check the differences in the XML in the .dtsx file. -Jamie
Free Windows Admin Tool Kit Click here and download it now
July 10th, 2006 4:53pm

hi, I also have same problem and also recreate my Task....but not solve my problem I'm using Script Component Task to do custom transformation.I add 12 output columns in output0.5 of these columns is DT_STR and their length is 4, 3 is DT_STR 2, 3 is DT_STR 12 and 1 is DT_I4. When executing the package ,I get an exception as below. The value is too large to fit in the column data area of the buffer.at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value) at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value) at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value) at ScriptComponent_04dc3685b8d744898f09d88716141989.Output0Buffer.set_isjsst(String Value) at ScriptComponent_04dc3685b8d744898f09d88716141989.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at ScriptComponent_04dc3685b8d744898f09d88716141989.UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at ScriptComponent_04dc3685b8d744898f09d88716141989.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) I debug and find the execption comes form output0Buffer set value.I alse do some testing,1.Create a simple package wiht same Script Component Task and find no exception.2.Remove some output0 columns and find no exception.It's very confusing me... Any help is very appreciated.
July 14th, 2006 11:41am

I had the same error and exception. I deleted and recreated the script component and the destination component that the output from the script component was going to. It works for me now.
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2006 6:36pm

was getting the same error - found the resolution was to make sure the output column created in the design script was set to the same size as the source column . In my case I was trying to set the desitination column to 35, since that is the size of the ultimate destination column in the destination database. Even though I was making sure the string inside the script was truncating to 35 characters, the design script component would still throw the error. I instead create a derived column component immediately following the script component, and use that one to do the trucation to the destination column size...
September 28th, 2006 7:40pm

In my case it was Custom Component which did not update OutputBuffer field lengths after a change in a Source componentThanks team for pointing the right direction!
Free Windows Admin Tool Kit Click here and download it now
February 10th, 2007 7:30am

I just got past this error by increasing the defaultBufferSize in the data flow task. Just FYI.TIA, ChrisRDBA
July 9th, 2009 7:27pm

Can i ask how to increase the defaultBufferSize in the data flow task?I got the same error.Thanks!
Free Windows Admin Tool Kit Click here and download it now
November 6th, 2009 4:41am

If I understand your problem you have child package running in Parallel and that is where you see the issue is surfaced, when you were running in sequence it was working fine. Script Component would have a GUID that is refered by framework for caching, probably script components for child packages have same GUID, which can happen if you have created one package from other package by changing some code and save the same with new name, the components are there with same GUID. So when running in parallel, script component for the first package execution is cached using GUID, while when in second package script component is executed, since GUID is same it won't load the task into memory from fresh, rather it would use the one that is cached. As these are child packages being executed from one package in parallel, everything is running under same context. Since script component in child packages have different code and refers different variables but still they have same GUID you see these kind of errors.
April 22nd, 2010 8:44am

to set the defaultBufferSize, go to the Data Flow and check the Properties. You will see the defaultBufferSize and the default is set to 10,485,760 and the max is 100,000,000.Yik Wing
Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2011 4:44pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics