Script task using VB.NET to serialize data to XML file

Question

Post reply

Script task using VB.NET to serialize data to XML file

sqlgreenhand

SSCommitted

Points: 1639
More actions
December 7, 2011 at 11:18 am

#247339

I am learning SSIS using VB.NET to serialize data to XML file with a simple class created. I am using a list
(System.Collections.Generic) to collect data before passing to XML. If I use an array it works. Anyone has this experience before or I have missed something. Please instruct me. Thanks.:angry:
But somehow it always throws this error
"Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: There was an error generating the XML document. ---> System.TypeInitializationException: The type initializer for 'Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriterList1' threw an exception. ---> System.NullReferenceException: Object reference not set to an instance of an object."
<code>
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.IO
Imports System.Xml.Serialization
Imports System.Collections.Generic
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
'
' Add your code here
'
'Set up product object.
Dim p As New clsProduct()
p.Name = "Johnsmith"
p.Description = "New VB student"
p.Qty = "555"
Dim alist As New List(Of clsProduct)
alist.Add(p)
'Serialize object to a text file.
Dim objStreamWriter As StreamWriter = New StreamWriter("C:\\Projects\\Product.xml")
'Dim x As New XmlSerializer(p.GetType)
Dim x As XmlSerializer = New XmlSerializer(GetType(List(Of clsProduct)))
x.Serialize(objStreamWriter, alist)
objStreamWriter.Close()
Dts.TaskResult = ScriptResults.Success
End Sub
<Serializable()> Public Class clsProduct
Private mstrName As String
Private mstrDescription As String
Private mintQty As String
Public Sub New()
Name = ""
Description = "'"
Qty = ""
End Sub
Public Property Name() As String
Get
Name = mstrName
End Get
Set(ByVal Value As String)
mstrName = Value
End Set
End Property
Public Property Description() As String
Get
Description = mstrDescription
End Get
Set(ByVal Value As String)
mstrDescription = Value
End Set
End Property
Public Property Qty() As String
Get
Qty = mintQty
End Get
Set(ByVal Value As String)
mintQty = Value
End Set
End Property
End Class
End Class
</code>