Weird SSIS recordset issue

Question

Weird SSIS recordset issue

Viewing 6 posts - 16 through 20 (of 20 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

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 SSCertifiable Points: 6382 More actions · Answer 2

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 One Orange Chip Points: 28499 More actions · Answer 3

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 One Orange Chip Points: 28499 More actions · Answer 4

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 SSC Rookie Points: 45 More actions · Answer 5

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.

dutchdba SSC Veteran Points: 281 More actions · Answer 6

marc.w.atkinson (12/11/2009)
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.

Luckily stumbled across this post as I was trying to debug my script tasks to figure out what was happening with the variable. I had a few breakpoints in the package and noticed this object variable still showed up as COM object, but for some reason the first adapter.Fill empties this object.

Anyway glad I found it, this solved the issue I had 🙂

/*----------------------------------------------------------------------------------*/
Always eager to learn.