SSIS 遍历目录，把文件内容导入数据库

最近接手一个项目，程序的基本框架是：程序A导出数据，以.tsv格式存储，数据列之间以tab间隔；程序B吃文档，把数据导入到数据库中，并把处理过的文档备份/移动到指定的目录中。为了快速开发，程序B设计成SSIS的Package，本文主要分享Package的设计思路，组件用法和注意事项。在设计Package时，使用Foreach Loop Container组件遍历Folder中存储的.tsv文档，通过平面文件源读取遍历的tsv文件，通过Expression Task组件获得该文件的备份路径，通过File System Task把处理过的文件移动到指定的目录中，该Package主要用到四个组件，分别是，Flat Files Source，Expression Task和 File System Task，Package的设计核心简化为下图所示：

一，遍历目录

导入文档内容的第一步是遍历目录，读取文件的路径，这一步可以通过Foeach Loop Container组件实现。打开Collection选项卡，如下图，从枚举器（Enumerator）列表中选择“Foreach File Enumerator”，枚举器的配置界面如下图：

• Folder：指定遍历的目录
• Files：指定文件名模式，示例是 *.tsv，代表文件名以.tsv结尾的所有文件，其中通配符*代表任意字符；
• Retrieve file name：获取文件名的模式，默认选项是完全限定名（Full qualified），格式是：虚拟盘符+目录+文件名+文件扩展名；
• Traverse Subfolders：如果勾选遍历子文件夹，那么文件遍历器将会遍历Folder下的子目录。

枚举器的配置属性，都可以通过Expressions属性来配置，属性表达式的配置界面如下图：

 

在变量映射（Variable Mappings）选项卡中，选择变量User::DataFilePath，用于临时存储每次遍历获取的文件名：

 

如果指定的目录下，没有存储任何文件，那么遍历循环容器（Foreach Loop Container）直接返回，不会指定其容纳的任何Task。

二，平面文件源

平面文件源（Flat File Source）用于从指定的路径中读取文件，并按照指定的界定符读取数据，把具有特定格式的平面文件转换成关系表，平面文件的格式界定符分为：列分隔符和行分隔符，通常，tsv文件的列分隔符是tab，列分隔符是回车+换行。本来的目的，平面文件是动态变化的，不能指定文件的路径，在设计时，却必须初始化设计平面文件连接管理器的元数据，因此，首先，必须写Hard Code，在连接管理器中指定一个文件的路径；然后设置表达式属性，通过变量来设置平面文件连接管理器的外部数据源，也就是，源平面文件。SSIS组件有一个共性，那就是，在表达式（Expressions）中指定的属性值，会覆盖相同属性直接指定的值。通过硬编码方式指定源文件，仅仅是为了提供源平面文件的格式元数据，不会影响Package的执行。

1，创建平面文件连接管理器

在File Name中指定平面文件的路径，选择平面文件的Locale，默认是English，勾选Unicode，表示平面文件以Unicode编码存储。

在General选项卡中，配置常规属性：

• 平面文件的格式（Format）是：Delimited，以界定符分割。
• Text qualifier：文本界定符，用于指定文本以什么符号分割，默认值是<none>，表示文本没有界定符；
• Header row delimiter：首行界定符，默认值是回车+换行；
• Header rows to skip：文本的第一行，需要从文档开头跳过多少行，默认值是0，也就是说，文档的第一行是文本的第一行；
• Column names in the first data row：勾选，表面数据的第一行是列名；

在Columns选项卡中，配置列属性：

• Row delimiter：配置行分隔符，通常选择{CR}{LF}，两行之间以回车+换行分割；
• Column delimiter：配置列分隔符，通常选择Tab {t}，两列之间以制表符分割；

在Advanced选项卡中，配置外部数据列的属性：

• Name：配置列名
• ColumnDelimiter：列分隔符
• DataType：列值的数据类型，如果勾选Unicode，那么输出列数据类型的默认值是Unicode string [DT_WSTR]，否则，默认值是string [DT_STR]；
• OutputColumnWidth：输出列的长度，默认值是50;
• TextQualified：文本界定，指定是否文件数据被文本界定符（例如，引号）围绕，默认值是True；

 

默认情况下，平面文件连接管理器把字符串字段的长度设置为50个字符，但是，当列的分割不是以字符数量，而是以分割符（Tab，制表符）来界定时，你需要根据文档中字段的实际长度来预估和设置各个数据列的长度，以避免发生数据截断，或者超出字段设置的宽度而引发的异常。

