SSIS XML Column Has Byte Mark

Question

I am using the oledb data source in an SSIS package to pull a column from a database. A column is an XML data type. In SSIS, it is automatically recognized as the DT_NTEXT data type. It will go to the script component, where I try to load it into the System.Xml.XmlDocument file. This is the code I use to get the XML data into a string:

System.Text.Encoding.Default.GetString(Row.Data.GetBlobData(0, Row.Data.Length))

Is it correct?

One strange thing that I see is that on one server I get a byte mark in the resulting string, and the other does not. I would not mind understanding why this is, but my real desire is how to get this line without specification.

Help me, stack overflow, you are my only hope ...

Answer 1

This is the only way I was able to get it to work:

System.Text.UnicodeEncoding.Unicode.GetString(...).Trim()

.Trim () removes the specification. I'm not sure if this is the “right” way, but this is the only thing that has worked so far.