Out-of-Memory error on Sort Transformation

Trying to determine if anyone else has encountered this problem and, if so, have you obtained a resolution:

I have a data flow initiated from a Foreach Container. Inside the Data Flow there is a Source Script transformation connected to a Sort transformation with an OLE DB Destination.

The Sort transformation has the sort keys defined and the "Eliminate Duplicates" option checked.

The first pass into the Data Flow is successful. The 2nd pass fails in the Sort:

Information: 0x4004300A at Load TC03, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Load TC03, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Load TC03, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Load TC03, DTS.Pipeline: Execute phase is beginning.
Warning: 0x80004005 at Load TC03, Sort [1506]: Unspecified error
Error: 0xC020821B at Load TC03, Sort [1506]: A worker thread in the Sort transformation stopped with error code 0x80004005. A catastrophic error was encountered while sorting a buffer.
Error: 0xC020821B at Load TC03, Sort [1506]: A worker thread in the Sort transformation stopped with error code 0x%1!8.8X!. A catastrophic error was encountered while sorting a buffer.
Error: 0xC0208296 at Load TC03, Sort [1506]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.
Error: 0xC0047022 at Load TC03, DTS.Pipeline: The ProcessInput method on component "Sort" (1506) failed with error code 0x80004005. 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.
Error: 0xC0047021 at Load TC03, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x80004005.
Error: 0xC0047039 at Load TC03, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load TC03, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Information: 0x40043008 at Load TC03, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Load TC03, hlp_ISOTypeLossTC03 [379]: The final commit for the data insertion has started.
Information: 0x402090E0 at Load TC03, hlp_ISOTypeLossTC03 [379]: The final commit for the data insertion has ended.
Information: 0x40043009 at Load TC03, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Load TC03, DTS.Pipeline: "component "hlp_ISOTypeLossTC03" (379)" wrote 5388 rows.
Task failed: Load TC03
Warning: 0x80019002 at Foreach TC03 File: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at ISOTypeLoss: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ISOTypeLoss.dtsx" finished: Failure.

Does this ring a bell with anyone?

ThanksI think this is a known issue that they are currently looknig into.
its not necassarily a problem with the Sort, its more caused by the Foreach loop (I think).

Check out thread "For Loop container - out of memory condition"

-Jamie|||

Hi Raul,

Yes, this is a known issue. We've fixed it for the next CTP. In the meantime, the only workaround I'm aware of is to put the data flow task in a separate package and use an Execute Package task to schedule the execution of the data flow task.
regards,
ash

|||What volume of data do you get this error on?

