Answered by:
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. BrownThursday, 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- Marked as answer by Jerry Nee Friday, March 4, 2011 5:25 AM
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
Thursday, February 17, 2011 6:16 PM -
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, MCTThursday, 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- Marked as answer by Jerry Nee Friday, March 4, 2011 5:25 AM
Friday, February 25, 2011 6:55 PM