stricknyn (11/19/2009)

---

Hi all,

I have a weird SSIS recordset issue. In my first data flow, I am summing and outputing some data (Only 2 colums, a count coulmn and a sum column) to a SSIS recordset. This is mainly for reuse purposes. In my second data flow task, I load the recordset into a .net datatable object and use the data in that task. In a third dataflow task when I attempt to use the data again (the same way) from the recordset object it crashes. I went back and checked the third data flow task and there were no records.

So my question is this: can the SSIS recordset be used more than once? Does it get cleared after first use of it?

Note: I have a work around in which I basically reloaded the recordset object with the same data after the first use of it in the second data flow task. But was just curious. If the recordset gets emptied after use.

Thanks,

Strick

Hi Strick,

The recordset is not cleared , but it is at the end after you finish executing the second data flow. What you have to do is move the pointer back to the first record, in the post-execute step of the second data flow. In this way the recordset will be ready again for use in the third data flow. To move the pointer try using the method MoveFirst.

Let us know how it goes.

stricknyn (11/20/2009)

---

Hi CrazyRoc,

...

Phil Parkin (11/20/2009)

---

Wow, you just got a cool new name!

CozyRoc (11/20/2009)

---

Strick,

The adapter you are using is just an adapter. It takes ADO recordset and populates .NET data set. This is making your memory consumption at least twice as much because you now have a copy of the data in both ADO recordset and the dataset.

From the approach you are using there are actually two solutions I could think of:

1. Store the data table you get from OleDbDataAdapter in package variable and use this managed data set in the 3rd data flow. I would recommend you dispose/release the recordset object after you load the data in the adapter. It is not needed.

2. Avoid using managed data set entirely. Use the ADO recordset directly. To do this you have to include reference to adodb library in your project. Then you have to include the following import:

Imports ADODB

and use the following code:

Dim rs As Recordset = CType(Me.Variables.rsPayeeSummary, Recordset)

and now you will have the familiar ADO Recordset in the rs variable and you can use your previous skills to pull the data from it ;)

Phil Parkin (11/20/2009)

---

Wow, you just got a cool new name!