Weird SSIS recordset issue

  • 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

  • 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

  • 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

  • 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/

  • 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

  • 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/

  • 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

  • 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

  • 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

  • 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/

  • 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/

  • 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

  • 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/

  • 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

  • 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?

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

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