Intermittent errors when executing Kimball SCD many times.

Topics: Issues 3: Executing the Component (Run-Time)
Oct 4, 2010 at 3:41 PM

Hi
Configuration: Kimball SCD V1.5 64bit; SQL 2008 R2.

We have a SSIS package that updates a dimension using the Kimball SCD and it appears to run fine without any problems. We then amended the package to take a date as a parameter, this allows us to run the package and populate the dimension for dates in the past. We then created a new SSIS package that accepts FromDate and ToDate parameters, the package then calls the SCD package for every date within the entire date range.

We entered a range of one year and the package failed with the error below. If we then run the package from the failed date, it runs to completion. We have tried this several times and each time the package fails at a different date in the range but when restarted at the failure date it runs to completion. The SSIS package is executed 'In Process'. We will try running it 'Out ofProcess' but haven't tried it yet.

Error:

Error: 0xC0047012 at Execute Kimball Method SCD: A buffer failed while allocating 10484240 bytes.

Error: 0xC0047011 at Execute Kimball Method SCD: The system reports 96 percent memory load. There are 2111348736 bytes of physical memory with 76656640 bytes free. There are 2147352576 bytes of virtual memory with 141651968 bytes free. The paging file has 4061081600 bytes with 979349504 bytes free.

Error: 0xC02020C4 at Execute Kimball Method SCD, Dimension (User Layer) [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

Error: 0xC0047038 at Execute Kimball Method SCD, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Dimension (User Layer)" (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.

Any help or advice is much appreciated.

Thanks

Roy

Oct 11, 2010 at 3:28 PM

We tried executing the package containing the Kimball SCD as 'Out of prcess'. Surprisingly the package runs much more slowly, we didn't let it run to completion as the speed wasn't acceptable. To get around the problem we are going to run the package for 3 month periods.

We would still like to know why this problem occurs. I would have thought that each time the package containing the Kimball SCD completes the memory that the package was using would be released.

Thanks

 

Roy

Coordinator
Oct 21, 2010 at 12:10 AM

Hi Roy,

I would expect the same thing you are - that the package wouldn't appear to "leak" memory.  That shouldn't be the case, and hasn't been from my observations of running basically the same setup as you've described.  The component does use the .Net framework - which means memory is managed "for me"... perhaps not that well, or perhaps I'm not the best developer.  (I know which one I'd pick... can't see how I could be better than hundreds of MSFT devs...)

I'm really stumped as to why running the package out of process is so much slower - that makes no sense... unless you're butting up against memory just by spawning a new instance of DTEXEC...