ssis import problems to mapping some data into xml files

Question

I need to import some data from XML file using a SSIS package to SQL Server but when I generate xsd file (from SSIS) some necessary data into XML file are not mapped as a column to output:

Here is a very similar model of xml file i'm using:

• xml format model

It's required to import the data in line 2 (DocumentID, ExportID, JobID, RunID,CreationTime, StartTime, EndTime).

Here's how the XSD file is being generated from SSIS:

<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="urn:com:ssn:schema:export:SSNExportFormat.xsd">
  <xsd:element name="SSNExportDocument">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element minOccurs="0" name="MeterData">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element minOccurs="0" name="RegisterData">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element minOccurs="0" name="RegisterRead">
                      <xsd:complexType>
                        <xsd:sequence>
                          <xsd:element minOccurs="0" name="Tier">
                            <xsd:complexType>
                              <xsd:sequence>
                                <xsd:element minOccurs="0" maxOccurs="unbounded" name="Register">
                                  <xsd:complexType>
                                    <xsd:attribute name="Number" type="xsd:unsignedShort" use="optional" />
                                    <xsd:attribute name="Summation" type="xsd:decimal" use="optional" />
                                    <xsd:attribute name="SummationUOM" type="xsd:string" use="optional" />
                                    <xsd:attribute name="CumulativeDemand" type="xsd:decimal" use="optional" />
                                    <xsd:attribute name="MaximumDemand" type="xsd:decimal" use="optional" />
                                    <xsd:attribute name="MaximumDemandTime" type="xsd:dateTime" use="optional" />
                                    <xsd:attribute name="DemandUOM" type="xsd:string" use="optional" />
                                  </xsd:complexType>
                                </xsd:element>
                              </xsd:sequence>
                              <xsd:attribute name="Number" type="xsd:unsignedByte" use="optional" />
                            </xsd:complexType>
                          </xsd:element>
                        </xsd:sequence>
                        <xsd:attribute name="ReadTime" type="xsd:dateTime" use="optional" />
                        <xsd:attribute name="GatewayCollectedTime" type="xsd:dateTime" use="optional" />
                        <xsd:attribute name="RegisterReadSource" type="xsd:string" use="optional" />
                        <xsd:attribute name="Season" type="xsd:unsignedByte" use="optional" />
                      </xsd:complexType>
                    </xsd:element>
                  </xsd:sequence>
                  <xsd:attribute name="StartTime" type="xsd:dateTime" use="optional" />
                  <xsd:attribute name="EndTime" type="xsd:dateTime" use="optional" />
                  <xsd:attribute name="NumberReads" type="xsd:unsignedByte" use="optional" />
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
            <xsd:attribute name="MeterName" type="xsd:unsignedInt" use="optional" />
            <xsd:attribute name="UtilDeviceID" type="xsd:string" use="optional" />
            <xsd:attribute name="MacID" type="xsd:string" use="optional" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="Version" type="xsd:decimal" use="optional" />
      <xsd:attribute name="DocumentID" type="xsd:string" use="optional" />
      <xsd:attribute name="ExportID" type="xsd:string" use="optional" />
      <xsd:attribute name="JobID" type="xsd:unsignedByte" use="optional" />
      <xsd:attribute name="RunID" type="xsd:unsignedByte" use="optional" />
      <xsd:attribute name="CreationTime" type="xsd:dateTime" use="optional" />
      <xsd:attribute name="StartTime" type="xsd:dateTime" use="optional" />
      <xsd:attribute name="EndTime" type="xsd:dateTime" use="optional" />
    </xsd:complexType>
  </xsd:element>
</xs:schema>

I'm new with SSIS and XML tasks and I would be grateful with any help! Tks...

Answer 1

I don't think you can achieve this using an XML Source component, you have to add a Script Component (as a Source), open the script component editor, add the output columns you need (DocumentID, ExportID, JobID, RunID,CreationTime, StartTime, EndTime).

Then open the script editor and write the following code (i used VB.NET):

You have to replace "C:\MyPath\xmlfile.xml" with the xml file path, also you have to add System.Xml.Linq dll as reference

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
Imports System.Xml.Linq

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute>
<CLSCompliant(False)>
Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()


        Dim strFilePath As String = "C:\MyPath\xmlfile.GetXmlNamespace"
        Dim xml As XDocument = XDocument.Load(strFilePath)


        Output0Buffer.AddRow()

        With Output0Buffer

            .DocumentID = If(xml.Root.Attribute("DocumentID") Is Nothing, "", xml.Root.Attribute("DocumentID").Value.ToString)
            .ExportID = If(xml.Root.Attribute("ExportID") Is Nothing, "", xml.Root.Attribute("ExportID").Value.ToString)
            .JobID = If(xml.Root.Attribute("JobID") Is Nothing, "", xml.Root.Attribute("JobID").Value.ToString)
            .RunID = If(xml.Root.Attribute("RunID") Is Nothing, "", xml.Root.Attribute("RunID").Value.ToString)
            .CreationTime = If(xml.Root.Attribute("CreationTime") Is Nothing, "", xml.Root.Attribute("CreationTime").Value.ToString)
            .StartTime = If(xml.Root.Attribute("StartTime") Is Nothing, "", xml.Root.Attribute("StartTime").Value.ToString)
            .EndTime = If(xml.Root.Attribute("EndTime") Is Nothing, "", xml.Root.Attribute("EndTime").Value.ToString)

        End With

    End Sub

End Class