<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
Weird SSIS recordset issue
21 posts, Page 2 of 3
««
1
2
3
»»
Weird SSIS recordset issue
Rate Topic
Display Mode
Topic Options
Author
Message
CozyRoc
CozyRoc
Posted Friday, November 20, 2009 1:56 PM
Ten Centuries
Group: General Forum Members
Last Login: Sunday, June 09, 2013 7:39 AM
Points: 1,106,
Visits: 2,120
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
stricknyn
stricknyn
Posted Friday, November 20, 2009 2:10 PM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:32 PM
Points: 264,
Visits: 577
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
CozyRoc
CozyRoc
Posted Friday, November 20, 2009 2:45 PM
Ten Centuries
Group: General Forum Members
Last Login: Sunday, June 09, 2013 7:39 AM
Points: 1,106,
Visits: 2,120
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
stricknyn
stricknyn
Posted Friday, November 20, 2009 2:52 PM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:32 PM
Points: 264,
Visits: 577
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
stricknyn
stricknyn
Posted Friday, November 20, 2009 3:01 PM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:32 PM
Points: 264,
Visits: 577
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
CozyRoc
CozyRoc
Posted Friday, November 20, 2009 4:14 PM
Ten Centuries
Group: General Forum Members
Last Login: Sunday, June 09, 2013 7:39 AM
Points: 1,106,
Visits: 2,120
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
stricknyn
stricknyn
Posted Friday, November 20, 2009 7:29 PM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:32 PM
Points: 264,
Visits: 577
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
CozyRoc
CozyRoc
Posted Friday, November 20, 2009 7:34 PM
Ten Centuries
Group: General Forum Members
Last Login: Sunday, June 09, 2013 7:39 AM
Points: 1,106,
Visits: 2,120
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
CozyRoc
CozyRoc
Posted Saturday, December 05, 2009 6:46 PM
Ten Centuries
Group: General Forum Members
Last Login: Sunday, June 09, 2013 7:39 AM
Points: 1,106,
Visits: 2,120
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
marc.w.atkinson
marc.w.atkinson
Posted Friday, December 11, 2009 1:43 PM
Forum 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 »
21 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
