Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

PublicSub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

EndSub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

PublicClass ScriptMain

PublicSub Main()

Dim dtAsNew System.Data.DataTable

Dim oleadAsNew Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim drAs DataRow

ForEach drIn dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

EndSub

EndClass

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class