Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Weird SSIS recordset issue Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 8:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:16 PM
Points: 265, Visits: 587
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




Post #821653
Posted Thursday, November 19, 2009 8:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 5,158, Visits: 12,001
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #821681
Posted Friday, November 20, 2009 7:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:16 PM
Points: 265, Visits: 587
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



Post #822399
Posted Friday, November 20, 2009 9:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:17 PM
Points: 1,118, Visits: 2,224
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/

Post #822510
Posted Friday, November 20, 2009 11:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:16 PM
Points: 265, Visits: 587


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




Post #822575
Posted Friday, November 20, 2009 11:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:17 PM
Points: 1,118, Visits: 2,224
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/

Post #822587
Posted Friday, November 20, 2009 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 5,158, Visits: 12,001
stricknyn (11/20/2009)



Hi CrazyRoc,

...



Wow, you just got a cool new name!



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #822665
Posted Friday, November 20, 2009 1:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:16 PM
Points: 265, Visits: 587
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



Post #822678
Posted Friday, November 20, 2009 1:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:16 PM
Points: 265, Visits: 587
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



Post #822687
Posted Friday, November 20, 2009 1:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:17 PM
Points: 1,118, Visits: 2,224
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/

Post #822691
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse