数据流任务，XML 源错误

Question

我正在使用 VS 和 SQL Server 2008 开发 SSIS 应用程序。但是，我对 SSIS 仍然很陌生。

我已成功创建一个项目，其中的 XML 任务连接到数据流任务。但作为同一解决方案的一部分，我创建了一个相同的项目，其中 XML 任务连接到数据流任务。

据我所知，这两个项目之间的唯一区别是文件。尽管第一个包工作顺利，但第二个包失败并出现以下错误：

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.

但是，我检查了这些文件，但看不出问题出在哪里。我该如何调试这个？输入 XML 文件如下所示：

<?xml version="1.0" encoding="utf-8"?>
<rows xmlns="http://www.w3.org/1999/xhtml">
  <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>

我的 XSD 文件如下所示：

<?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">
  <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>

此外，当我忽略此输出错误时，包执行开始，但随后因不同的错误而失败：

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

Answer 1

在您的 XML 架构中，您已定义属性 WITHDRAWAL_DATE 的类型为 xs:date。这意味着 WITHDRAWAL_DATE 的内容必须采用 YYYY-MM-DDThh:mm:ss 格式。您的 XML 代码具有格式为 YYYY-DD-MM 的 WITHDRAWAL_DATE 属性值。因此，根据您的架构，这些值无效，并且使用此类值创建 XML 文档将创建无效的 XML 文档。

XML 模式没有格式为 YYYY-DD-MM 的内置数据类型，因此您需要更改在 XML 文档中使用的格式，或者自己定义此属性的有效类型你的架构。