Running SSIS packages in separate memory allocations or increasing the default buffer size?

Question

I have a SSIS package that has a child package that is failing. The error message isn't very helpful.

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

The problem seems to be I am running out of Virtual Memory to complete the job.

I found a forum thread that may help solve the problem. http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/d6d52157-0270-4200-a8c2-585fa9a0eed5/

From the solutions offered I am unsure though how to:

1. increase default buffer size
2. Allocate a child pacakage into it's own memory allocation.

I am running the package daily in SQL Server 2005. I was running fine daily up until the 12th. I am assuming the dat file that we are using to import data into the database grew to a size that was to large for the database to handle. It's only a 8.90MB csv file though. The import is a straight column to column import.

The problem child package is step 1 and fails and continues and successfully completes the next 8 steps.

Answer 1

How much memory is allocated to SQL Server? How much memory is allocated outside of the SQL Server process space?

The reason I ask is becuase SSIS memory is allocated from the memToLeave area of memory that sits outside of the SQL Server process space.

See here for details on configuring the amount of memory available to the memToLeave portion of memory.

For generic performance tuning of SSIS consult the following article.

http://technet.microsoft.com/en-gb/library/cc966529.aspx

I hope this makes sense but feel free to drop me a line once you have digested the material.

Cheers,

Answer 2

Random thought: memory leak?

Our prod cluster (big corporatebbuild) was SQL 2005 SP1. Memory leaks, reboot needed every 1-4 weeks.

SP2 + HF 3068: fixed

Answer 3

To change buffer size, select Data Flow task and change DefaultBufferMaxRows, MaxBufferSize properties. They together determine the buffer size.

Note that in the discussion you've linked, the user originally increased the buffer size (to 100Mb), which probably caused the problem, and the recomendation was to decrease it. Default buffer size (10Mb) should usually be OK.

To run child package in separate process, set ExecuteOutOfProcess property of Execute Package task (in the master package).

But first of all: make sure you're really running of Virtual Memory, not real memory, which is usually more likely. Look at the task manager at the processes that consume memory on this machine.