InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:42:34 GMT20http://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]marc.w.atkinson (12/11/2009)[/b][hr]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.aspxDon'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.[/quote]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 :)Sun, 06 Jan 2013 11:38:10 GMTdutchdbahttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxOk, 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.aspxDon'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.Fri, 11 Dec 2009 13:43:03 GMTmarc.w.atkinsonhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]stricknyn (11/20/2009)[/b][hr][quote][b]CozyRoc (11/20/2009)[/b][hr]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 DataTableDim da As OleDbDataAdapter = New OleDbDataAdapterDim rs As Object = Me.Variables.rsPayeeSummaryda.Fill(dt, rs)Me.Variables.rsPayeeSummary = dt'release objectsMarshal.ReleaseComObject( rs )rs = nothingdt = nothingda = nothing[/quote]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[/quote]Strick,We have created script component, which loads specified input to ADO.NET DataSet object. You can check it [b][url=http://www.cozyroc.com/script/dataset-destination]here[/url][/b]. It is dependent on the CozyRoc [b][url=http://www.cozyroc.com/ssis/script-component]Script Component Plus[/url][/b], but you can use it as a reference if you want to.Sat, 05 Dec 2009 18:46:27 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]stricknyn (11/20/2009)[/b][hr]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[/quote]Yes, this is correct. You are confusing object with [b][url=http://www.vbdotnetheaven.com/UploadFile/rajeshvs/StructuresInVbDotNet04192005235119PM/StructuresInVbDotNet.aspx]Structure[/url][/b].Fri, 20 Nov 2009 19:34:50 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxI 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.StrickFri, 20 Nov 2009 19:29:53 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]stricknyn (11/20/2009)[/b][hr]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.countor would I have to declare an instance:dim dt as datatable = dtPayeeSummarydt.rows.count.reason I ask is because if I do have to declare an instance thats like having two copies and wasting memory correct?[/quote]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 [b]not[/b] have two copies.Fri, 20 Nov 2009 16:14:48 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxOh, 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.countor would I have to declare an instance:dim dt as datatable = dtPayeeSummarydt.rows.count.reason I ask is because if I do have to declare an instance thats like having two copies and wasting memory correct?Fri, 20 Nov 2009 15:01:21 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxAh 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!StrickFri, 20 Nov 2009 14:52:45 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]stricknyn (11/20/2009)[/b][hr][quote][b]CozyRoc (11/20/2009)[/b][hr]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 DataTableDim da As OleDbDataAdapter = New OleDbDataAdapterDim rs As Object = Me.Variables.rsPayeeSummaryda.Fill(dt, rs)Me.Variables.rsPayeeSummary = dt'release objectsMarshal.ReleaseComObject( rs )rs = nothingdt = nothingda = nothing[/quote]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[/quote]You have to implement script component destination and populate the data set object yourself.Fri, 20 Nov 2009 14:45:25 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]CozyRoc (11/20/2009)[/b][hr]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 DataTableDim da As OleDbDataAdapter = New OleDbDataAdapterDim rs As Object = Me.Variables.rsPayeeSummaryda.Fill(dt, rs)Me.Variables.rsPayeeSummary = dt'release objectsMarshal.ReleaseComObject( rs )rs = nothingdt = nothingda = nothing[/quote]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,StrickFri, 20 Nov 2009 14:10:30 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxYou 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 DataTableDim da As OleDbDataAdapter = New OleDbDataAdapterDim rs As Object = Me.Variables.rsPayeeSummaryda.Fill(dt, rs)Me.Variables.rsPayeeSummary = dt'release objectsMarshal.ReleaseComObject( rs )rs = nothingdt = nothingda = nothingFri, 20 Nov 2009 13:56:15 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]Phil Parkin (11/20/2009)[/b][hr][quote][b]stricknyn (11/20/2009)[/b][hr]Hi [b][i]CrazyRoc[/i][/b],...[/quote]Wow, you just got a cool new name! :-D[/quote]Yep, It has been going on for awhile. Sounds nice though :)Fri, 20 Nov 2009 13:47:26 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]CozyRoc (11/20/2009)[/b][hr]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 ADODBand 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 ;)[/quote]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 DataTableDim da As OleDbDataAdapter = New OleDbDataAdapterda.Fill(dt, Me.Variables.rsPayeeSummary)Me.Variables.rsPayeeSummary = dt'release objectsdt = nothingda = 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!StrickFri, 20 Nov 2009 13:42:11 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]Phil Parkin (11/20/2009)[/b][hr][quote][b]stricknyn (11/20/2009)[/b][hr]Hi [b][i]CrazyRoc[/i][/b],...[/quote]Wow, you just got a cool new name! :-D[/quote]Sorry, I misread your name :)StrickFri, 20 Nov 2009 13:27:08 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]stricknyn (11/20/2009)[/b][hr]Hi [b][i]CrazyRoc[/i][/b],...[/quote]Wow, you just got a cool new name! :-DFri, 20 Nov 2009 13:09:36 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxStrick,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 ADODBand 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 ;)Fri, 20 Nov 2009 11:40:58 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote]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 [b]MoveFirst[/b].Let us know how it goes.[/quote]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.MovefirstHowever because SSIS stores the recordset in an object variable, I'm not sure how to achieve a movefirst on the object.Thanks,StrickFri, 20 Nov 2009 11:22:24 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspx[quote][b]stricknyn (11/19/2009)[/b][hr]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[/quote]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 [b]MoveFirst[/b].Let us know how it goes.Fri, 20 Nov 2009 09:42:34 GMTCozyRochttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxHello,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,StrickFri, 20 Nov 2009 07:38:31 GMTstricknynhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxI think you might need to MULTICAST to enable the 'reuse' you are after ...Thu, 19 Nov 2009 08:45:18 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic821653-148-1.aspxHi 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,StrickThu, 19 Nov 2009 08:25:13 GMTstricknyn