β

SQLServer2012 IntegratiionServices高级教程

享受代码 883 阅读
比SQL强悍之处:
1,可以使用脚本处理任务,比如C#
2,数据源可以多源,如web service
3,FTP传送数据,生成文件并邮件、FTP
4,可以通过转换,将不标准的数据传到另一路径处理,标准数据按正常路径执行,通过unionall转换来合并数据
5,若是同一sqlserver中,用sql job完全可以使用,若是数据源来自不同服务器,有的是文本这些用SSIS效率会更高, 第8章有个典型例子
      windows services的地方都可用ssis来替换,windwos servies的脚本ssis中的脚本任务可完整实现 ,并且ssis放在agent job中执行,可以按多种执行计划来调度,还有错误报告机制,并不用再费心对每个windows services写错误机制,错误可写到表中,这样通过数据库连接就能看到错误信息
 6,通过数据转换与查找,利用提供的现有函数,简单完成复杂的数据抽取
7,可替换:windows services,账单上传,本质是涉及到多数据源的数据操作,就是SSIS的用武之地
8,可在asp.net调用包执行数据
9,当出错是,可进行配置,将错误信息自动记录到表中

第二章 

1,在sqlserver上通过向导,选择将包保存到文件,最后可生成.dtsx文件,打开sqlserver data tools2012,新建一个项目,可将前面生成的包copy到项目的包中,进行编辑
2,执行包:在解决方案管理器中右击该包,并选择执行包
3,控制流中执行进程任务可用来执行exe这些项目
4,数据流中有两个箭头,绿字的表示本次执行成功后执行哪一个,而红色的表示执行失败后执行哪一个
5,任务流的箭头上双击,可以指定按哪种方式转到下一个,若表达式,成功,失败等,其中完成约束表示无论前一个成功与否,都执行下一个。
6,选择多个,右键可以分组
7,批注,在空白处点右键,批注
第三章 SSIS任务
1,属性
  • DelayValidation:设置为True,则不会对任务中的属性集进行验证
  • Disable:禁用任务
  • FailPackageOnFailure:设置为True时,只要单个任务失败,整个包就会失败
  • FailParentOnFailure:设置为True时,单个任务失败,该任务的上层如包或容器也会失败
2,任务类型
  • 控制流中执行进程任务可用来执行exe这些项目
  • 脚本任务可以通过C#来编写代码,设置ReadOnlyVariables和WriteVariables属性,可将SSIS变量以逗号分隔的列表形式传送到脚本
  • 文件系统任务,复制移动目录等
  • FTP任务,获取或发送文件
  • Web服务任务:接收WebService数据
  • XML任务:可以用来验证XML任务是否正确
  • 大容量插入任务:将来自文本的数据插入到关系数据库中,类似于sqlserver中的导入文本文件
  • SQL任务:执行sql,sql脚本,存储过程
  • 发送消息任务:与ssb通信等
  • 发送邮件任务
  • WMI数据读取器任务:读取日志信息
  • WMI事件观察器任务:在OS某个指定事件触发后执行相关任务,如某目录下新增了文件后再执行
  • SMO任务
    • 传输job任务
    • 传输登陆名任务
    • 传输错误信息任务
    • 传输SQLServer对象任务

第四章 容器
1,容器:可以视为一个小型的包,将变量范围缩小到一个容器内,注意容器内的任务不能与容器外的任务相连接
2,For循环容器:遍历执行,直到满足某个条件跳出
3,Foreach循环容器,遍历一个对象集合,如文件,对象,通过定义变量来与索引匹配,在属性变量映射中设置【debug不成功】
4,Foreach dao容器,将Foreach的属性设置为ADO即可
第五章 数据流
1,数据流主要在内存中工作,提供了更快的速度,虽然也需要更好的内存,以空间换速度,数据从源中流出,默认大小为10M或1万行(以先达到为准)开始进行转换
2,数据查看器:在运行时查看数据,方法,右击一个路径,选择“启用数据查看器”,默认显示1万行
3,OLE DB源:可以在错误输出时,选择错误、截断时执行忽略还是继续执行
4,平面文件源:
  • 文本限定符,若指定按逗号分隔,以引号文本限定符包含中的逗号将能被忽略
  • 默认情况下,SSIS将扫描文件中的前100条记录,从而猜测合适的数据类型
  • FastParse选项,对数据进行类型检查,一般会增加20%~30%的时间来验证,若文件格式比较整齐的话,可以忽略,在右键-高级编辑器-输入属性和输出属性-平面文件源输出-输出列 ,右边FastParse
5,多平面文件连接管理器:在连接管理器中右键添加 

6,目标:目标与源的区别在于在目标中有一个映射属性页,来指定数据来源
7,OLE DB目标:
  • 每批行数,指定了在每次发送到目标的批处理有多少行
  • 最大提交大小,指写了在在发出提交语句之前批处理的大小,可设置为一个小的数字,以便快速提交
  • 表锁:在目标表中设置一个锁,以便提高加载速度,但若有多线程,此选项将会耗时
