I have a script component in a data flow that is exhibiting some strange behavior.In the PreExecute event of the data flow,Istuff a recordset intoa variable that is declared at the data flow scope. Within the data flow, I use a script component to read in the data from the recordset. Example: Dim olead As New Data.OleDb.OleDbDataAdapter Dim dt1 As New System.Data.DataTable Dim row As System.Data.DataRow olead.Fill(dt1, Me.Variables.rsIntRateStrata) If I display the count of the records in the data table dt1, it shows 42 rows, which is correct. Run the package, everything runs as expected. So far, so good. Now, I set up another source/destination within the same data flow, as well as a script component between them, same as the first flow described above. Now my data flow has two parallel flows (different source & destinations). I copy the same script logic from the first flow into the second. Run the package- no errors, everything is fine... except when I inspect the data, it looks like the transformation isn't working correctly in the second script. So I display a messagebox of each script component during run time. The first component displays 42 records, while the second displays 0 records? Same variable. Same data flow. So I delete the first (original) flow from my data flow. Run the package again. Now the messagebox says 42. What is happening here? Do I have to create two variables to duplicate the same recordset if I need to use it multiple times within the same data flow? Is this a bug? -Kory

Strange. Its as if the object in the variable has some sort of pinter indicating where it is at in the dataset. You could try casting the variable as an OLEDB dataset and then cloning it in each script component. That way each script gets its own copy of the data. Just an idea. -Jamie

Hi Kory, I just managed to replicate the problem using my example here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspxand am working on a fix. Watch this space. -Jamie

Thanks for the prompt response Jamie. I look forward to what you find out. BTW- your blog is great, I read it all the time. -Kory

KoryS wrote: Thanks for the prompt response Jamie. I look forward to what you find out. BTW- your blog is great, I read it all the time. -Kory cool. thanks. Actually I've been looking at this and can't figure it out. I've created a band new variables and used a script task to copy the contents of variable1 into variable2 - then use variable2 in my second script component...and STILL it happens. I'm flumoxed. -Jamie

Ok, does this sound like a problem for Microsoft PSS? Anyone from Microsoft out there want to chime in?? This shouldn't be a show-stopper, but I would like to know if this behavior is by design or truly a defect. Reuse of variables seems like something that should be supported... -Kory

KoryS wrote: Ok, does this sound like a problem for Microsoft PSS? Anyone from Microsoft out there want to chime in?? This shouldn't be a show-stopper, but I would like to know if this behavior is by design or truly a defect. Reuse of variables seems like something that should be supported... -Kory Kory, I've submitted a bug here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265841along with a repro. perhaps you could go and vote. And leave a comment. -Jamie

Kory, Can you not just use a MULTICAST component instead of two source script components? -Jamie

It isn't a bug. Recordsets are generally single-use collections. When you call that Fill method, it walks forward through the recordset and copies the data into the DataTable until the recordset is consumed. Yes, there is a pointer. There are various cursor types that can let you rewind to the beginning or move the pointer forward and backward. Frankly, I don't know what cursor type these SSIS recordsets are, but you don't want multiple readers trying to read/rewind the same recordset, so rewinding is irrelevant. A possible solution is to call the Clone method on the recordset before you call Fill, but I don't know how to access the ADODB object model anymore.

That seems correct, based on my experimentation. If you run the Fill command twice in the same script, the data table is empty the second time.

JayH wrote: It isn't a bug. Recordsets are generally single-use collections. When you call that Fill method, it walks forward through the recordset and copies the data into the DataTable until the recordset is consumed. Yes, there is a pointer. There are various cursor types that can let you rewind to the beginning or move the pointer forward and backward. Frankly, I don't know what cursor type these SSIS recordsets are, but you don't want multiple readers trying to read/rewind the same recordset, so rewinding is irrelevant. A possible solution is to call the Clone method on the recordset before you call Fill, but I don't know how to access the ADODB object model anymore. Hi Jay, yeah, I wanted a .Clone method. That's what the bug is saying - not that the current beaviour is wrong. I know ADODB got shifted but I don't know where to. I have this code: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspxthat would allow you to traverse the recordset but without the ADODB reference there's no way to get it working. -Jamie

jwelch wrote: That seems correct, based on my experimentation. If you run the Fill command twice in the same script, the data table is empty the second time. J, Yeah, correct. The interesting thing is that you get the same behaviour if you call .Fill() in different script components. If you access it concurrently then you get an arbitrary number in each - which in a way is kinda cool If you're at all interested (I doubt it at this stage)I've posted a repro at Connect (link above). -Jamie

