Memory allocation for SSIS variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Memory allocation for SSIS variable

16 posts, Page 2 of 2

««1 2

Memory allocation for SSIS variable

Rate Topic

Display Mode

Topic Options

Author

Message

rburko

Posted Wednesday, September 26, 2012 1:36 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:57 AM
Points: 26, Visits: 284

Now this is interesting.

I moved the write of the recordset to the variable in the data flow.

Before:

OLEDB Source
Derived column transform
Data conversion transform
Multicast transform going into:
flow 1 - OLEDB Destination
flow 2 - ADO recordset destination that writes to the variable

I rearranged things so that the multicast is done after the OLEDB source, flow 1 going to the derived column and data conversion transforms, then the OLEDB Destination while flow 2 goes to the ADO recordset destination.

What happens? The ADO recordset destination turns yellow. Everything turns yellow. But nothing completes! When I stop debugging, I get the same error:

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

I still don't know what is going on.

[Scratch head, hurl expletives at monitor]

Post #1364910

Evil Kraig F

Posted Thursday, September 27, 2012 12:46 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 5,678, Visits: 6,130

Is that the only error in the log? There should be a bunch more information alongside it and additional errors neighboring it... otherwise I'd call microsoft because you don't have enough of an error tree to debug.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1365453

GSquared

Posted Friday, September 28, 2012 6:49 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

I agree. Time to escalate this one to MS.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1365839

rburko

Posted Friday, September 28, 2012 6:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:57 AM
Points: 26, Visits: 284

Haven't had time to rerun my scenario to see if there are other error messages. I think there are but they were pretty generic "pipeline" messages. I'll try to do that today.

What is a good contact number for Microsoft? I should know one but I try to avoid calling if I can at all possible find the answer somewhere else.

Post #1365841

rburko

Posted Friday, September 28, 2012 7:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:57 AM
Points: 26, Visits: 284

These are the errors I'm getting. I also did full logging but basically got the same messages at the point of error.

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

Error: The pipeline received a request to cancel and is shutting down.

Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Whatever" (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.

Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

Post #1365863

rburko

Posted Friday, September 28, 2012 1:24 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:57 AM
Points: 26, Visits: 284

Truly bizarre. I don't believe the problems I was having have anything to do with lack of memory (except maybe my own).

The OLEDB source I had was using a SQL statement to check against the OLEDB destination table to see if rows already existed on that table. Only rows that didn't exist on the destination table were returned. The code was functionally similiar to this:

SELECT *
FROM sourcetable
WHERE recordkey NOT IN (
		SELECT DISTINCT recordkey
		FROM destinationtable)

If I changed the SQL statement to use a different table than the eventual destination table, the package worked like a charm.

If I changed the SQL statement to return the top something rows, even if the top value exceeded the number of rows in the source table, and kept the check for existing records pointing to the destination table, the package worked like a charm.

But if I kept everything the same as in the SQL example, and the number of rows exceeded something like 1,000, strange things happened. It was almost as if there was a loop. The data flow never completed.

Maybe it does have to do with cache or memory. Maybe some records were obtained from the source, written to the dataflow pipe, then maybe it recognized the key already being "written" to the destination table and it got confused. I just don't know.

I changed the SQL, followed the OLEDB source with a lookup task, looked up the record key in the destination table, conditionally passed records that didn't already exist from the lookup, then wrote it to the destination table. And ... the package worked like a charm. Something like 50,000 rows were processed.

I still don't quite understand what was happening. All I know is that, after changing things around a bit, it all works now.

Chalk it up to problematic SSIS error and warning messages leading me down errant paths.

Post #1366085

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions