Script task + xml imput

Question

Script task + xml imput

chatwithkrishan

Ten Centuries

Points: 1056
More actions
April 29, 2009 at 4:35 am

#133555

Public Sub Main()
sqlconn = New SqlConnection("data source=ss;initial catalog=ss;uid=sa;pwd=sa")
sqlcmdFinal = New SqlCommand("test", sqlconn)
sqlcmdFinal.CommandType = CommandType.StoredProcedure
sqlparam_XML_Record = New SqlParameter("@ReXML", SqlDbType.Xml)
Dim xm As Xml.XmlDocument = New Xml.XmlDocument()
Dim Str As String = Dts.Variables("t").Value.ToString()
xm.LoadXml(Str)
sqlparam_XML_Record.Value = xm.InnerXml
sqlcmdFinal.Parameters.Add(sqlparam_XML_Record)
'sqlparam_XML_Record.Value = Dts.Variables("t").Value
sqlconn.Open()
sqlcmdFinal.ExecuteNonQuery()
sqlconn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Str string got xml file ( < field1>1< /field1>)
when i run package i got
ERROR :Failed to convert parameter value from a XmlDocument to a String.
how to resolve ?

Viewing 2 posts - 1 through 1 (of 1 total)

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

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

chatwithkrishan (4/29/2009)
Public Sub Main()
sqlconn = New SqlConnection("data source=ss;initial catalog=ss;uid=sa;pwd=sa")
sqlcmdFinal = New SqlCommand("test", sqlconn)
sqlcmdFinal.CommandType = CommandType.StoredProcedure
sqlparam_XML_Record = New SqlParameter("@ReXML", SqlDbType.Xml)
Dim xm As Xml.XmlDocument = New Xml.XmlDocument()
Dim Str As String = Dts.Variables("t").Value.ToString()
xm.LoadXml(Str)
sqlparam_XML_Record.Value = xm.InnerXml
sqlcmdFinal.Parameters.Add(sqlparam_XML_Record)
'sqlparam_XML_Record.Value = Dts.Variables("t").Value
sqlconn.Open()
sqlcmdFinal.ExecuteNonQuery()
sqlconn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Str string got xml file ( 1)
when i run package i got
ERROR :Failed to convert parameter value from a XmlDocument to a String.
how to resolve ?

Can you debug the script and tell us on which line it fails?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/