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

posted @ 2017-08-15 09:39  悦光阴  阅读(4725)  评论(2编辑  收藏  举报