XML is not read fully by SSIS package

问题:

I'm trying to read XML files in SSIS package. The thing is that when I'm trying to read XML source file using XML SOURCE process it doesn't read full content of this file. In my scenario I have some files in folder which I read using "For Each Loop". Then when I need to map colums available in this file I don't see one property : "Date_of_adding" what is very important for me also. I have read that I should divide XML reading in two parts and later join result of each path into one result but I can't do that : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ca7dabf5-9810-4661-b8cb-94c2fd0fddde/loading-xml-file-with-header-and-detail-information-to-sql-sever-table?forum=sqlintegrationservices. Can you help me and tell how to read missing level? I have following components already created:

回答1:

I would suggest defining a C# script component as source - this provides the possibility to handle the XML as required without the hassle of using a XSD. Anyways, if it comes to way more complex XML scripts, an additional XSLT would be usefull. However, following an example C# console app code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication22
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Start...");

            string sPath = "C:\\temp\\xs\\a.xml";

            XDocument xdoc = XDocument.Load(sPath);

            string snumer_tabeli = xdoc.Element("tabela_kursow").Element("numer_tabeli").Value.ToString();
            string sdata_publikacji = xdoc.Element("tabela_kursow").Element("data_publikacji").Value.ToString();

            IEnumerable<XElement> elements = xdoc.Element("tabela_kursow").Elements("pozycja");
            foreach (XElement element in elements)
            {
                // Start of SSIS Buffer Column adding...
                Console.WriteLine("numer_tabeli ---> " + snumer_tabeli);
                Console.WriteLine("data_publikacji ---> " + sdata_publikacji);
                foreach (XElement xelement in element.Descendants())
                {
                    Console.WriteLine(xelement.Name + " ---> " + xelement.Value.ToString());
                }
                // End of SSIS Buffer Column adding...
                Console.WriteLine("\n");
            }

            Console.WriteLine("...End");
            Console.ReadKey();
        }
    }
}

And here an example SSIS script component.

public override void CreateNewOutputRows()
{
    XDocument xdoc = XDocument.Load(sPath);

    string snumer_tabeli = xdoc.Element("tabela_kursow").Element("numer_tabeli").Value.ToString();
    string sdata_publikacji = xdoc.Element("tabela_kursow").Element("data_publikacji").Value.ToString();

    IEnumerable<XElement> elements = xdoc.Element("tabela_kursow").Elements("pozycja");
    foreach (XElement element in elements)
    {
        Output0Buffer.AddRow();
        Output0Buffer.numertabeli = snumer_tabeli;
        Output0Buffer.datapublikacji = sdata_publikacji;
        foreach (XElement xelement in element.Descendants())
        {
            switch (xelement.Name.ToString())
            {
                case "nazwa_waluty":
                    Output0Buffer.nazwawaluty = xelement.Value.ToString();
                    break;

                case "przelicznik":
                    Output0Buffer.przelicznik = xelement.Value.ToString();
                    break;

                case "kod_waluty":
                    Output0Buffer.kodwaluty = xelement.Value.ToString();
                    break;

                case "kurs_sredni":
                    Output0Buffer.kurssredni = xelement.Value.ToString();
                    break;

                default:
                    break;
            }
        }
    }
}

sPath is a variable I defined and which I loaded from a SSIS package variable. In order to use this component, you have to add a reference to System.XML.Linq to the script component. And for the time being, I defined ALL output columns as String (dt_wstr(50) - you will have to take care of transformations either within the script or in the next task in your dataflow.

回答2:

Hm, interesting case.
Basically, you have a table currency root element and not being able to read its properties - date of adding.
SSIS XML Source derives set of outputs from the XML structure and the top level entry is not mapped to any output; this is by design. Some explanations of this phenomena are given at MSDN.
The solution I used in similar cases - add another root node before table currency node, so your XML will look like

<?xml version="1.0" ?>
<root_node>
 <table_currency ...>
 ...
 </table_currency>
</root_node>

You can perform this with XML Task using XSLT transform like this

<xsl:template match="/">
 <root_node>
  <xsl:copy-of select="node()"/>
 </root_node>
</xsl:template>