x

SSIS data flow task getting error code 0xC02020C4

In the data flow task around 10 million rows are fetched from a staging table BY QUERY and after some data conversion the records are put into another table.

The following errors are encountered at source

[SSIS.Pipeline] Error: Unspecified error

[OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (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. There may be error messages posted before this with more information about the failure.

The server 64 bit machine with 8 GB Ram

When processed with TOP 20000 rows the data flow task is working fine

Plz Suggest any solution.

more ▼

asked Sep 23, 2011 at 10:01 AM in Default

kaks gravatar image

kaks
43 2 5 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

what is the defaultBufferMaxRows and the DefaultBufferSize set on the data flow task?

As always, the error messages are very vague, but it could be fixed with tweaking the values above.
more ▼

answered Sep 25, 2011 at 09:53 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 13 14

(comments are locked)
10|1200 characters needed characters left

Cannot it be related to the memory problems?

You wrote, you have 8 GB of RAM, but how much RAM you have free when executing the SSIS Package?

When a data flow is executed, buffers are allocated on the defaultBufferMaxRows and DefaultBufferSize. Then based on the row size an appropriate buffer size is chosen. But you have to remember, that based on the data flow complexity and especially transformation components chosen several execution trees can be created and then each of the execution tree will have it's own buffer.

Then when data are flowing though the data flow, and there is a lot of data going though, then then buffers for all the execution trees are allocated and you can run out of memory.

In case of lower number of data coming from the source it could happen, then all the data read from the source prior other parts of the data flow execution tree are finished and the memory for the buffer used by the source component can be reused later in the data flow so it executes correctly.

As @Daniel Ross mentioned, by proper tuning of the defaultBufferMaxRows and DefaultBufferSize you can avoid this problem.

You can find some useful information here: [Integration Services: Performance Tuning Techniques][1].

[1]: http://technet.microsoft.com/cs-cz/library/cc966529(en-us).aspx
more ▼

answered Sep 25, 2011 at 11:18 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Hi, it's the case, therefore I was asking how much free RAM you had when executing SSIS. As if the RAM is consumed by SQL server or other services, then you can run out of memory due to buffer allocations in SSIS pipeline.

In case of you server if minimum memory was set to 6128 MB, then some memory were allocated by OS and only a small piece of free memory left out of the 8 GB.
Sep 26, 2011 at 01:12 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

Hi Pavel,

Thanx for the inputs..tried to solve the problem by tweaking the values of defaultBufferMaxRows and DefaultBufferSize of the task..but it didnot help..

Got a breakthrough when changed the Server memory configuration of the SQL server ,the task was accessing.The minimum memory was 6168MB and maximum memory was 7168 MB. After changing the minimum memory required by the server instance to 0 MB , the task successfully completed.

Since SSIS has its own buffer manager which manages data specific to the data flow pipeline engine and is different from the SQL Server buffer manager, it was not getting enough memory since 6GB memory has been specifically allocated to the SQL Server instance. So I made the Minimum Server Memory as 0MB.
more ▼

answered Sep 26, 2011 at 12:27 AM

kaks gravatar image

kaks
43 2 5 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x937

asked: Sep 23, 2011 at 10:01 AM

Seen: 16289 times

Last Updated: Sep 23, 2011 at 10:01 AM