8,原始文件目标:通常作为部分转换数据的中间点,以本机格式编写,读取速度非常快。

9,SQLServer目标:它需要用内存中的一个接口,需要将包运行在sqlserver所在的服务器中才行
10,转换
  • 聚合转换,对源数据进行聚合
  • 有条件拆分转换:相当于加了一个if,else,比如当某字段首字母为“A”时,执行一个路径,其它执行另一路径
  • 数据转换:对字段进行convert,转化为指定类型
  • 派生列转换:对数据源增加新列,此新列可以为一计算值
  • 查找转换:在数据流和第二个数据集之间执行join
  • 行计数转换:记录转换的行数,需要定义一个变量存储行计数的结果
  • 排序转换:对指定数据进行排序,是一种异步转换,比较慢,若想排序的话,在TSQL语句中,高级编辑器-输入属性和输出属性中指定IsSorted为True
  • UNION转换:对多个数据源执行UNION,将源数据都指向此转换即可
  • 字符映射表转换:对列进行字符操作,如大小写,字节反转,简繁体转换
  • 复制列转换:对列进行复制,是派生列转换的简化版
  • 导出列转换:将数据库中BLOB类型的字段转化成物理文件,它要求输入有两个,一个是BLOB类型字段,一个是生成的对应文件
    • 允许追加,指定了如果文件存在,是否应将输出附加到现有文件
    • 强制截断,当文件存在时就重写
    • 编写字节顺序标记,是否将字节顺序记录文件
  • 导入列转换:将文件导入到数据库
  • 模糊查找转换:比如对于文本,将它的列与数据的列进行相似匹配,正确的话就替换文本的列,然后存入目标,没有查找转换效率高
  • 模糊分组转换:浏览一组相似的文本进行转换
  • 合并联接转换,对数据进行JOIN,生成结果表
  • 多播转换:将源数据下放到多个目标中,多播转换会向下游的每一个目标发送所有行,而有条件拆分是将满足条件的行输出到下一目标
  • 百分比抽样转换:按百分比抽取数据
  • 行抽样转换:指定行数抽取数据
  • 透视转换:进行行转列
    • 透视键:原来的一列数据作来新数据中的列名
    • 设置键:左边的行值
    • 透视值:据设置键与透视键进行聚合
  • 逆透视:列转行
  • 字词提取转换:提取一字符串字段,对于进行分析,生成各字词的频次表
  • 字词查换转换:类似于查找转换,指定一数据源进行匹配
第六章 使用变量、参数和表达式
1,变量区分大小写,按F4查看属性,其中将EvaluteAsExpression属性设置为True时,通过Expression属性提供的表达式来定义变量,相对于表达式,不过更好的一点是更通用的表达式。
2,2012中提供了参数,可以设置是否敏感,这样ssis可对包进行加密。
3,表达式与T-SQL有部分不一样:IsNull 若为null,则为true,而不是tsql中的二元。datepart要加引号,如:DATEPART( "mm",getdate())
4,字符串要有双引号,而不是单引号

5,在SSIS中变量不能被设置为NULL,都有默认值 ,如datetime的默认值为12/30/1899
第10章 加载数据仓库
1,事件查看器:对数据质量进行分析,将数据事件探查器任务放到控制流,属性快速配置文件中指定数据源,常规中指定目标,执行,就能生成xml分析文件,用sqlserver中Integration Services下的数据配置文件查看器就能查看此XML文件
     注此工具在2012才开始提供,在查看器中,树的右上角可按列显示数据
     使用说明:
  • 候选键配置文件,显示 哪些列是唯一的
  • 列Null比率,验证数据完整性
  • 列长度分布配置文件,查看最大最小长度
  • 函数依赖关系配置文件:看列与列是否1对1的关系
第22章 SSIS部署
1,在sqlserver上有个Integration Services目录,要建立一个SSISDB,在ssis设计时,指定此部署包即可,部署上后,通过job指定要运行的包,设定好时间就可运行
  • SSISDB是一个数据库,在数据库里面能找到此数据库,可看到他的表
  • 在Integration Services目录,项目里右键属性可看到版本,历史版本都有记录,可以还原,在SSISDB属性上有个选项可设置保留的最大版本数
  • 部署有两种,项目部署模型,2012新的默认的,包部署模型,历史兼容
  • 部署到sqlserver上的包可以通过tsql来执行,在包上右键执行,生成脚本,就能得到
  • 环境是参数的集合,单独抽取出来方便调用,引用环境就是在包上右键-配置-引用即可
  • 报表已经内部生成好,在包上右键-报表,可看到包的运行情况,详细的运行到每一步的情况及报表
  • 若报表不足够,可在这里下载或者自己开发http://ssisreportingpack.codeplex.com/,下载后用ssrs打开修改数据源,在自定义报表中指定即可
作者:享受代码
关注数据库、算法、程序设计的IT技术博客
原文地址:SQLServer2012 IntegratiionServices高级教程, 感谢原作者分享。

发表评论