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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.