InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:56:09 GMT20http://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxTruly 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:[code="sql"]SELECT *FROM sourcetableWHERE recordkey NOT IN ( SELECT DISTINCT recordkey FROM destinationtable)[/code]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.Fri, 28 Sep 2012 13:24:23 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxThese 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.Fri, 28 Sep 2012 07:31:04 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxHaven'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.Fri, 28 Sep 2012 06:53:06 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxI agree. Time to escalate this one to MS.Fri, 28 Sep 2012 06:49:11 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxIs 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.Thu, 27 Sep 2012 12:46:25 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxNow this is interesting.I moved the write of the recordset to the variable in the data flow.Before:OLEDB SourceDerived column transform Data conversion transformMulticast transform going into:flow 1 - OLEDB Destinationflow 2 - ADO recordset destination that writes to the variableI rearranged things so that the multicast is done [b]after[/b] 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]Wed, 26 Sep 2012 13:36:59 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspx[quote][b]rburko (9/26/2012)[/b][hr]I'm taking the recordset, saving it to a variable, and using it in a subsequent loop.I'm sure I can cajigger the subsequent step to read from a temp table or something but I wanted to use the loop. And you're right, the things I've seen about this particular error seem to only obliquely be related to what I'm trying to do.If it's a buffer issue, I don't know where to make changes in settings to get it to work, it that's at all feasible.[/quote]There's another way to deliver to a recordset, but it's awkward. Use a Script Component Destination and build out your code to feed the recordset object. Worst case scenario you might at least get a usable error. I don't know how to do it offhand but I know there's a few blog posts around the web on it.Wed, 26 Sep 2012 11:10:20 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxI'm taking the recordset, saving it to a variable, and using it in a subsequent loop.I'm sure I can cajigger the subsequent step to read from a temp table or something but I wanted to use the loop. And you're right, the things I've seen about this particular error seem to only obliquely be related to what I'm trying to do.If it's a buffer issue, I don't know where to make changes in settings to get it to work, it that's at all feasible.Wed, 26 Sep 2012 07:50:21 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxNot sure it's relevant, but I plugged that error into Google and got this as the top result: [url]http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/8a3558df-221a-45e6-8e08-c23c987c12a9[/url]Seems to be a similar issue. Since you're going for an ADO recordset, instead of a table, it might not be relevant.On the other hand, you might want to check if you can recode the package to put the data into a table in tempdb or some such, instead of ADO, and see if you can make it do what you need that way.Wed, 26 Sep 2012 07:40:23 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxEvil Kraig F, GSquared, both good points. I let the task run all night, came in this morning, and it still did not complete. However, I did notice this in the execution results:[blah blah blah[1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. So it looks to be a buffer error of some kind. I'm still researching a solution.Wed, 26 Sep 2012 06:46:33 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspx[quote][b]rburko (9/25/2012)[/b][hr]No, it is preceded by a multicast transformation. One leg of the data flow goes into an OLE DB destination and receives 1,210 rows. The other leg goes into the ADO recordset destination which should also receive 1,210 rows, albeit one column from the row.I see 1,210 going into the OLEDB destination. The ADO recordset destination just hangs there. I've let it run for over and hour and it still doesn't complete.[/quote]As Kraig mentioned, Not Yellow = Hasn't Started, which is different from Hasn't Finished. Or possibly the UI is balking on the update. Have you checked available system resources? Have you looked at the execution log to see if that step is even reached?Wed, 26 Sep 2012 06:15:17 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspx[quote][b]rburko (9/25/2012)[/b][hr]Just for chuckles, I changed my initial selection criteria so that only 100 rows get selected, instead of 1,000 or so rows, ending up in the ADO recordset destination that writes to the object variable I'm having issues with. And it worked flawlessly. So the problem definitely seems to be related to volume.But why? I still don't know.[/quote]I don't have anything but anecdotal evidence for this. I've seen similar issues with Object items for recordsets for larger datasets, but considering the only thing you can do with it is loop it after the fact (other than via .NET) I don't normally hit more than 100 items in my recordsets. I have however seen similar issues for the record count. Width doesn't seem to matter as much, if at all. It's inconsistent however. I've done other tests where I simply created a script source and dumped 5000 ints into the stream to a recordset object directly with no issue.You might be better off dumping this to a .raw file, but what's the end goal with the recordset data? I don't have a reason it's occurring but I might know of a few workarounds if I know where it's heading.The fact that the exact same package works with less inputs blows out most of my usual troubleshooting.GSquared above is absolutely correct, however, SSIS simply uses local windows memory for whatever it needs and will grow to whatever it wants. It ignores SQL Server's cache completely (which means SSIS might be starved if you've got it on the same system as a server leaving only 2 gigs for the OS left, but that's a different problem). The object itself doesn't have any restrictions afaik, unless these rows are rediculously huge (4 gigs huge) and you're in 32 bit mode.Here's the final curiousity though... Yellow/White/Whatnot occurs during processing. Anything white hasn't even begun to fire yet, but the multicast should already be sending data to it (at least the first row).What is listed in Execution Results for that task after you've stopped the process? Is it still validating? Does it Prepare for Execute?... you get the drift. Once execute fires it should turn yellow in the interface, but that can get grouchy as we all know.Tue, 25 Sep 2012 15:38:39 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxJust for chuckles, I changed my initial selection criteria so that only 100 rows get selected, instead of 1,000 or so rows, ending up in the ADO recordset destination that writes to the object variable I'm having issues with. And it worked flawlessly. So the problem definitely seems to be related to volume.But why? I still don't know.Tue, 25 Sep 2012 15:13:55 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxNo, it is preceded by a multicast transformation. One leg of the data flow goes into an OLE DB destination and receives 1,210 rows. The other leg goes into the ADO recordset destination which should also receive 1,210 rows, albeit one column from the row.I see 1,210 going into the OLEDB destination. The ADO recordset destination just hangs there. I've let it run for over and hour and it still doesn't complete.Tue, 25 Sep 2012 13:10:21 GMTrburkohttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxThat won't be a memory allocation problem, unless the server you're running it on is already all the way at the limits of the swap file. You'd know that, because the server would crash instead of just not getting to a certain step in the SSIS package.More likely, the step is either taking longer than you expect, possibly because of the dataset size being larger (hence, longer query), or there's something in the package stopping it from getting to that step in the first place, like a malformed process flow (conditions incorrectly set, that kind of thing).Are you querying data from SQL Server to fill the dataset? If so, running a trace (Profiler or server-side), with begin and end steps logged, will tell you how long the query takes to run. That might be a good place to start.Tue, 25 Sep 2012 13:02:05 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1364173-364-1.aspxI'm having a problem with an SSIS variable.The variable is defined as data type object. I'm writing an ADO record set to this variable using a Recordset destination in a data flow. I'm writing one input column to this variable, varchar(50), and the count is 1,210 rows. My problem is that, when I view the code execution in debugger, everything works but it hangs up writing the recordset variable. All other data flow tasks turn yellow but the recordset destination remains white. I've written to this variable successfully many times before. I'm wondering if I'm having a problem with restricted memory when the number of rows increase. Is there a way to increase this with an SSIS setting? Is there a way to verify that memory is the problem? This isn't the biggest record set in the world so I don't understand why the task fails to complete.Tue, 25 Sep 2012 10:27:33 GMTrburko