SSIS探索之SSIS增量抽取数据

背景

         在工作中，我想对业务系统的日志信息进行分析，需要将日志信息抽取到另外一个表里，由于日志表的数据量庞大，我只需要从某一特定时间开始对业务系统产生的日志抽取出来，而且需要每5分钟（假定）进行抽取一次，把在这5分钟内产生的日志信息抽到我的表里面，产生一个增量抽取的效果。

 

解决过程

        在网上找了很多的资料，关于SSIS数据抽取的有不少，但是增量抽取的解决方法不多，而且有的看起来比较复杂。下面分享下我的解决方法。

 

 

解决思路

 

1.      创建SSIS包

2.      在SSIS包中设置开始时间和结束时间的变量

3.      在数据库中创建一张时间表，里面记录开始时间

4.      在SSIS包中创建执行SQL任务，将时间表里的开始时间以及运算的结束时间赋给包中的开始和结束时间变量

5.      创建数据流任务

6.      在数据流任务中添加OLE DB源和 OLE DB目标

7.      在OLE DB源中执行带有参数的SQL语句，将包变量开始时间和结束时间赋给语句的参数

8.      连接OLE DB源和 OLE DB目标，完成数据流任务

9.      创建执行SQL任务，更新时间表中的开始时间

10.    连接各部分完成SSIS包并执行。

 

开发环境

 

1.      Windows 7 64位操作系统

2.      SQL SERVER 2008 R2 的SQL ServerBusiness Intelligence Development Studio

3.      使用的数据库自然是SQL SERVER 2008 R2啦。

 

关键点分析

 

1.      如何将时间表的时间取出来赋值给包的两个变量：开始时间和结束时间

2.      在OLE DB源如何执行带有包变量的SQL语句。

 

准备

 

1.      创建数据库BITest

2.      创建业务系统的日志表logs（模拟的）

 

insert into dbo.logs

select '日志信息','2014-11-14 23:00:00','正常'union all

select '日志信息2','2014-11-14 23:01:50','正常'union all

select '日志信息3','2014-11-14 23:05:20','正常'union all

select '日志信息4','2014-11-14 23:07:30','失败'union all

select '日志信息5','2014-11-14 23:10:30','正常'union all

select '日志信息6','2014-11-14 23:13:30','失败'union all

select '日志信息7','2014-11-14 23:16:30','正常'union all

select '日志信息7','2014-11-14 23:21:30','正常'union all

select '日志信息7','2014-11-14 23:24:30','正常'union all

select '日志信息7','2014-11-14 23:30:05','失败'

 

3.      创建时间表bi_startTime(假设初始时间是2014-11-14 23:00:00)

insert into dbo.bi_startTimevalues('2014-11-14 23:00:00')

4.        创建目标表logsbak

 

解决步骤

 

1.      创建SSIS包

        选择新建【Integration Services项目】，输入项目【名称】以及选择项目存放的【位置】，点击【确认】。

 

2.      新建数据源

        右键【数据源】单击【新建数据源】打开添加数据源向导，完成添加数据库。在连接管理器中右键选择【从数据源新建连接】，选择可用的数据源，然后确定。

3.       在SSIS包中设置开始时间和结束时间的变量

          点击【SSIS】下的【变量】，在旁边的变量面板创建startTime和endTime。

 

4.      创建获取开始时间的执行任务

         在工具箱中拖入执行SQL任务控件到控制流，双击该任务打开设置面板。在常规选项卡中设置数据库连接，【SQLSourceType】为直接输入，SQLStatements一项输入selecttimeasstartTime,DATEADD(MINUTE,+5,time)asendTime frombi_startTime。【ResultSet】一项选择单行。在结果集选项卡中【添加】，将结果赋给自定义时间变量，然后【确定】。

5.      创建数据流任务

         将数据流任务控件拖入控制流，双击进入数据流设计面板。拖入OLE DB源和OLE DB目标到设计面板。双击OLE DB源进行编辑。

         在编辑面板中选择数据源，【数据访问模式】选择SQL命令，【SQL命令文本】中输入select * from logs where endTime > ? andendTime < ?，注意这里是关键，用问好?来代替要替换的自定义时间变量，不能用@[user:startTime]这种引用变量。然后点击旁边的【参数】。按?的顺序来映射我们定义的时间变量。然后点击【确定】。同样，设置OLE DB目标为我们要将数据抽取到的表logsbak.

 

 

6.      创建更新数据表的SQL执行任务

         拖入实行SQL任务到控制流，双击该任务进行设置。【SQLStatement】一项中输入update bi_startTimeset time = (select DATEADD(MINUTE,+5,time) as endTime from bi_startTime)【结果集】选择无。然后【确定】。至此，我们的包就设计完了。

 

运行

 

         单击【调试】启动调试，可以看到包执行成功了。每执行一次包就将相应时间段的记录信息插入到目标表中。然后将SSIS包部署到服务器上开个定时任务就可以了。

 

总结

 

        该方案或许只适合源数据表是静态的诸如日志这种流水账那样的增量抽取，对于原数据表中数据有变化如更新，删除等的增量抽取还需要做一些改动。当然，自定义的结束时间变量设为开始时间后5分钟也有点问题，如果服务器挂了，重新运行包的话，那目标表获取到的信息会滞后很多，可以把结束时间赋一个当前时间的值。

        或许你会问这样做的意义在哪里呢，将一个日志表的数据复制到另一个表去，直接分析源数据表就好了。可实际情况可能是业务系统的数据库是分布式的，每个数据库中的表结构相同，都有一个日志表，我需要将所有日志表的信息汇总到一个表上统一分析处理的话可能会有这样的需求。而且要做到实时同步各日志表的信息又不大可能，所以就设计成按一定的时间段将数据同步到一个表中啦。

        以上只是一个新手的实践和看法，如果有误，还请指正哈。当然还有更好的实现复杂情况的增量抽取方案，也希望各位分享下哈。