2，创建平面文件源

 打开平面文件源组件，在Connection Manager选项卡中指定已创建的平面文件连接管理器，在Columns选项卡中查看所有可用的外部数据列，在Error Output选项卡中，当外部列转换成输出列出现异常时，指定组件对对转换错误的处理模式。

通常情况下，勾选"Retain null values from the source as null values in the data flow"，保持数据源的null值。

3，使用平面文件连接管理器的Expressions属性

在连接管理器（Connection Managers）窗体中，选中平面文件连接管理器，打开属性（Properties），配置表达式属性。

选中Expressions属性，点击后面的“...”，弹出属性表达式编辑器，

在属性表达式编辑器中，有一个属性表达式表格，有Property列和Expression列，Property列是一个列表，Expression列是表达式生成器（Builder），为了逐个读取指定目录下的文件，从Property列表中，选择连接字符串（ConnectionString）属性，并在Expression列中选中变量@[User::DataFilePath]，这样，每次遍历，平面文件连接管理器都会连接到变量指定的文件，读取数据，按照已设置的元数据向下游组件输出。

三，表达式任务组件

指定表达式，根据当前文件的路径，修改为备份的目的目录，注意，应该指定移动到的目录（Destination Directory），而不是文件的完全限定名：

四，文件系统任务

文件系统任务，用于对文件进行操作，其配置界面如下图，操作类型由属性Operation指定，本例是为了把文本备份到指定的目录中，选中操作类型为：Move file

文件系统任务，主要属性是：Source Connection，Destination Connection 和 Operation，分别是：

• Source Connection：源连接，用于操作的源文件路径；设置属性IsSourcePathVariable为True，可以通过变量指定源文件路径；
• Operation：操作类型，共有：
  • 复制目录(Copy directory)，
  • 复制文件(Copy file)，
  • 创建目录(Create directory)，
  • 删除目录(Delete directory)，
  • 删除目录内容(Delete directory content)，
  • 删除文件(Delete file)，
  • 移动文件(Move file)，在移动文件时，目标目录必须存在。
  • 重命名文件(Rename file)，
  • 设置属性(Set properties)
• Destination Connection：目标连接，用于指定操作的目标目录的路径；设置属性IsDestinationPathVariable为True，可以通过变量指定目标目录；属性OverwriteDestination用于指定，当目标文件已存在时，是否重写目标文件。

注意，在移动文件（Move File）时，需要指定目标目录（Destination Directory），SSIS对目标连接的定义如下：

如果指定的是文件的完全限定名，或目标目录不存在，文件系统任务会报错：

An error occurred with the following error message:"Could not find a part of the path."

五，重要属性

在本例中，有一个非常重要的属性必须配置，那就是延迟验证（DelayValidation）属性，该属性表示可执行组件的验证延迟到运行时，为什么要延迟到运行时呢？这是因为在设计Package时使用动态赋值的特性，有一些变量的值，或者表达式的值，只有在Package运行时，才会真正赋值。基本上每个Task组件都有延迟验证（DelayValidation）属性，其默认值是False，双击切换为True，启用组件的延迟验证。

1，启用文件系统任务

在配置完成之后，文件系统任务会抛出错误提示，错误消息是：Variable "DataFilePath" is used as a source or destination and is empty.

这是因为变量未被赋值，而文件系统任务组件探测（Detect）到引用的文件不存在，所以抛出异常消息，为了避免出现运行时错误，开启延迟验证，把可执行组件的验证推迟到执行时，需要设置文件系统任务的属性，把延迟验证（DelayValidation）属性设置为True。

2，启用Container和Data Flow Task的延迟验证

六，WMI 事件监控任务

WMI 事件监控任务（WMI Event Watcher Task）是一个非常神秘的任务组件，创建WMI Connection，输入WQL命令:

在WqlQuerySource中输入WQL命令，该命令表示：监控指定的目录，当有新文件插入后，该组件执行成功，否则，一直轮询。

SELECT * 
FROM __InstanceCreationEvent WITHIN 10 
WHERE TargetInstance ISA "CIM_DirectoryContainsFile" 
and TargetInstance.GroupComponent= "Win32_Directory.Name=\"D:\\\\DataFromCosmos\\\\MSCommunity\\\\MSCommunity_Posts\""

本例没有用到该组件，仅仅为了多了解一些组件的基本用法。

 

参考文档：

Flat File Connection Manager

图解SSIS监视文件夹并自动导入数据

Using the WMI Event Watcher Task in SSIS to Process Data Files

WMI Event Watcher Task