微软的东西还有一块比较大头的就是ETL开发工具SSIS,下面我给你示范下SSIS的创建和开发:
打开VISUAL STUDIO
点击文件:
然后新建项目:
新建一个Integration Services项目,下面的名称和位置是可以改变的,右下角的Add to Subversion是添加到版本控制,也就是SVN上。然后点击确定,即可,就会出现如图:
主要就是我勾的那三块需要配置,添加数据源和数据源视图其实是跟SSAS里面是一样的,这里我继续给你说一下步骤,但是就不截图了,关键的地方再截图。
右键数据源-》新建数据源-》点击下一步,就又是这个界面了:
然后点击新建:
然后填进去:
服务器名,随后:
这里填进去用户名和密码,保存密码,方便下次使用。
然后测试连接,然后点击确定即可。
记得:
这一块不要去改动,这个是微软推荐使用的方式。
然后数据源就选好了:
然后添加数据源视图,右键,新建数据源视图,会出现如下图:
我们选定后点击下一步即可,然后就是继续下一步,选择表,然后点击确定完成即可。
然后双击:
Package.dtsx进行编辑,这个名字是系统自己命名的,我们可以右键重命名进行更改,修改完了会问你是否重命名对象,点击是即可,我给它重命名的是orderform:
记得,后缀名不要改掉了,就是.dtsx,不要动它。好啦,然后就是给你说控件的作用了,微软的东西基本都能靠拖拉控件来完成的,下面我就给你说下各个控件的作用,控件是在这里:
点击工具箱,就出现下图:
先说第一个控件:
For循环容器,这个控件的作用其实就是实现一个For循环,根据设置变量,判断变量来实现For循环语句的作用,使用方法,把它从左边拖到这里:
控制流下面,就会是这样了:
然后双击这里,就会进入编辑模式:
进行编辑后就会是这样:
我来说下每一个具体的含义:
第一个@begindate是参数,是开始时间。在这里你可以认为是针对orderform的一个全局变量,在哪里设置这个全局变量一会我在告诉你。
(DT_DBDATE):是变量的类型
DATEADD(“day”,-8,getdate()):dateadd是数据库自带的函数,进行日期计算的。比如在SQL SERVER里: select DATEADD(DAY,-8,GETDATE()) 就是八天前是哪一天。Getdate()是获取当前日期时间。DATEADD(DAY,-8,GETDATE())整个的含义就是getdate()+(-8day),里面的参数和数值可以随便变。
这个就是设定一个For的开始日期。
这个就是For循环的结束日期。
这个就是For循环里,执行的频率,这个里面就是每次执行七天内的数据。你把它改成多少每次就执行几天内的数据。
这个对你来说应该很好理解。
然后就是设置变量,其实设置变量应该是在前的,但是也都无所谓,设置变量的位置在:
点击变量按钮,然后会弹出上图左边部分,然后上面圈着那里,就是添加变量的,然后点击:
这样就添加了一个变量,上面圈着的那里可以删除变量,然后修改变量的名称和数据类型,还有值,我们把变量叫做begindate,然后作用域可以选是针对整个包还是只是For循环容器,然后再选择数据类型和值,因为是时间,所以我们选择date time,然后值的话,系统会默认,这样设置完毕后,就出现下图:
这样参数就设置好了,这样一个完整的For循环容器也就设置好了。下面就是添加一个数据流任务:
把数据流任务拖到For循环容器里面,要拖到For循环容器的框框里:
数据流任务其实实现的功能就是一个SQL脚本的功能,我们先继续看下去,再看看估计你就明白了,双击数据流任务,就出现下图:
注意这个时候,编辑界面已经跳转到数据流里了,左边的工具箱里,具体的工具也已经变成了数据流源和数据流转换了。
数据流源:其实就是数据的来源数据库和数据的结果数据库。
数据源转换:其实就是一系列的SQL语句操作,只不过在SSIS里是用控件来实现的。
数据源目标:其实就是目标表所在的数据库。
先来说数据流源和数据流目标常用的两个控件:
- OLE DB源:就是数据的来源数据库,也就是来源表所在的数据库。
- OLE DB目标:就是数据的目标数据库,目标表所在的数据库
先来看OLE DB源的配置,把OLE DB源拖到数据流任务里:
就是这个,然后我们进行编辑配置,双击即可,就会弹出下图:
然后我们进行配置:
点击新建,弹出下图:
这里就是我们已经建立的数据源,如果没有建立的话,就点击新建,然后方法同数据源。
然后这里我们选择一个数据源,然后点击确定。然后我们就选择表:
这里会让你选择表的,然后选择你需要的表就可以了:
然后再去选择列,这里选择列的原因是过滤掉不需要的列,只选择我们需要的列,提高性能,如果你全部选择的话那就无所谓了。如图:
然后选择好了后点击确定即可。
这样一个数据源就建立好了,也就是一个表也就弄好了,如果需要对这个表做什么操作,我们可以选择一种简便的方法:
在数据访问模式里选择SQL命令:
然后直接输入SQL语句即可,可以对表做种种SQL支持的前提下的操作。
我这里又新加了一个地域维度表的数据源:
就不演示添加步骤了,然后我要让两个表进行关联,因为SSIS里面关联之前需要对表进行排序的,所以这里要添加一个排序的控件(在数据流转换中可找到),如图:
注意这里:
这个就是一个依赖关系,也就是先后关系的限制条件,弄明白步骤的先后顺序,拖拽这条线,到排序这个控件上,这样,他们就会建立一个运行的先后顺序了。双击排序进行编辑:
因为我们是与地域表关联,所以我们选择areakey作为排序的字段,勾选即可,然后点击确定,这样对于OLE DB源的排序就完成了,同理操作OLE DB源1,然后就如下图:
然后我们就可以进行关联了,添加合并联接控件(在数据流转化里可找到),如图:
然后就去编辑合并联接控件,先弄先后关系,如图:
把这个线从排序拖到合并联接上,在拖拽的时候会弹出一个对话框,如下图:
让你选择是放在join的左边还是右边,我们这里是直接关联,所以无所谓,选择左侧即可,然后如图即是正确的:
同理,配置右侧的排序1,最终如此:
编辑合并联接,双击:
这样配置,把左外连接改为内部连接即可。具体是用内连接还是左外连接或者右外连接,主要还是看你实际的需求是怎么样的。
然后这些都完毕了,就到了数据到结果数据集里的时候了,如下图添加一个OLE DB目标:
把左边的OLE DB目标拖到右边即可,然后合并联接下面有一条线:
把这条线的位置拖到OLE DB目标这个控件上即可确定一个先后顺序的关系,如下图:
正确连接后都会有一个绿色线,绿色的箭头。双击OLE DB目标控件,就会弹出跟OLE DB源一样的页面:
同样的选择表或者视图的名称,我在数据库里创建一个叫做test的表,里面包含fact_orderform_test的全部列和dimarea-1中地域的三个字段拼接的数据。所以我们先需要一个派生列的控件,先把合并联接与OLE DB目标的关系删掉:
点住线后按delete按键即可,然后我们添加一个派生列的控件:
控件在数据流转换里。把派生列拖到右边即可,然后再建立先后关系,如图:
这样即可,然后双击派生列控件,点击进去编辑:
这里的列,是合并联接后全部的字段,都在里面,这里我们派生列的名字叫做area_full_name,可以自定义,然后派生列可以选择派生的类型:
看到了没,下拉框里有很多选项,第一个选项是作为新列添加,就是增加一列。下面的都是替换,也就是你把字段数据类型转化后,替换原先的那一列,这里我们选择作为新列添加,然后配置表达式,因为我们新派生的列是三个地域字段拼接的,所以配置如下图:
然后系统就自动把数据类型给你确定了,还有长度。这样我们就不需要管了,然后点击确定即可,然后再建立派生列跟OLE DB目标的关系:
然后双击OLE DB目标,编辑OLE DB目标:
然后再在
选择目标表的名称,这里我们选择test表,然后点击左上角的映射,进去编辑映射关系:
看到了没,系统自动把输入列和目标列匹配了,微软是根据列与列的名称和字段类型来进行匹配的,所以很多时候就会有其实你想要两列进行匹配,但是却匹配不了一说。这个时候你就要查看列名是否相同,字段类型是否相同。如果审查完匹配没有问题,那么好了,点击确定:
好了,这样一个基础的SSIS项目就完成了,可能你感觉什么OLE DB源、OLE DB源1之类的很不方便,这些都是可以来重命名的,如图:
所有的这些都可以重新命名,命名为你比较方便识别的。
一般来说一个项目会比这个更复杂,然后里面会有多个SSIS包,但是只要你弄明白其中一个,那么其余的也都是小事了。
这里你可能感觉如果多个表,很复杂的话该怎么办,其实还有个偷懒的办法,新建一个SSIS包,然后如图:
直接放进来一个执行SQL任务的控件:
在这个里面
下拉会有一个执行SQL任务控件:
然后拖拽到右边:
双击,编辑:
然后就是这样,只需要配置connection和SQLstaement这两块:
先配置connection:
点击新建连接:
然后就会出现和配置数据源一样的界面了,然后点击选择对应的数据连接即可,然后点击确定,然后再配置SQL statement:
点击下图画圈的地方:
然后就会出现:
这里可以支持SQL语句查询,可以支持SQL语句,带参数的SQL语句,简单或者复杂的都可以,按照程序一样编写也可以。
这个是个偷懒的办法,不建议前期使用,因为这样的话,就跟用脚本做一样了,而且效率还会比正常的脚本低,使用工具的一个考虑就是方便、快捷,好查找问题。传统用脚本查找问题对于基础数据库知识能力考验挺高的,使用工具的话,你可以直接根据执行步骤发现在哪里报错了。
然后运行的话与SSAS类似,右键SSIS包的,弹出如下界面:
然后点击执行包即可,我执行后会如图(绿色是运行成功的,黄色是正在运行的,白色是还未开始运行的,如果有错误的话对应的模块就会出现红色):
就开始执行了,进度这里就相当于程序运行的日志:
如果报错的话,就会出现报错的详细信息。运行完毕后,点击这里的停止调试,就会回到编辑模式:
如果你程序运行期间你想手动停止程序的话,这里有个停止调试: