none
SSIS 2005 XML Source Problem - No columns shownig

    Question

  • Problem: XML Source shows no columns

    I have a file that is sent to me once a day. Need to automate the import of file into SQL Server 2005 table. I'm trying to load this xml file source "SampleOne.xml":

    <?xml version="1.0"?>
    <SampleOne xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.whatever.com/schema/2009-01/motor/xdr" >
        <AsOfDate>02/14/2011 16:00:00</AsOfDate>
        <SampleOne>  24325.55</SampleOne>
    </SampleOne>
    
    
    

    I've created a project with a data flow and within the data flow I've added an XML source component. Referencing my SampleOne.xml file I've used the "Generate XSD..." to create SampleOne.xsd. Clicking on columns displays nothing:

    <?xml version="1.0"?>
    <xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.whatever.com/schema/2009-01/motor/xdr" xmlns:xs="http://www.w3.org/2001/XMLSchema">
     <xs:element name="SampleOne">
      <xs:complexType>
       <xs:sequence>
        <xs:element minOccurs="0" name="AsOfDate" type="xs:string" />
        <xs:element minOccurs="0" name="SampleOne" type="xs:decimal" />
       </xs:sequence>
      </xs:complexType>
     </xs:element>
    </xs:schema>
    

    What is wrong here? What are my work arounds?


    Brett L. Brown
    Thursday, February 17, 2011 6:03 PM

Answers

  • XSLT is NOT a simple solution. I ended up using the "Execute Process Task" and crafting my own C# specific console app executable to parse the file and add the root node.

    // Source: Article ID: 307548 - Last Review: March 20, 2008 - Revision: 4.1 Microsoft Support
    
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Text;
    using System.Xml;
    
    namespace XmlFileReader
    {
      class Program
      {
        static void Main(string[] arg)
        {
          string setPathToFilename = parseArgs(arg);
    
          if (setPathToFilename.Length > 0)
          {
            Console.WriteLine("Past Length check...");
            if (File.Exists(setPathToFilename))
            {
              XmlTextReader reader = new XmlTextReader(setPathToFilename);
              reader.WhitespaceHandling = WhitespaceHandling.None;
    
              List<string> xmlValues = new List<string>();
    
              while (reader.Read())
              {
                switch (reader.NodeType)
                {
                  case XmlNodeType.Text:
                    xmlValues.Add(reader.Value.Trim());
                    break;
                }
              }
    
              string customTextFile = setPathToFilename.Replace(".xml", ".txt");
    
              if (File.Exists(customTextFile))
              {
                File.Delete(customTextFile);
              }
    
              if (!File.Exists(customTextFile))
              {
                using (StreamWriter sw = File.CreateText(customTextFile))
                {
                  sw.Write("\"");
                  sw.Write(xmlValues[0]);
                  sw.Write("\",\"");
                  sw.Write(xmlValues[1]);
                  sw.WriteLine("\"");
                }
              }
            }
          }
        }
    
        private static string parseArgs(string[] arg)
        {
          try
          {
            if (arg.Length == 0)
            {
              Console.WriteLine("XmlFileReader syntax: ");
              Console.WriteLine();
    
              Console.WriteLine("XmlFileReader [Path_And_Filename]");
              Console.WriteLine(" Path_And_Filename");
              Console.WriteLine();
    
              Console.WriteLine("The path to the Ercot XML file must be specified.");
              Console.WriteLine();
              return "";
            }
    
            string userParameter = arg[0];
            return userParameter;
          }
          catch (Exception ex)
          {
            Console.WriteLine(ex.Message);
            return "";
          }
        }
      }
    }

     


    Brett L. Brown
    Friday, February 25, 2011 6:55 PM

All replies

  • You need additional external nodes around your XML, otherwise you must see just a single picked up.

    This is a good post in general on how to: http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, February 17, 2011 6:16 PM
    Moderator
  • This is the problem of the Root node not showing.  It treats the root node as a kind table with rows.  I suggest looking at Matt Masson's blog about using XSLT to add a root outside of your root.  http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx


    Russel Loski, MCT
    Thursday, February 17, 2011 6:40 PM
  • XSLT is NOT a simple solution. I ended up using the "Execute Process Task" and crafting my own C# specific console app executable to parse the file and add the root node.

    // Source: Article ID: 307548 - Last Review: March 20, 2008 - Revision: 4.1 Microsoft Support
    
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Text;
    using System.Xml;
    
    namespace XmlFileReader
    {
      class Program
      {
        static void Main(string[] arg)
        {
          string setPathToFilename = parseArgs(arg);
    
          if (setPathToFilename.Length > 0)
          {
            Console.WriteLine("Past Length check...");
            if (File.Exists(setPathToFilename))
            {
              XmlTextReader reader = new XmlTextReader(setPathToFilename);
              reader.WhitespaceHandling = WhitespaceHandling.None;
    
              List<string> xmlValues = new List<string>();
    
              while (reader.Read())
              {
                switch (reader.NodeType)
                {
                  case XmlNodeType.Text:
                    xmlValues.Add(reader.Value.Trim());
                    break;
                }
              }
    
              string customTextFile = setPathToFilename.Replace(".xml", ".txt");
    
              if (File.Exists(customTextFile))
              {
                File.Delete(customTextFile);
              }
    
              if (!File.Exists(customTextFile))
              {
                using (StreamWriter sw = File.CreateText(customTextFile))
                {
                  sw.Write("\"");
                  sw.Write(xmlValues[0]);
                  sw.Write("\",\"");
                  sw.Write(xmlValues[1]);
                  sw.WriteLine("\"");
                }
              }
            }
          }
        }
    
        private static string parseArgs(string[] arg)
        {
          try
          {
            if (arg.Length == 0)
            {
              Console.WriteLine("XmlFileReader syntax: ");
              Console.WriteLine();
    
              Console.WriteLine("XmlFileReader [Path_And_Filename]");
              Console.WriteLine(" Path_And_Filename");
              Console.WriteLine();
    
              Console.WriteLine("The path to the Ercot XML file must be specified.");
              Console.WriteLine();
              return "";
            }
    
            string userParameter = arg[0];
            return userParameter;
          }
          catch (Exception ex)
          {
            Console.WriteLine(ex.Message);
            return "";
          }
        }
      }
    }

     


    Brett L. Brown
    Friday, February 25, 2011 6:55 PM