SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Weird SSIS recordset issue


Weird SSIS recordset issue

Author
Message
stricknyn
stricknyn
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 690
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



Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51326 Visits: 21156
I think you might need to MULTICAST to enable the 'reuse' you are after ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
stricknyn
stricknyn
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 690
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
CozyRoc
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6634 Visits: 2235
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
stricknyn
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 690


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
CozyRoc
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6634 Visits: 2235
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 Wink

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


Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51326 Visits: 21156
stricknyn (11/20/2009)



Hi CrazyRoc,

...



Wow, you just got a cool new name! :-D


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
stricknyn
stricknyn
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 690
Phil Parkin (11/20/2009)
stricknyn (11/20/2009)



Hi CrazyRoc,

...



Wow, you just got a cool new name! :-D


Sorry, I misread your name Smile

Strick



stricknyn
stricknyn
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 690
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 Wink


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. Smile 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
CozyRoc
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6634 Visits: 2235
Phil Parkin (11/20/2009)
stricknyn (11/20/2009)



Hi CrazyRoc,

...



Wow, you just got a cool new name! :-D


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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search