It would be interesting to see if ExtraSort component (http://www.ivolva.com/extrasort.html) would hit the same problem.|||Very small - about 300 rows|||Ash:

I have the June CTP installed and am getting the same error:

Warning: 0x80004005 at Load V6 Policy, Sort [4166]: Unspecified error

Error: 0xC020821B at Load V6 Policy, Sort [4166]: A worker thread in the Sort transformation stopped with error code 0x80004005. A catastrophic error was encountered while sorting a buffer.

Error: 0xC020821B at Load V6 Policy, Sort [4166]: A worker thread in the Sort transformation stopped with error code 0x%1!8.8X!. A catastrophic error was encountered while sorting a buffer.

Error: 0xC0208296 at Load V6 Policy, Sort [4166]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.

Error: 0xC0047022 at Load V6 Policy, DTS.Pipeline: The ProcessInput method on component "Sort" (4166) failed with error code 0x80004005. 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.

Error: 0xC0047021 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x80004005.

Error: 0xC0047039 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Another poster suggested a thread for "For Loop container - out of memory condition" - I searched this forum and it returned no results.

Any update as to whether the fix made the June CTP?

|||

Raul Villaronga wrote:

Any update as to whether the fix made the June CTP?

It didn't. It'll be in the next CTP.|||Yes, this was caused by a bug in the sort transform. It was not correctly releasing some resources when the dataflow completed. The forloop just exposes the problem because the sort doesn't get freed between executions.|||FYI - Still getting this in September CTP.
|||Both memory issues I had with June CTP including the catastrophic failure of the sort in a loop and the data viewer consuming memory have been fixed in September CTP.

Out of memory Exception when Running a package with XML Task

Hi..

I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.

The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.

I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".

I also run this package on a 8GB Ram server, and same applies.

Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.

Thanks

Are you running on a 32-bit or 64-bit OS? 32-bit servers are generally limited to about 2 gb of addressable memory, unless you have made some changes to the server configuration.|||

I'm running 32-bit... I wasn't aware of that!! I hope my admins knew that and made whatever change (I'm been highly opomistic, givin them the benefit of the doubt).

**UPDATE**

Yes... The server has the "/PAE" switch in the boot.ini, so its configured to use the 8 gigs of memory available to it.

|||Are other things running on the server - for example, the SQL Server relational engine? How much memory is allocated to it?|||

I'm looking at the Performance Tab in Task Manager and there is

Total Physical Memory: 8.32 Gigs

Available Physical Mem: 7.12 Gigs

From that 1 gig of memory taken is about 600 Megs devoted to System Cache and the rest is commited to running programs like SQL Server.

The only SQL server 2005 services that are running are

SSIS (of course)

Full Text Search

SQL Server

SQL Server Agent

Again... Thanks for all your help

|||

This is a good work around.

I used a Script Task (I find myself using it more and more as I find limitations in SSIS) and with 3 simple lines of code I resolved the issue.

Code Snippet

Dim xslt As New XslTransform()
xslt.Load("C:\Temp\Template.xslt")
xslt.Transform("C:\Temp\XMLSource.xml", "C:\Temp\XMLSource_Revised.xml")

This code performs the task in 5 mins -/+ consumming about 900 megs of memory space, for a 500 meg file.

Out of memory Exception when Running a package with XML Task

Hi..

I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.

The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.

I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".

I also run this package on a 8GB Ram server, and same applies.

Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.

Thanks

Are you running on a 32-bit or 64-bit OS? 32-bit servers are generally limited to about 2 gb of addressable memory, unless you have made some changes to the server configuration.|||

I'm running 32-bit... I wasn't aware of that!! I hope my admins knew that and made whatever change (I'm been highly opomistic, givin them the benefit of the doubt).

**UPDATE**

Yes... The server has the "/PAE" switch in the boot.ini, so its configured to use the 8 gigs of memory available to it.

|||Are other things running on the server - for example, the SQL Server relational engine? How much memory is allocated to it?|||

I'm looking at the Performance Tab in Task Manager and there is

Total Physical Memory: 8.32 Gigs

Available Physical Mem: 7.12 Gigs

From that 1 gig of memory taken is about 600 Megs devoted to System Cache and the rest is commited to running programs like SQL Server.

The only SQL server 2005 services that are running are

SSIS (of course)

Full Text Search

SQL Server

SQL Server Agent

Again... Thanks for all your help

|||

This is a good work around.

I used a Script Task (I find myself using it more and more as I find limitations in SSIS) and with 3 simple lines of code I resolved the issue.

Code Snippet

Dim xslt As New XslTransform()
xslt.Load("C:\Temp\Template.xslt")
xslt.Transform("C:\Temp\XMLSource.xml", "C:\Temp\XMLSource_Revised.xml")

This code performs the task in 5 mins -/+ consumming about 900 megs of memory space, for a 500 meg file.

OTP: Jamie Thomson, ssis man?

are you sleeping inside a Data Flow? Where exists SSIS you are

LOL... he's already a SSIS legend

|||

There are no social forums here, this is not a question and therefore off topic. It's OK to make a social comment within a thread, but creating non-question threads pollutes the unanswered question area, and potentially the search.

|||you're right cgraus. I'll keep in mind