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 Friday, November 20, 2009 1:56 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
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/

Post #822695
Posted Friday, November 20, 2009 2:10 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)
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



Post #822703
Posted Friday, November 20, 2009 2:45 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
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/

Post #822725
Posted Friday, November 20, 2009 2:52 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
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



Post #822731
Posted Friday, November 20, 2009 3:01 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
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?




Post #822738
Posted Friday, November 20, 2009 4:14 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
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/

Post #822775
Posted Friday, November 20, 2009 7:29 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
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



Post #822813
Posted Friday, November 20, 2009 7:34 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
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/

Post #822814
Posted Saturday, December 5, 2009 6:46 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
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/

Post #829469
Posted Friday, December 11, 2009 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 10:29 AM
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.
Post #833186
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse