SSIS 批量导入远程多级别的XML数据源。

看看我们SSIS的流程图

大概的流程是这样的：

先用执行SQL 任务，返回XML数据源的ResultSet， 再用Foreach容器，遍历每个XML数据源，在数据流任务里实行,合并连接和排序等处理，最终插入到database。

详细步骤

首先用储存过程生成远XML数据源的链接， 例：

http://www.abc.com/xml.php?p_checkin=18-Aug-12&p_checkout=18-Sep-12&p_hotel=BJJD

http://www.abc.com/xml.php?p_checkin=18-Aug-12&p_checkout=18-Sep-12&p_hotel=BDJD

http://www.abc.com/xml.php?p_checkin=18-Aug-12&p_checkout=18-Sep-12&p_hotel=SZXSW
....

例如看下图的 Hotel_Rate

用浏览器打开，这些XML的返回结果一般是这样的, 多级的。

<?xml version="1.0" encoding="UTF-8"?>
<QRATE>
  <XML_RESULT>
    <CONTRACTS>
      <CONTRACT>HC10000068</CONTRACT>
      <VER>26</VER>
      <HOTEL>BJJD</HOTEL>
      <HOTELNAME>北京酒店</HOTELNAME>
      <CUR>RMB</CUR>
      <PRODUCT>
        <PROD>15</PROD>
        <NATION>ALL</NATION>
        <NATIONNAME>全部国籍</NATIONNAME>
        <MIN>1</MIN>
        <MAX>99</MAX>
        <ADVANCE>0</ADVANCE>
        <TICKET>0</TICKET>
        <CANCEL>1</CANCEL>
        <CUTOFF>4</CUTOFF>
        <ROOM>
          <CAT>D3</CAT>
          <TYPE>D</TYPE>
          <SERV>10</SERV>
          <BF>2</BF>
          <DEADLINE>18-08-12</DEADLINE>
          <STAY>
            <STAYDATE>18-08-12</STAYDATE>
            <PRICE>650</PRICE>
            <ALLOT>5</ALLOT>
            <IS_ALLOT>N</IS_ALLOT>
          </STAY>
        </ROOM>
        <ROOM>
          <CAT>D3</CAT>
          <TYPE>S</TYPE>
          <SERV>10</SERV>
          <BF>1</BF>
          <DEADLINE>18-08-12</DEADLINE>
          <STAY>
            <STAYDATE>18-08-12</STAYDATE>
            <PRICE>650</PRICE>
            <ALLOT>5</ALLOT>
            <IS_ALLOT>N</IS_ALLOT>
          </STAY>
        </ROOM>
        <ROOM>
          <CAT>D3</CAT>
          <TYPE>T</TYPE>
          <SERV>10</SERV>
          <BF>2</BF>
          <DEADLINE>18-08-12</DEADLINE>
          <STAY>
            <STAYDATE>18-08-12</STAYDATE>
            <PRICE>650</PRICE>
            <ALLOT>5</ALLOT>
            <IS_ALLOT>N</IS_ALLOT>
          </STAY>
        </ROOM>
      </PRODUCT>
    </CONTRACTS>
    <RETURN_CODE>0</RETURN_CODE>
    <ERROR_MESSAGE/>
  </XML_RESULT>
</QRATE>

Sql 任务的属性
(SQL 连接这里就不详细解释了)

SQLStatement 输入XML数据源的表。

ResultSet 设为完整结果集

结果集那里新建的object类型 user::table

Foreach循环容器
集合的Enumerator 选择为 Foreach ADO 枚举器
ADO 对象源变量 选择之前定义的 user::table变量

 
变量映射
新建二个变量contract和XML都为String类型的并定义它们的索引。

回过头，再看看我们所定义变量

好了，主角来了, 下图，是XML数据源的合并, 排序处理流程，最终写入到sql server数据库

要做到一步，我们设置一下XML源的高级属性。
在输入属性和输出属性, XML_Result是根元素, 我们需要的子元素是 Contract, Product, Room, Stay 实现对应每个父元素和子元素
要把Contract, Product, Room, Stay的IsSorted属性要设为True。

然后，我们找到 每子元素的对应ID并设置它们的SortKeyPosition的值。
Contract - Contract_ID = 1

Product - Contract_ID = 1
Product - Product_ID = 2

Room - Product_ID = 1
Room - Room_ID = 2

Stay - Room_ID = 1

数据访问模式, 选择来自变量的XML文件, 选择之有定义好的user::xml, XSD位置 选择生成好的xsd文件。
可以在 数据访问模式 选择XML文件位置 输入某个XML源文件来生成xsd。
例：http://www.abc.com/xml.php?p_checkin=18-Aug-12&p_checkout=18-Sep-12&p_hotel=BJJD
在列那里，把相应的列表对应好。

我们先合并Contract和Product元素, 联接类型 以Contract为准，我这里设定是左外部联接。
在这里个图，我们可以看到 联接键是 contract_id, 这是我们之前设置XML源的IsSorted和contract_id的SortKeyPosition为1 到来的结果。
在contract里边还有一个product_id 钩选为输出列，为下一步与product元素合并使用。

在进行Product和Room合并之前，我们要把Product_id重新排序为1。就是这样以此类推设置room_id。

大概的流程是：
把contract和product合并, 排序product_id,  
再把contract和product结合体和room合并, 排序room_id,
最后把 contract, product, room的结合体与stay合并并入写数据库。

排序product_id


排序room_id

大工告成，开始执行

导入ing

再看看我们的写入的表, OK, done.

注：请把全局和数据流任务的MaximumErrorCount调大一点，这样发生错误之后，会继续执行任务

如果您觉我得写的不错或者对您有帮助, 请推荐一下吧。