SSIS学习(1-8).doc
《SSIS学习(1-8).doc》由会员分享,可在线阅读,更多相关《SSIS学习(1-8).doc(64页珍藏版)》请在咨信网上搜索。
1、 SSIS 学习(1) 概要俗话说:“十年磨一剑”,Microsoft通过5年时间的精心打造,于2005年浓重推出Sql Server 2005,这是自SQL Server 2000以后的又一旷世之作。这套企业级的数据库解决方案,主要包含了以下几个方面:数据库引擎服务、数据挖掘、Analysis Services、Integration Services、Reporting Services 这几个方面,其中Integration Services (即SSIS),就是他们之间的中转站、纽带,将各种源头的数据,经ETL到数据仓库,建立多维数据集,然后进行分析、挖掘并将结果通过Reporting
2、 Services 送达给企业各级用户,为企业的规划决策、监督执行保驾护航。SSIS其全称是Sql Server Integration Services,是MicrosoftBI解决方案的一大利器,是Sql Server 2000中DTS一个升级之作。无论是功能上,性能上,还是可操作方面都有很大的改进。且看下面的操作界面就可见一斑。SQL Server 2000 DTSSql Server 2008 SSIS现在很多人都把SSIS说成是一个ETL (Extract-Transform-Load)工具,我个人觉得不太准确,或许是大家基本上都把他做为ETL使用,其实SSIS已经超越了ETL的功能
3、,ETL仅是其中之一,它在其它方面也有非常突出的表现:(1)系统维护:a)在数据库维护方面: i.数据库备份; ii.统计信息更新; iii.数据库完整性检查; iv.索引重建 v.SSIS包执行; vi.SSAS任务处理。b)业务处理: i.执行SQL任务。 ii.Web Service任务。c)操作系统维护: i.WMI事件观察器任务 ii.文件系统任务。d)其它: i.执行SQL任务 ii.执行进程任务 iii.ActiveX脚本任务 iv.脚本任务(VB/C#). v.执行Web Service服务尤其是上面的第四点,可以执行SQL任务,可以执行Web Service服务,可以执行系统
4、进程,可以执行(VB/C#)脚本任务,这给了我们多大想象的空间,还有什么例外的?强啊。不得不佩服务一下。 SSIS 的体系结构主要由四部分组成:Integration Services服务、Integration Services对象模型、Integration Services运行时和运行时可执行文件以及封装数据流引擎和数据流组件的数据流任务(如图): 这是我们初学者必须要了解的,只要明白了这个体系统结构,体会了各组成部分之间的关系,清楚了什么是控制流、什么是数据流,SSIS学起来就不难了。总之,SSIS并不简单的是DTS的一个升级版,除了上面所说的几个方面的改进外,在开发环境方面,Micr
5、osoft还一如继往地发挥着他的优势,与Visual Studio紧密集成,让开发人员可以在一个更加熟悉,更加方便的平台上设计、开发,大大降低了入门的门槛,加速了学习、开发的进度。它的组成元素也更加对象化,每一个包、每一个任务、每个一控制流、每一个数据流,都是一个独立的对象,有其对应的属性、对应的事件。VB/C#的脚本任务;变量、属性的参数化,更是让人震撼,几乎是无所不能,无所不可似的(有些夸张了,我不是托,只是感觉比以前强大太多了)。使用起来也并不复杂,只要你安装了SQL Server Integration Services 10.0服务(SQL 2005 应该是Integration S
6、ervices 9.0),New project,选择Integration Services项目,就可以一睹芳容,亲密感受他的博大 SSIS 学习(2):数据流任务(上)数据流任务是SSIS中的一个核心任务,估计大多数ETL包中,都离不开数据流任务。所以我们也从数据流任务学起。数据流任务包括三种不同类型的数据流组件:源、转换、目标。其中:源:它是指一组数据存储体,包括关系数据库的表、视图;文件(平面文件、Excel 文件、Xml 文件等);系统内存中的数据集等。转换:这是数据流任务的核心组件,如果说数据流任务是ETL的核心,那么数据流任务中的转换,则是ETL核心中的核心了。它包含非常丰富的数
7、据转换组件,比如数据更新、聚合、合并、分发、排序、查找等。可以说SQL语句中有的功能,它都基本上运用起来了。目标:与“源”相对应,也是一组数据存储体。包含表、视图;文件;多维数据集、内存记录集等。除以上三类组件外,还有一种组件,那就是”流(Flow)“,它形象地显示了数据从”源“,经过”转换“,最后到达”目的“地的一组路径。我们可以利用”流“,来查看数据,添加备注说明等。下面一幅图,就充分展示了源、转换、目的、流的关系。下面我们以将IIS Log 导入数据库为例,来介绍如何进行数据流任务开发。在开发之前,我们先来看看IISlog 的结构,如图:它基本上记录了网页浏览的所有信息,如日期、时间、客
8、户IP、服务器IP、页面地址、页面参数等很多信息,我们再根据这些信息,在关系型数据库中,建立一张对应表,来记录这些信息。代码万事俱备,下面我们就可以开始ETL的开发之旅了,打开Visual Studio 2008工具,文件-新建-项目,选择“Integration Services 项目”,ETL的开发界面就跃入眼帘,这是从事.Net 开发的朋友们非常熟悉的界面。打开左边“工具箱”,将“数据流任务”拖到主窗口“控制流面板”,如图所示:然后双击“控制流”面板上的“数据流任务”,进入“数据流”面板,这两部分UI没有什么差异,只是所实现的功能不同罢了。真正的数据流任务开发,从现在才算开始。打开左边“
9、工具箱”,可以看到有三大部分:数据流源、数据流转换、数据流目标。我们从“数据流源”中,将“平面文件源”拖到主窗口下,双击打开“平面文件源”编辑器,点击“新建”,打开平面文件连接管理编辑器,如图:输入连接名称,选择IisLog 文件,选择行分隔符、列分隔符,就可以从预览窗口看到数据的真面目了。这里有一点要注意,不同的平面文件,其行分隔符、列分隔符都是不一样的,如果选不正确,将达不到你想要的效果,所有的数据都可能挤到一列中去了。一般行分隔比较简单,基本上都是以回车换行(CRLF)来分隔;列分隔符却不一样了,它既可以以任意文本字符来分隔,比如逗号(,)、分号(;)、冒号(:)tab符、竖线(|),以
10、及常用的文字字符、数字字符,也可以定义每一列的固定宽度来分隔。这就需要视文件源不一样,分别对待了。在平面文件连接管理器中,选择“高级”,还可以定义每一列的列名、数据类型、字符长度等信息。等一切定义完成,点击确定,返回到平面文件编辑器界面,前面建立的连接将自动返回到“平面文件连接管理器”的下拉列表框中,下面就要以选择需要输出的列了,如图: 然后再选择“错误输出”,缺省选项如下图所示: 这一选项非常重要,是要求我们配置当源数据发生错误的时候该如何处理,一般源数据发生错误有两种情况:一是数据类型错误,比如日期格式错误、数字变字符了等;另一情况就是字符太长,超出列宽了。根据不同的情况,其处理方式也不一
11、样,系统提供了三种解决办法:忽略失败:是指如果某一行数据错误,忽略此行,不影响程序执行,继续导入其它数据。重定向行:将错误的数据行,导入到另外一个数据流目标,供以后人工检查后,再重新处理。组件失败:这是最严格的,只要遇到数据错误,组件立即失败,停止运行。就IISLOg 这样的数据源文件来说,有错误数据行,那是是经常发生,但是这些少量数据错误,也不会影响最终的结果,我们就要以考虑容错性为主了,放宽对数据质量的要求,一般选择“忽略错误”,以方便程序继续运行。 一切都定义完后,我们看到“平面文件源”控件上,还有一个红色的叉(X),那是指没有为此数据源定义目标,那就是下一步要定义的。另外下面还有两个长
12、线箭头,一个绿色,一外红色,其中绿色:表示正确数据流通路,红色表示错误数据流通路,如果前面定义错误“重定向行”,那么错误数据将沿着红色路径,流向错误数据存放地。定义数据源目标,这可能要简单一些了,同理从左边工具箱中,看到有很多种类型的数据源目标,我们选择“OLEDB目标”,将“平面文件源”控件下的绿色箭头连接到“OLEDB目标”,然后双击,打开“OLEDB目标编辑器”窗口,“新建”数据库连接,如图:返回到“OLEDB目标编辑器”窗口,在数据访问模式下,选择“表或者视图-快速加载”一项,然后再选择对应的表,如图:下面配置列映射,如图:如果没有的列,直接忽略即可(前提是表中该列允许为空),后面仍然
13、是配置错误处理方式,参照平面文件源错误处理方式即可。到此为止,一个简单的数据流任务就基本上完成了,点击运行,我们期待已久的结果出现了。当然,在实际开发过程中,可能并没有这么顺利,会遇到很多各种各样的问题,在这篇文章中我们很少提及,主要是因为这仅是个开始,没有涉及到这么深入,在以后的专题中,会逐渐讲解。一个简单的数据源任务就算完成了,其实这只是一个Demo ,让大家了解了一个概况,可以说万里长城只是走出了第一步,真正的ETL不会这么简单。下后面我们将介绍ETL最精彩的部分“数据流转换”,敬请期待。Integration Services学习(3):数据流任务(下)前一篇文章SSIS 学习(2):
14、数据流任务(上),介绍了如何创建一个简单的ETL包,如何通过一个简单的数据流任务,将一个文本文件的数据导入到数据库中去。这些数据都保持了它原有的本色,一个字符不多,一个字符地少导入,但是在实际应用过程中,可能很少有这种情况,就拿IisLog文件来说吧,其中包含有:请求成功的记录(sc-Status=200),也有请求失败的记录;有网页(比如:*.aspx、*.htm、*.asp、*.php等)、有图片、有样式表文件(*.CSS)、有脚本文件(*.js)等,可谓是鲜花与毒草并存,精华与糟铂同居啊,我们如何根据不同的需求,把其中的鲜花与精华提炼出来呢,这就是我们今天要讲的重点:数据流转换。在进行数
15、据流转换之前,我们先介绍一下使用场景:以IISLOG为依据,进行网站点击率分析(IP & PV 分析),具体需求如下:(1)分析一段时间内,网站点击率的变化趋势。同时还需要知道各个周未、各个节假日网站的流量情况。(2)分析一天内,各时段(以小时为单位)网站的压力情况。(3)了解网站客户群分别来自哪些国家,哪些地区。为了实现这些需求,我们建立了如下的数据模型,请看:代码下面,我们就一步一步地介绍,如何进行数据流转换,以达到上面的需求。(一)、条件性拆分(Conditional Split )。相当于Sql 语句的Where 条件。这或许是所有数据流转换任务的第一步,为了减少后续处理的数据量,为了
16、提高系统性能,先过滤掉不需要的记录。前面讲过,IisLog 文件包括有各式各样的记录,而对本例需求来说,为了准确计算IP、PV数据,我们将如何过滤呢?(1)、筛选出纯网页浏览记录。即*.aspx、*.htm(本网站只有这两种类型的网页文件)文件记录。(2)、筛选出请求成功的记录(sc-Status=200)。打开上一篇文件的SSIS Solution,切换到数据流Tab,从左边工具箱中,打开“数据流转换”,找到“条件性拆分(Conditional Split)”组件,拖到数据流面板上,然后将“平面文件源”组件下的绿色箭头拖到“条件性拆分”组件上,双击“条件性拆分”组件,打开“条件性拆分转换编辑
17、器”,如图:在这个窗口,有系统变量、数据源列、系统函数这些资源可供使用。我们为了筛选出纯网页浏览记录,需要从列cs_uri_stem中找到以.aspx、.htm、“/” 结尾的页面链接。请分别在上图列表的“输出名称”栏位,输入“Form Records”,在条件表达式栏位输入:RIGHT(cs_uri_stem,5)=.aspx|RIGHT(cs_uri_stem,4)=.htm |RIGHT(cs_uri_stem,1)=/然后筛选请求成功的记录,其表过式为:sc_status=200最后将两个表达式组合起来,即为:(RIGHT(cs_uri_stem,5)=.aspx|RIGHT(cs_u
18、ri_stem,4)=.htm |RIGHT(cs_uri_stem,1)=/)&sc_status=200如图所示:点击确定.数据过滤就算大功告成了。(二)、派生列(Derived Column),相当于SQL语句中的计算列,即根据其它列,按照一定的计算公式,派生出一个新列。在此例中,有三种情况需要用到派生列:(1)日期列,从log文件导入的日期、时间,为两个独立的字符串(varchar),而数据库中的对应字段为Datetime 型,如果要想建立一种映射,则需要根据log 文件的Date 、time 字段,派生出一个Datetime 型的字段。(2)时间段,同理log 文件中的Time 为一
19、字符串,需要取出其中的“小数(hour),才能与dimTime 中的lngHour 相匹配。(3)IP,我们想根据客户IP,确定他所在国家、省市、地区。要达到这一需求,我想并不需要IP完全匹配,只要IP的前三段匹配,就可以确定了(没有考证过,个人感觉而已,如不妥,请指正),所以需要派生出一个ipSegment =IP的前三段,以此映射他所在的地区。同理,从工具箱中,将“派生列”组件拖到“条件拆分”组件的下方,再将“条件拆分”组件下方的绿色箭头拖到“派生列”组件上,系统会弹出一窗口,要求选择条件拆分的的输出名称,如图:从下拉列表框中选择“Form Records”,点击确定。然后再双击“派生列”
20、组件,打开“派生列转换编辑器”,如图:这个窗口太眼熟了吧,那不是前面讲的“条件性拆分编辑窗口”吗?是的,非常类似,我就不罗嗦了,按图上要求,输入派生列名称,选择派生类型,输入表达式,后面的数据类型、数据长度、精度等属性,将根据派生表达式自动生成,一般是不允许修改的。(三)、数据类型转换。在Integration Services 中,数据类型匹配要求是相当严格的,尤其是后面要讲的查找(Lookup)组件,数据类型必须绝对匹配,才能Join ,否则将不成功。Integration Services 中的数据类型,它为了兼容多种数据源(比如平面文件、MssQL、ORACLE、DB2、MYSQL等)
21、,在形式上它不同于前面说的任何一种数据源的数据类型,一旦数据进入Integration Services 包中的数据流中时,数据流引擎就会将这些列的数据转换为Integration Services 的数据类型,前面介绍的“条件性拆分”、“派生列”中的表达式,都是对这种Integration Services类型的数据进行操作。所以如果后面要应用到查找(Lookup)组件,就必须要对这种数据类型进行转换,才可以与查找源(关系型数据库中的表或视图)的列匹配。具体操作为:从工具箱中,将“数据转换”组件拖到窗口上,将上一组件(派生列)组件下面的绿色箭头拖此组件上,双击打开“数据转换组件”,如图:勾选
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SSIS 学习
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【a199****6536】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【a199****6536】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。