Weird SSIS recordset issue

Question

Weird SSIS recordset issue

stricknyn

SSCertifiable

Points: 6560
More actions
November 19, 2009 at 8:25 am

#385815

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

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

I think you might need to MULTICAST to enable the 'reuse' you are after ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

stricknyn SSCertifiable Points: 6560 More actions · Answer 2

Hello,

Thanks for your reply. However, the issue does not stem from attempting to reuse the recordset object. I am able to do that. The issue is after I use a recordset object it automatically gets cleared out.

Thanks,

Strick

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

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.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

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.

Hi CrazyRoc,

Thanks, this does make some sense. I remember the ADO recordset from my VB5 and VB6 days. Since my recordset is stored in an SSIS object variable, how would I use the movefirst method though?

Here's how I use it in my script component:

Dim dt As DataTable = New DataTable

Dim da As OleDbDataAdapter = New OleDbDataAdapter

da.Fill(dt, Me.Variables.rsPayeeSummary)

I then use the data table in in this dataflow t(2nd) task. The next dataflow task (3rd) , the SSIS object variable Me.Variables.rsPayeeSummary (per your post) is at the end. So it sounds like I'd use the movefirst in my second task. In VB6 this would be Me.Variables.rsPayeeSummary.Movefirst

However because SSIS stores the recordset in an object variable, I'm not sure how to achieve a movefirst on the object.

Thanks,

Strick

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

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 😉

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Phil Parkin SSC Guru Points: 246959 More actions · Answer 6

stricknyn (11/20/2009)
Hi CrazyRoc,
...

Wow, you just got a cool new name! 😀

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

stricknyn SSCertifiable Points: 6560 More actions · Answer 7

Phil Parkin (11/20/2009)
stricknyn (11/20/2009)
Hi CrazyRoc,
...
Wow, you just got a cool new name! 😀

Sorry, I misread your name 🙂

Strick

stricknyn SSCertifiable Points: 6560 More actions · Answer 8

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 😉

Hi CozyRoc,

Interesting stuff. I like #1 as when I made the switch from VB6 to .NET back in 2003 I vowed never to go back unless a project called specifically for VB6. 🙂 So with that said, is it ok to load the datatable from the data adapter and then release the recordset by just setting it = to the data table?

Something like:

Dim dt As DataTable = New DataTable

Dim da As OleDbDataAdapter = New OleDbDataAdapter

da.Fill(dt, Me.Variables.rsPayeeSummary)

Me.Variables.rsPayeeSummary = dt

'release objects

dt = nothing

da = nothing

Also, if there is a way in which I can load the recordset as a .NET (instead of ADO)one in the first dataflow task, I could do that as well.

Thanks!

Strick

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

Phil Parkin (11/20/2009)
stricknyn (11/20/2009)
Hi CrazyRoc,
...
Wow, you just got a cool new name! 😀

Yep, It has been going on for awhile. Sounds nice though 🙂

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

You can definitely load your data directly in data set. However you have to implement a script component for that. Regarding the release of the COM object I would do the following:

Imports System.Runtime.InteropServices

...

Dim dt As DataTable = New DataTable

Dim da As OleDbDataAdapter = New OleDbDataAdapter

Dim rs As Object = Me.Variables.rsPayeeSummary

da.Fill(dt, rs)

Me.Variables.rsPayeeSummary = dt

'release objects

Marshal.ReleaseComObject( rs )

rs = nothing

dt = nothing

da = nothing

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

stricknyn SSCertifiable Points: 6560 More actions · Answer 11

CozyRoc (11/20/2009)
You can definitely load your data directly in data set. However you have to implement a script component for that. Regarding the release of the COM object I would do the following:
Imports System.Runtime.InteropServices
...
Dim dt As DataTable = New DataTable
Dim da As OleDbDataAdapter = New OleDbDataAdapter
Dim rs As Object = Me.Variables.rsPayeeSummary
da.Fill(dt, rs)
Me.Variables.rsPayeeSummary = dt
'release objects
Marshal.ReleaseComObject( rs )
rs = nothing
dt = nothing
da = nothing

I would love to just load my data in data flow task right into a .NET dataset. Currently in dataflow task 1, Im using a recordset destination with the SSIS object variable set as rsPayeeSummary. The other dataflow tasks (per the previous post) then use rsPayeeSummary. What's the recommended route to instead load a .NET data table/set into rsPayeeSummary instead of a recordset destination?

Thanks,

Strick

COZYROC One Orange Chip Points: 28499 More actions · Answer 12

stricknyn (11/20/2009)
CozyRoc (11/20/2009)
You can definitely load your data directly in data set. However you have to implement a script component for that. Regarding the release of the COM object I would do the following:
Imports System.Runtime.InteropServices
...
Dim dt As DataTable = New DataTable
Dim da As OleDbDataAdapter = New OleDbDataAdapter
Dim rs As Object = Me.Variables.rsPayeeSummary
da.Fill(dt, rs)
Me.Variables.rsPayeeSummary = dt
'release objects
Marshal.ReleaseComObject( rs )
rs = nothing
dt = nothing
da = nothing
I would love to just load my data in data flow task right into a .NET dataset. Currently in dataflow task 1, Im using a recordset destination with the SSIS object variable set as rsPayeeSummary. The other dataflow tasks (per the previous post) then use rsPayeeSummary. What's the recommended route to instead load a .NET data table/set into rsPayeeSummary instead of a recordset destination?
Thanks,
Strick

You have to implement script component destination and populate the data set object yourself.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

stricknyn SSCertifiable Points: 6560 More actions · Answer 13

Ah I see. Makes sense. Looks like this way is a little more work, but is very usefull if one wants to go right to datatable object from dataflow I think I may do that though. Thanks for all your help!

Strick

stricknyn SSCertifiable Points: 6560 More actions · Answer 14

Oh, forgot one last question on this. If I load the data right into datatable object in my first dataflow task, do I have to declare an instance of this since I have to save the datatable object to the SSIS object variable?

so for example if I have an SSIS object variable named dtPayeeSummary and in my script component destination I load this. When I use it in my other data flow tasks will it know that this is a datatable?

ie. to get row count dtPayeeSummary.rows.count

or would I have to declare an instance:

dim dt as datatable = dtPayeeSummary

dt.rows.count.

reason I ask is because if I do have to declare an instance thats like having two copies and wasting memory correct?