I got it working, but it's not very pretty. First, I followed the steps in the above article from Jamie's blog. The ADODB.dll is not in the location referenced (might just be my machine), but by searching my drive, I found one and copied it to %windir%\Microsoft.net\framework\v2.0.xxxxx as suggested in the article. Then I could add ADODB to my references in the script task. Since the recordset is stored as an object, you have to cast it to an ADODB.Recordset object. Code SnippetDim adoRS As ADODB.Recordset = CType(Me.Variables.resultset, ADODB.Recordset) Then you can call the clone function on it. However, you still can't call the OleDbDataAdapter.Fill method to convert it to an ADO.NET DataTable object. If you do, you get exactly the same behavior we were seeing before, even though the Fill is being done from the cloned recordset. Something about the Fill method seems to close everything associated with the recordset for good. If you alter your script to use the old ADODB model for iterating the rows and fields, though, everything works fine.

jwelch wrote: I got it working, but it's not very pretty. First, I followed the steps in the above article from Jamie's blog. The ADODB.dll is not in the location referenced (might just be my machine), but by searching my drive, I found one and copied it to %windir%\Microsoft.net\framework\v2.0.xxxxx as suggested in the article. Then I could add ADODB to my references in the script task. Since the recordset is stored as an object, you have to cast it to an ADODB.Recordset object. Code SnippetDim adoRS As ADODB.Recordset = CType(Me.Variables.resultset, ADODB.Recordset) Then you can call the clone function on it. However, you still can't call the OleDbDataAdapter.Fill method to convert it to an ADO.NET DataTable object. If you do, you get exactly the same behavior we were seeing before, even though the Fill is being done from the cloned recordset. Something about the Fill method seems to close everything associated with the recordset for good. If you alter your script to use the old ADODB model for iterating the rows and fields, though, everything works fine. Good work John. I know thatafter I wrote that first blog entry they changed where the ADODB was located wich is partly why the blog entry isn't relevant anymore. Mind you, I still think MULTICAST would solve the problem. -Jamie

I agree with you on the MULTICAST. At a certain point, it became more about proving that it could be done, than about it being practical.

Hi,So the conclutions are dont use fill use ADODB...?Does someone know how to access ADODB from the script component?Thanks,Ronen

I'll mark an answer on this thread now, because I've experienced this same behaviour.No - the conclusion is not "don't use ADODB". The conclusion is that the enumerator for an ADODB recordset is "global". For any single ADODB recordset there is only one enumerator, and having two processes loop over the one recordset causes them to share that enumerator. This can happen inside a Script component, as the OP had happen to him, or it can occur when you use a recordset returned from an Execute SQL Task in two Foreach Loop Containers.The conclusion in this particular case is:Use the Script as a Source, generate your rows in the data flow from the ADODB recordset, then use the Multicast component to use that data flow in multiple places within your data flow.The conclusion in general is:Don't use a single ADODB recordset in more than one place. If you need to do so, "regenerate" it.

Hi Guys, I think I found a work around for the problem submitted at the beginning of this thread. At first we can store the record set data inside a variable, by using dataflow or SQL task. Then after we have the data stored in variable we can fill the data into a datatable Dim like this inside a script component: olead.Fill(dt1, Me.Variables.rsIntRateStrata) or Like that in case you are using script task. olead.Fill(dt1, dts.variables(“rsIntRateStrata”)) Right away we can store the data back into a SSIS variable like this: Dts.variable(“SSIS_dataTable”) = dt1 From this moment on we have the dataTable rowset inside this variable, ready for use as much as we like anywhere we like. Script component: Ctype(Variables.SSIS_dataTable, System.Data.DataTable) Script task: Ctype(dts.Variables(“SSIS_dataTable”), System.Data.DataTable) BTW Using This variable with For Each Loop Container works perfect ….EnjoyJ Thanks, Ronen

RonenA: This worked perfect when storing the object in the variable by using Dts.variable(“SSIS_dataTable”) = Ctype(dt1,system.data.datatable) and then pulling it back out similarly. I am seeking a solution for this issue in C# if anyone has it I'd love to see it. Thank you

The equivalent to: Dts.variable(“SSIS_dataTable”) = Ctype(dt1,system.data.datatable) in C# is: Dts.Variable["SSIS_dataTable"] = dt1 As System.Data.DataTable; Talk to me now on

can somebody send me the complete code, its till not working for me. this is wat i am uinsg Dim ad As New OleDb.OleDbDataAdapter Dim dt1 As New DataTable Dim row As DataRow Dim col As DataColumn Dim sMsg As String ad.Fill(dt1, Me.Variables.Result) ' this is where i am trying to assign values again Me.Variables.Result2 = CType(dt1, System.Data.DataTable) ad.Fill(dt1, Me.Variables.Result) 'This produces new rows in the dataflow…………. For Each row In dt1.Rows With myOutputBuffer .AddRow() .loadid = CLng(row("load_id")) .extractid = CInt(row("extract_id")) End With Next student

In 2005 when using that exact code above I am getting Error 1 Value of type 'Microsoft.SqlServer.Dts.Runtime.Variable' cannot be converted to 'System.Data.DataTable'.

Hi JohnK.NET, Please make sure you using a variable of datatype "Object", any other datatype won't work here. thanks, Ronen