SQL Server BI Step by Step SSIS 3 --- 批量导入Excel表

上一次我们已经介绍了简单的数据导出和导入,但是只是对单一文件进行操作,如果我们想同时对一个目录下面的所有的文件执行数据导入怎么实现呢?相当简单,SSIS在控制流中提供了Foreach循环容器,很容易理解,它和序列容器相比就是能够循环的遍历执行,能够对指定枚举器的每个成员重复执行控制流:

         可以看到,Foreach循环窗器遍历文件夹可以通过一些设置或者是正则来匹配,使用 Foreach 循环容器，可以枚举：
•        ADO 记录集行和架构信息
•        文件和目录结构
•        系统、包和用户变量
•        SQL 管理对象 (SMO)

      首先我们准备一下数据,把上次生成的Excel文件重命名为Product1.xls,新建一个Product2.xls,复制Product1.xls中的ProductID<800的行到新建的Product2.xls中(注意两个Excel的Sheet名字一致),然后在我们的SSIS项目中新建一个ForeachInput包,Foreach 循环容器容器进来,双击进行集合设置,在文件夹处指定我们Excel文件所在的文件路径,比如F:/ ,文件名处写*.xls,这样就能够匹配出所有的后缀名为xls的文件,然后单独执行容器里面的流程,不过在我们的容器中需要去访问每一个Excel文件,我们必须获得每一个枚举变量也就是文件的名称,可以通过设置变量映射实现.

      

       如上图,新建变量后,每次找到一个与条件相匹配的文件时，Foreach 循环容器就会将用该文件名填充用户定义的变量.同理,当我们遍历其它对象时,也可以采用变量的方式进行映射,获得遍历到的单个匹配对象.
       然后我们直接把上一次建的包(OutputAndInput.dtsx)中的数据导入这个数据流组件复制到Foreach 循环容器中(就象复制普通文件一样,呵呵),同样也把连接管理器中的Excel文件连接和数据库连接复制过来.这样我们基本上已经完成了,只不过现在的Excel文件连接还是指定文件,我们只需要和我们的变量currentFilePath绑定上就行了.点击我们的Excel文件连接,在右边的属性中点击Expressions.
      

      在属性表达式编辑器里,我们可以对Excel文件连接的所有的属性的值直接用表达式绑定,不仅是Excel文件连接,象普通文件连接,数据库连接,FTP连接同样可以.这无疑提高了灵活性.在这里,我们仅需要绑定其文件路径,在属性里选择ExcelFilePath,然后点击表达式后面的省略号按钮.
     

      这里不仅可以直接绑定系统变量和用户变量,而且可以使用脚本组合成表达式.我们只需要选定用户变量currentFilePath.点击计算表达式,只是一个空的字符串,因为此时并没有进行遍历.所以点击后发现Excel Source数据流源组件显示红色的,提示文件并不存在,我们不需要此时对文件进行检测,所以将Excel文件连接的延迟验证DelayValidation属性值设置成True.
      我们为了防止和数据库里面已经有的数据发生冲突,修改派生列组件,因为上一次我们是ProductNumber和Name加了1,这次我们加2.
     

     然后右击包执行,你会发现执行成功,而且是执行了两次数据流(有可能很快看不出来),可以通过对比数据库看出来,已经添加进了两个Excel表里的数据.当然,你可以分成更多个Excel,多少个都没有关系.
     

 

      此处,你也可以通过添加数据查看器进行查看执行过程中的数据,这样可以很方便的进行SSIS包的简单的调试.右击派生列组件和Product目标表中间的绿色,点击数据查看器,然后点击添加,再选择网络,其它全部默认,确定.
     

      我们把派生列中改成+"3”时再次运行包,你会发现所有的组件都是黄包,这代表正在执行,在弹出来的数据查看器中可以看到所有的数据流的55行数据,点击上面的绿色按钮可以继续运行.通过数据查看器我们可以查看正在数据流中传输的数据.

    

      我们在上面的数据查看器中就可以看到新增加的NewProductNumber和NewName两个列是否正确.
      到现在我们就已经完成了批量导入Excel,本次我们主要学习了Foreach循环容器,并且配合用户变量的使用,以及在使用连接器中的连接的一些简单设置,比如属性动态绑定表达式等,另外还介绍了数据查看器的用法.

     

     参考文章:http://blog.csdn.net/jinjazz/archive/2008/07/25/2710169.aspx
                  http://www.oracle.com.cn/redirect.php?tid=85304&goto=lastpost

     本次项目文件下载.(for Vs 2005)

 

作者：孤独侠客（似水流年）
出处：http://lonely7345.cnblogs.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。