Data Flow Task, XML Source error

Question

I am developing an SSIS application with VS and SQL Server 2008. I am still new to SSIS, however.

I have successfully created one project with an XML Task connected to a Data Flow Task. But as part of this same solution I created an identical project with XML Task connected to Data Flow Task.

From what I can see, the only difference between the two projects is the files. Although the first package worked smoothly, this second one fails with the following error:

Error: 0xC02090FB at Data Flow Task, XML Source [1]: The "component "XML Source" (1)" failed because error code 0x80131537 occurred, and the error row disposition on "output column "WITHDRAWAL_DATE" (1689)" at "output "row" (28)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC02092AF at Data Flow Task, XML Source [1]: The component "XML Source" (1) was unable to process the XML data. Pipeline component has returned HRESULT error code 0xC02090FB from a method call.

However, I examined these files and cannot see where the problem lies. How can I debug this? The input XML file looks like:

<?xml version="1.0" encoding="utf-8"?>
<rows xmlns="http://www.w3.org/1999/xhtml"&gt;
  <row OTHER_ID="100041" GRAD_YR="2011" LAST_NAME="VALENTO" FIRST_NAME="SARA" MIDDLE_NAME="JEAN" BIRTHDATE="1993-01-30" DISTRICT_CODE="" STUDENT_STATUS="A" STUDENT_ID="10172" ALPHAKEY="VALENSAR000" SCHOOL_ID="010" ADVISOR="0" EW_DATE="2007-07-03" ENTRYC_CODE="00" WITHDRAWAL_DATE="2007-08-31" WITHDRAWAL_CODE="99" WITHDRAWAL_SCHOOL_YEAR="2008" WITHDRAWAL_GRAD_YR="2011" X_WITHDRAWAL_RETAINED="false" CY_TEAM_SCHD_ID="" GRADUATED="false" MN_EDE_NBR="624000665509">
  </row>
  <row OTHER_ID="100041" GRAD_YR="2011" LAST_NAME="VALENTO" FIRST_NAME="SARA" MIDDLE_NAME="JEAN" BIRTHDATE="1993-01-30" DISTRICT_CODE="" STUDENT_STATUS="A" STUDENT_ID="10172" ALPHAKEY="VALENSAR000" SCHOOL_ID="010" ADVISOR="0" EW_DATE="2008-07-08" ENTRYC_CODE="00" WITHDRAWAL_DATE="2008-07-24" WITHDRAWAL_CODE="99" WITHDRAWAL_SCHOOL_YEAR="2009" WITHDRAWAL_GRAD_YR="2011" X_WITHDRAWAL_RETAINED="false" CY_TEAM_SCHD_ID="" GRADUATED="false" MN_EDE_NBR="624000665509">
  </row>
  <row OTHER_ID="100041" GRAD_YR="2011" LAST_NAME="VALENTO" FIRST_NAME="SARA" MIDDLE_NAME="JEAN" BIRTHDATE="1993-01-30" DISTRICT_CODE="" STUDENT_STATUS="A" STUDENT_ID="10172" ALPHAKEY="VALENSAR000" SCHOOL_ID="010" ADVISOR="0" EW_DATE="2009-07-07" ENTRYC_CODE="24" WITHDRAWAL_DATE="2009-08-06" WITHDRAWAL_CODE="99" WITHDRAWAL_SCHOOL_YEAR="2010" WITHDRAWAL_GRAD_YR="2011" X_WITHDRAWAL_RETAINED="false" CY_TEAM_SCHD_ID="" GRADUATED="false" MN_EDE_NBR="624000665509">
  </row>

And my XSD file looks like:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" 
targetNamespace="http://www.w3.org/1999/xhtml" xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
  <xs:element name="rows">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="row">
          <xs:complexType>
            <xs:simpleContent>
              <xs:extension base="xs:string">
                <xs:attribute name="OTHER_ID" type="xs:string" use="optional" />
                <xs:attribute name="GRAD_YR" type="xs:decimal" use="optional" />
                <xs:attribute name="LAST_NAME" type="xs:string" use="optional" />
                <xs:attribute name="FIRST_NAME" type="xs:string" use="optional" />
                <xs:attribute name="MIDDLE_NAME" type="xs:string" use="optional" />
                <xs:attribute name="BIRTHDATE" type="xs:date" use="optional" />
                <xs:attribute name="DISTRICT_CODE" type="xs:string" use="optional" />
                <xs:attribute name="STUDENT_STATUS" type="xs:string" use="optional" />

                <xs:attribute name="STUDENT_ID" type="xs:integer" use="optional" />
                <xs:attribute name="ALPHAKEY" type="xs:string" use="optional" />
                <xs:attribute name="SCHOOL_ID" type="xs:string" use="optional" />
                <xs:attribute name="ADVISOR" type="xs:integer" use="optional" />
                <xs:attribute name="EW_DATE" type="xs:date" use="optional" />
                <xs:attribute name="ENTRYC_CODE" type="xs:string" use="optional" />
                <xs:attribute name="WITHDRAWAL_DATE" type="xs:dateTime" use="optional" />
                <xs:attribute name="WITHDRAWAL_CODE" type="xs:string" use="optional" />
                <xs:attribute name="WITHDRAWAL_SCHOOL_YEAR" type="xs:integer" use="optional" />
                <xs:attribute name="WITHDRAWAL_GRAD_YR" type="xs:integer" use="optional" />
<xs:attribute name="X_WITHDRAWAL_RETAINED" type="xs:boolean" use="optional" />
                <xs:attribute name="CY_TEAM_SCHD_ID" type="xs:string" use="optional" />
                <xs:attribute name="GRADUATED" type="xs:boolean" use="optional" />
                <xs:attribute name="MN_EDE_NBR" type="xs:decimal" use="optional" />

              </xs:extension>
            </xs:simpleContent>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Furthermore, when I ignore this output error, package execution begins but then it fails on a different error:

conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

Answer 1

In your XML Schema you have defined that the type of the attribute WITHDRAWAL_DATE is xs:date. This means that the contents of WITHDRAWAL_DATE must be in format YYYY-MM-DDThh:mm:ss. Your XML code has WITHDRAWAL_DATE attribute values that are in format YYYY-DD-MM. Therefore these values are not valid according to your schema and creating a XML document with such values would create an invalid XML document.

XML Schema does not have a built-in datatype that has the format YYYY-DD-MM so you either need to change the format you use in your XML documents or define yourself the valid type for this attribute in your schema.