April 28, 2010 at 5:34 am
I am attempting to "trickle" feed on very large dataset into another.
I have three packages involved in the loop. Package 1 calls package 2 as an Execute Package task, then package 2 called package 3, and finally package 3 calls package 1 again.
There is a check in place within package 1 so that when all records have been processed it stops before it calls package 2.
After about 11 or 12 "loops" I get the following error, and the process stops:
Message
Executed as user: __ServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 06:23:09 Error: 2010-04-28 06:49:19.56 Code: 0xC0047012 Source: Email Lookup Description: A buffer failed while allocating 10475584 bytes. End Error Error: 2010-04-28 06:49:19.85 Code: 0xC0047011 Source: Email Lookup Description: The system reports 76 percent memory load. There are 3756089344 bytes of physical memory with 870182912 bytes free. There are 2147352576 bytes of virtual memory with 45793280 bytes free. The paging file has 5729230848 bytes with 2594545664 bytes free. End Error Error: 2010-04-28 06:49:20.10 Code: 0xC02020C4 Source: Email Lookup Description: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E. End Error Error: 2010-04-28 06:49:20.67 Code: 0xC0047038 Source: Email Lookup SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Email Lookup" (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. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 06:23:09 Finished: 06:50:02 Elapsed: 1613.84 seconds. The package execution failed. The step failed.
Since then I have stopped Package 3 calling package 1, and so stopped the looping. I have though scheduled Package 1 to run every 5 minutes, during which time it calls package 2, which in turn calls package 3.
Doing it this way has caused no problems at all.
Is there some reason why the package looping doesn't release memory?
April 28, 2010 at 5:43 am
As an aside... does anyone know whether SQL Server Agent will run the same job twice? The job mentioned above is scheduled to run every 5 minutes. If it has not finished by the time 5 minutes lapses, will it wait, skip to the next 5 minute slot, or attempt to run the job twice?
Thanks for any info
April 28, 2010 at 8:38 am
SQL Agent will only have a single instance of a job running. If a schedule overlaps then the schedule that should have been run will be ignored.
It sounds like you have created a near infinite loop, 1 calls 2 calls 3 calls 1. I don't see why it would release memory.. If anything, package one should be made smart enough to loop itself after it it gets done with package 2 (and conversely package 3).
CEWII
April 28, 2010 at 8:45 am
Thanks for that.
I had though that the memory would be released when each individual package finished running.
I could make package 1 re-run itself after package 2 has finished (which would include package 2 calling package three) without too much problem. Do you think that would somehow release memory better than my infinite loop?
April 28, 2010 at 9:32 am
It won't release memory until the instance of the package completes and unloads. When you call a package from a package both packages exist in memory, because package 1 is still running when you call out to package 2 (and 3). Think of it as a call stack, the caller is still in memory even though execution has transfered to a child process.
If you make package 1 smart enough to run its guts more than once there is still just a single instance of it in memory. You don't want to truly run the package more than once just add a step that checks for more work at the bottom, if it finds it it loops to the top and starts again, if not it ends.
CEWII
April 28, 2010 at 9:48 am
Elliott W (4/28/2010)
".. smart enough to run its guts ..."CEWII
Wow, I just learnt a new technical term 😛
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 28, 2010 at 10:21 am
And I a new lesson. Not calling packages in cyclic fashion.
Rex Smith
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply