Using SSIS Script task to read XML from variable object LoadXml fails
<p>I have a SQL statement (SQL server 2014) that builds out XML formatted data. I am pulling this into an SSIS (2013) object variable. If I put the SQL into a data flow task to go into a text file, it works. BUT, no encoding header. Because of the size of the return data, converting to a NVARCHAR is not an option - data would get cut off.</p>

<p>So, instead I have a sql task, same query:
ResultSet = XML; Variable datatype = Object (it fails if I try to string)</p>

<p>So, after much time here - I found all sorts of great stuff to use XmlWriter and XmlWriterSettings. Awesome, great. BUT -- I can't seem to get the script task (C#) to read (or format/define to) the XML object correctly. I keep getting a "DTS Script Task has encountered an exception in user code"</p>

<p>I set a break point at the LoadXML statement - verified the variable does have the XML data - tho interestingly enough it gets surrounded with  ...  . In debug mode the specific error is "Data at the root level is invalid. Line 1, position 1."</p>

<p>It throws the exception at the LoadXML statement -- what am I missing? Do I need to define the XML layout (fields)? The commented out test string works w/o issue. no fail. header written.</p>

<p>Any ideas?</p>

<p><strong>Update</strong>: it also fails with a string version of my test data, without the root tag. </p>

<pre><code>public void Main()
        {

            Variables varCollection = null;
            Dts.VariableDispenser.LockForWrite("User::PCC_XML");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            XmlDocument xdoc = new XmlDocument();

            XmlWriterSettings settings = new XmlWriterSettings
            {
                Encoding = Encoding.UTF8,
                ConformanceLevel = ConformanceLevel.Document,
                OmitXmlDeclaration = false,
                CloseOutput = true,
                Indent = true,
                IndentChars = "  ",
                NewLineHandling = NewLineHandling.Replace
            };    

            xdoc.LoadXml(varCollection["User::PCC_XML"].Value.ToString());

            //xdoc.LoadXml("&lt;xml&gt;&lt;foo&gt;&lt;/foo&gt;&lt;/xml&gt;");

            using ( StreamWriter sw = File.CreateText("C:\\test_xml.xml") )
            using ( XmlWriter writer = XmlWriter.Create(sw, settings))
            {
                xdoc.WriteContentTo(writer);
                writer.Close();
            }    

            Dts.TaskResult = (int)ScriptResults.Success;
        }
</code></pre>

<p>a skinnied down version of my XML is (including the ROOT that got added)</p>

<pre><code>&lt;ROOT&gt;
    &lt;Universal_letters&gt;
        &lt;Universal_letter&gt;
            &lt;docID&gt;123456&lt;/docID&gt;
            &lt;Letter_Code&gt;123&lt;/Letter_Code&gt;
            &lt;Callback_Phone&gt;1-888-111-111&lt;/Callback_Phone&gt;
            &lt;Delivery_Methods&gt;
                &lt;Delivery_Method /&gt;
                &lt;Print_Queue /&gt;
            &lt;/Delivery_Methods&gt;
            &lt;Requested_Date&gt;2016-07-28&lt;/Requested_Date&gt;
            &lt;Consumer_First_Name&gt;Bubba&lt;/Consumer_First_Name&gt;
            &lt;Consumer_Last_Name&gt;Bubbster&lt;/Consumer_Last_Name&gt;
            &lt;Consumer_Address_1&gt;123 Street Way&lt;/Consumer_Address_1&gt;
        &lt;/Universal_letter&gt;
    &lt;/Universal_letters&gt;
&lt;/ROOT&gt;
</code></pre>