Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Memory allocation for SSIS variable Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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
Posted Thursday, September 27, 2012 12:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Posted Friday, September 28, 2012 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Friday, September 28, 2012 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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
Posted Friday, September 28, 2012 7:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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
Posted Friday, September 28, 2012 1:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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 »

Add to briefcase ««12

Permissions Expand / Collapse