ADO Recordset from SQL Task issue

Dear Folks,
I have a Foreach Loop that enumerates a set of files from an ADO recordset variable which is populated by a preceding SQL task. The query from the task that populates the recordset returns about 200 rows with one varchar field(a file path). The loop is long running, and so far it errors on the connection string populated by the enumeration variable after about an hour. The timeout for the SQL task is set to zero. Could it be the source recordset variable timing out, or could it be that the recordset is too large?
Thanks,
Chris

If it is related to the number of records you could start by limiting the result to 10 records, and add 10 records until you see the problem again.

Also, do you have the latest service pack? Here's a post concerning an issue prior to the service pack.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277765&SiteID=1

|||

200 rows is not too large for a recordset.

I am confused. the sql task populates the recordset and the for each loop enumerates the results. I assume that the sql task is outside (before) the loop. so, which is taking a long time? executing the sql command or running over the recordset in the loop?

|||Thanks

for the replies, and yes, the SQL Task populates the ADO recordset

variable before the loop. The loop enumerates the results for about an

hour before it errors out. The error is the same every time, reporting

that the connection string has failed to have been acquired. The

variable for the connection string is populated by enumerating the

recordset. If i change the loop's collection enumerator to a directory,

the loop runs fine.
Thanks for your help,
Chris
|||I'm on a SQL Server 2k5 x86 ,SP1, windows 2000 platform if that helps.
Thanks,
Chris
|||Also, It's definitely not the recordset itself, as I've been able to use the same ADO variable result set to enumerate loops with fewer tasks\are shorter running. Any chance this is a memory leak?
|||

Well, what are you doing with the rows from the recordset? I think the enumerator gives you a fields collection.

in other words, what is in the loop

|||The (one field per) rows from the recordset enumerate the collection, a directory of Access databases. Inside the Foreach loop are several data flow tasks. A variable is mapped from the returned row and helps populate a connection string expression for an Access connection manager. The source connection for each data flow task uses the connection manager. It works great for about an hour when i use the recordset as a collection enumerator, and the loop does not error out if I use the directory as a collection enumerator.
Thanks,
Chris
|||

This is odd. there certainly could be a bug somewhere in the recordset object itself (in the interest of full disclosure, I wrote the code for the ADO Recordset object back in '96 :) )

If you don't mind doing some experiments for me, I would ask you to try to separate the recordset usage from the pipeline usage as a way to try to narrow down the problem. it sounds like you have done this a bit.

instead of a dataflow task, put a script task in the loop. extract the value of the connection string and write it out somewhere so that you can see the values and make sure they all look rational. If this loop completes without error, then add some long delay (like a 5 minute wait) into the script task. this will tell us if their is some purely time related issue with the recordset (doubtful, but who knows could be a garbage collector kicking in and dropping references to the com objects of the recordset? crazy i know).

You say you can use a non-recordset list of the same exact connections and get this to succeed? that makes me think that this is not an issue with the access driver, but who knows. Exactly what error are you getting when it fails?

These two things just don't seem related.