SSIS 2012 not showing correct Available External Columns in Xml Source Task.

 

The “ORDER” type in the following XML document will not be shown in the available external columns dialog in SSIS 2012.

<?xml version=”1.0″ encoding=”utf-8″?>
<ORDER>
  <ID>1</ID>
  <ORDERLINE>
    <ID>1</ID>
      <PRODUCTNAME>Test1</PRODUCTNAME>
  </ORDERLINE>
  <ORDERLINE>
    <ID>2</ID>
      <PRODUCTNAME>Test2</PRODUCTNAME>
  </ORDERLINE>
</ORDER>

 

Result

image

 

If you want the ORDER type to show up, add a root element to the xml document:

 

<?xml version=”1.0″ encoding=”utf-8″?>
<ROOT>
    <ORDER>
      <ID>1</ID>
      <ORDERLINE>
        <ID>1</ID>
          <PRODUCTNAME>Test1</PRODUCTNAME>
      </ORDERLINE>
      <ORDERLINE>
        <ID>2</ID>
          <PRODUCTNAME>Test2</PRODUCTNAME>
      </ORDERLINE>
    </ORDER>
</ROOT>

 

Now you will see both ORDER and ORDERLINE show up:

image

 

To add a root node to an XML document in SSIS you can use the following script code:

public void Main()
{
try
{
string selectedXmlFile = Dts.Variables[@"User::SelectedXmlFile"].Value.ToString();
string AlteredXmlFile = (String.Format("{0}x", selectedXmlFile));
Dts.Variables[@"User::AlteredXmlFile"].Value = selectedXmlFile;
XmlDocument oldDoc = new XmlDocument();
oldDoc.Load(selectedXmlFile);
XmlDocument newDoc = new XmlDocument();
XmlElement root = newDoc.CreateElement("ROOT");
newDoc.InsertAfter(root, null);
XmlNode content = newDoc.ImportNode(oldDoc.ChildNodes[2], true);
root.AppendChild(content);
newDoc.Save(AlteredXmlFile);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Variables[@"User::LogMessage"].Value = ex.ToString();
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *