最近接手一个项目,程序的基本框架是:程序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下的子目录。
如果指定的目录下,没有存储任何文件,那么遍历循环容器(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},两列之间以制表符分割;
默认情况下,平面文件连接管理器把字符串字段的长度设置为50个字符,但是,当列的分割不是以字符数量,而是以分割符(Tab,制表符)来界定时,你需要根据文档中字段的实际长度来预估和设置各个数据列的长度,以避免发生数据截断,或者超出字段设置的宽度而引发的异常。
2,创建平面文件源
打开平面文件源组件,在
Connection Manager
选项卡中指定已创建的平面文件连接管理器,在
Columns
选项卡中查看所有可用的外部数据列,在
Error Output
选项卡中,当外部列转换成输出列出现异常时,指定组件对对转换错误的处理模式。
通常情况下,勾选"
Retain null values from the source as null values in the data flow
",保持数据源的null值。
文件系统任务,主要属性是: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.
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
最近接手一个项目,程序的基本框架是:程序A导出数据,以.tsv格式存储,数据列之间以tab间隔;程序B吃文档,把数据导入到数据库中,并把处理过的文档备份/移动到指定的目录中。为了快速开发,程序B设计成SSIS的Package,本文主要分享Package的设计思路,组件用法和注意事项。在设计Package时,使用Foreach Loop Container组件遍历Folder中存储的.tsv文档,...
这些包可以独立使用,也可以与其他包一起使用以满足复杂的业务需求。Integration Services 可以提取和转换来自多种源(如 XML 数据文件、平面文件和关系数据源)的数据,然后将这些数据加载到一个或多个目标。(摘自MSDN,更多详细信息可参考:http://technet.microsoft.com/zh-cn/library/ms141026(v=sql.105).aspx) 下面我使用SSIS来演示一个实际例子。比如我有一个数据库,出于备份数据或者其它的目的,会定期的对这个数据库的数据迁移到其它的数据库去。迁移的时候,有些新增的字段会被插入备份数据库,而有些被修改过的字段也会在
1、“01 参考资料列表”文件夹——该文件夹包含一个名为“参考资料列表.doc”的文档,文档列出了本次考核所涉及到的五部分内容(SQL管理及T-SQL、SSIS及SSAS、网络及硬件、Oracle及DB2,其他)的知识点和考查点,以及为了学习这些知识需要阅读的参考资料清单。
2、“02 参考资料内容”文件夹——包含了“参考资料列表.doc”中描述的参考资料文档。包括:“01 sql_ox.pdf”、“02 SQL21天自学通.pdf”、“03 SSIS&SSAS 参考资料.doc”、“04 oracle&DB2 参考资料.doc”、“05 网络及硬件 参考资料.doc”
3、“03 入门考试题&答案”文件夹——包括入门考试的考题和答案。此目录下共包含3个文件:
a、“01 代理培训考试题.doc”文档,包括本次考试的习题和标准答案(不包括BI上机题的答案和简答题windows防火墙配置的答案)
b、“NorthWind.bak”,一个数据库备份文件,用户在进行BI部分的上机测试时,需要利用此数据库备份文件进行数据库的恢复操作。
c、“03 BI考试题答案.rar”压缩文件,是关于BI上机测试部分的标准答案。解压缩后包括四个文件夹:
“01 SSIS” 文件夹,为数据加工用例。
“02 SSAS” 文件夹,为多维数据用例。
“03 数据库备份”文件夹,是构建好的SSIS和SSAS 数据库备份文件。
“04 OLAP展现”文件夹,包含“OLAP图.xls”Excel文件,是多维数据在Excel中的展现效果。
根据业务部门提供的数据,连接业务数据库查询数据,并根据业务数据库处理的数据写入xls表。
1、打开 Microsoft Visual Studio 2008,文件-新建项目-新建一个“Integration Services 项目”,重命名为:SSIS_xls。
2、重命名SSIS包为:GetCusFullnumber.dtsx,点击并切换到控制流,并从工具箱中脱出如下组合工具
Add variables :
varFileFullName expression: @[User::varFilePath]+ @[User::varFileName]
varFileName default vaule : [file name]
varFilePath...