使用SSIS汇集监控数据

1、背景

1、数据库的告警日志是必须进行的工作,监控并且应该根据不同的严重级别,发送不同级别的告警信息(通过邮件、短信),及时了解数据库的变化与异常,及时响应并介入处理。
2、DBA应该对据库部署完善的监控系统,并对重要信息进行采样,能够实时或定期生成数据库重要指标的曲线图,展现数据库的运行趋势。
3、每天至少了解或熟悉一个Top SQL,提出优化和调整建议。应当对稳定系统中的SQL非常熟悉,这样才可能在系统出现性能问题时见微知著,快速地作出判断和响应。
4、根据对数据库的研究和了解,不断记录数据库的状况,撰写数据库架构、现状及调整备忘录,不放过任何可能的优化与改进的机会。

数据库服务器部署有各类巡检脚本,现在想把巡检收集到的数据汇集到一起。

Source源对应的是各业务数据库服务器,Destination目标对应的是保存汇集数据的服务器。前期部署的时候已将巡检相关的保存在同一个数据库下,方便移植到其他服务器。每张表都有id(自增列)和时间字段。

2、获取数据

由点到面,先取一个实例下的一张表,然后取多个实例下的一张表,最后扩展取多个实例下的多张表。

2.1、获取一个实例下的一张表

源表中的数据不断有新增,目标每次应该只获取新增部分(当然可以先清空再全部获取,显然这样不妥)。首先获取目标表最新的记录max(InsertTime),然后将源表max(InsertTime)之后的记录插入到目标表。
添加[执行SQL任务]Database,SQLStatement获取目标表的最新记录,将结果集置为[单行],传递给变量(用户::InsertTime)


添加[数据流任务]DFT_Database,[OLE DB源]使用SQL命令访问模式,输入带参数的命令文本,并编辑参数映射;[OLE DB目标]选择对应的表或视图名称

注意:如果我们使用时间字段(库中为datetime类型)区分新增数据,创建变量时需将类型设置为string类型,SSIS变量中的DateTime类型只到秒,对毫秒采取"四舍五入"。例如获取目标最新记录为'2016-11-29 22:05:58.497',把它赋给DataTime类型的SSIS变量会变成'2016-11-29 22:05:58',再往下执行的话会重复获取'2016-11-29 22:05:58.497'的记录。如果[OLE DB源]使用的是存储过程,参数映射中的名称必须与存储过程定义的参数名称一致(测试中我将存储过程参数名称修改为@insertTime,报错:SQL 命令需要名为“@insertTime”的参数,但在此参数映射中找不到该参数。)在VS2005/2008测试发现SSIS将时间字段类型设置为string类型,存储过程的参数可用varchar或datetime)。

2.2、获取多个实例下的一张表

首先我们将源的连接信息保存到连接配置表

添加[执行SQL任务]GetConnection,SQLStatement获取配置表中的连接信息,结果集置为[完整结果集]

参数映射添加需要的变量信息,结果集添加需要的变量信息。变量的使用可参考文章:变量在SSIS包中的使用


源连接信息已经有了,再添加[Foreach循环容器],对枚举执行迭代。集合页参照下图设置

变量映射页参照下图设置

我们的目的是迭代配置表中的源连接,重置连接信息,执行获取数据操作。因此需要修改Source连接为表达式,连接管理器中右击Source->属性->Expressions,编辑ServerName和ConnectionString属性表达式

设置好后再将2.1中的[执行SQL任务]Database和[数据流任务]DFT_Database拖入[Foreach循环容器]。此时正常的话,针对配置表中的每一条源连接信息,Source就使用表达式重置源,这样就可以得到对应的源数据。
是否记得GetConnection中有将InstanceID和ServerID赋值给变量,当目标端保存有多个实例数据后,我们需要用InstanceID区分数据来自哪个实例,因此在[执行SQL任务]Database,获取目标上的最新记录时,需在命令中添加WHERE条件,参数映射上添加用户::InstanceID变量


到此,基本已经完成动态获取多个实例下的数据。其他的数据表只需参考[执行SQL任务]Database和[数据流任务]DFT_Database,添加到[Foreach循环容器]即可。

图中变量应该在一开始就创建,获取目标最新记录、获取源连接信息需要使用到这些变量。连接管理器中的Source通过表达式得到各服务器的连接信息,Destination连接的是目标服务器。

2.3、完善及疑问

1->对于数据量很大的情况,我们可以将[执行SQL任务]Database和[数据流任务]DFT_Database添加到[For循环容器],设置每次获取的记录数,反复执行控制流,直至求值条件为False。
2->记录包执行的日志,方便查看执行过程中所处位置,以及出错时的错误信息。事件处理程序对包(GetMonitorData.dtsx)捕获错误信息 (OnError),添加[执行SQL任务],获取系统变量信息(User::SourceServerName、System::PackageName、System::SourceDescription、System::SourceName、System::ErrorDescription)
3->包中的SQL命令全部使用的是"查询语句",然后分配给连接登录名reader权限。可将"查询语句"封装到存储过程,仅给登录名执行对应存储过程的权限。
4->动态连接信息是从数据表中获取信息,对应保存有登录名、密码、数据库信息,感觉不太好!在没有使用其他方法前,很有必要尽量限制相关登录名的权限。

上图就是修改"查询语句"为存储过程的过程中,捕获到的错误信息。通过此表很容易就能定位到是哪个源(SourceServerName)的哪个包(PackageName)的哪个任务(TaskName)的哪个组件,在什么时间(InsertTime)出错(ErrorDescription)。
修改源和目标数据表的列类型(由varchar(4000)->varchar(max)),如果存在超长的记录,会导致SSIS包执行报错,需要更新数据流任务中的元数据。

3、部署

SSIS包调试通过后,需要将包部署到服务器,按照设定的调度定期获取数据。

3.1、SSIS包如何放到作业中执行

SSIS中调试包一切正常,但把它放到作业中,执行却报错

关键字:无法解密受保护的XML节点"DTS:Password"
这是受包的Protectionlevel影响,VS中打开包(.dtsx),在设计页面的空白处右击->属性,修改Protectionlevel为EncryptSensitiveWithUserKey,然后键入PackagePassword的值。下次VS打开包或者作业引用此包时需输入前面的Password。

3.2、打开SSIS包时验证时间过长

默认情况下SSIS在运行包之前会验证包的每个数据源,若出现较为复杂的情况则可能需要较长的时间。修改可执行文件(任务、容器和包本身)的DelyaValidation属性为True,修改数据流组件的ValidateExternalMetadata属性为False,延迟验证。

posted @ 2016-11-30 16:30  Uest  阅读(711)  评论(3编辑  收藏  举报