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


Weird SSIS recordset issue


Weird SSIS recordset issue

Author
Message
CozyRoc
CozyRoc
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6968 Visits: 2235
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
stricknyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

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

Group: General Forum Members
Points: 6968 Visits: 2235
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
stricknyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1526 Visits: 690
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
stricknyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

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



CozyRoc
CozyRoc
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6968 Visits: 2235
stricknyn (11/20/2009)
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?



When you declare the dt variable you are declaring reference to the existing object, not a new instance. So to answer your question, you will not have two copies.

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


stricknyn
stricknyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1526 Visits: 690
I see. That's interesting because I always thought objects were ByVal when declared instead of ByRef. So that means Im assuming that if I change some data in the data table object, dtPayeeSummary will have it's data changed.

Strick



CozyRoc
CozyRoc
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6968 Visits: 2235
stricknyn (11/20/2009)
I see. That's interesting because I always thought objects were ByVal when declared instead of ByRef. So that means Im assuming that if I change some data in the data table object, dtPayeeSummary will have it's data changed.

Strick


Yes, this is correct. You are confusing object with Structure.

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


CozyRoc
CozyRoc
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

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


Strick,

We have created script component, which loads specified input to ADO.NET DataSet object. You can check it here. It is dependent on the CozyRoc Script Component Plus, but you can use it as a reference if you want to.

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


marc.w.atkinson
marc.w.atkinson
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 18
Ok, I am posting my solution here because this is the first forum I looked at when I started looking.

As it turns out you cannot reuse recordsets stuffed into SSIS object variables across multiple tasks.
There is a solution though:

This code with take your recordset and push it into an ADO.Net DataTable. From there you can do what you want with it.

DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["ExcelSet"].Value);

For some reason this empties your object variable. What???

Now what you have to do is take the data in your DataTable and push it back into your object variable. BUT, the other tasks (and the above code) are expecting an ADOBD.Recordset. So first you have to convert your datatable to an ADODB.Recordset. The code to do that is in the article linked below. Just copy and paste to your script code (note: code is in C#....may have to convert to VB.Net).

http://www.codeproject.com/KB/database/DataTableToRecordset.aspx

Don't forget to set a reference to Microsoft Active X Objects in your script (Project>Add Reference>COM tab>Microsoft Active X Data Objects 2.X....I used 2.7 but any will work). Add a Using ADODB (or Imports ADODB) to the top of your page.

Restuff your object variable:
Dts.Variables["ExcelSet"].Value = ConvertToRecordset(excelTable);

And you are off. You can then reuse your object variable across SSIS tasks. Whew! Coming from the OO world I was quite dismayed to find this didn't work automatically. And, really Microsoft, an ADODB recordset. How 1999.
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