SQL SERVER 2008 SSIS 实战文档

SQL SERVER 2008 SSIS 实战文档1 使用CDC模式实现增量更新 SQLSERVER2008 CDC(Change Data Capture)其主要是通过对事务日志的异步读取，记录DML操作的发生时间、类型和实际影响的数据变化，然后将这些数据记录到启用CDC时自动创建的表中。通过SQL Server 2008 CDC相关的存储过程，可以获取详细的数据变化情况。由于数据变化是异步读取的，因此对整体性能的影响不大，远小于通过Trigger实现的数据变化记录1.1 SSIS实现增量更新步骤如下1.1.1 增量更新所用到的表,存储过程，函数等1.1.1.1 变更日志表u 日志表: 记录ETL每次更新的时间.只保留最后一次更新的结果1.1.1.2 详情日志表u 日志表: 记录ETL每次更新的时间.每一次的结果都保存1.1.1.3 禁用CDC变量捕获机制u 该存储过程功能说明: 禁用数据表的变更数据捕获权限1.1.1.4 启用CDC变量捕获机制u 该存储过程功能说明: 启用数据表的变更数据捕获权限1.1.1.5 启用CDC变量捕获对应的业务库u 功能说明: 启用增量捕获功能1.1.1.6 获取事实表变更日期u 该函数功能说明: 获取数据变更日期(以变更数据捕获功能为依托)1.1.2 SSIS实现增量更新的过程1.1.2.1 判断是全量抽取，还是增量抽取u 实现方式: 第一次是全量更新，以后每次都增量更新.详情请看下面步骤的讲解u 实现方式: judge has record里面要执行的SQL语句是从事实表取结果，看是否有数据，如果没有数据，说明是第一次更新，即全量更新.u 实现方式: calculate time range计算ETL时间范围.表示的意思如下: 如果记录大于0 并且开始日期等于结束日期的话，表示加入CDC增量更新是第一次加入的。如果记录大于0 并且开始日期小于结束日期的话，表示CDC增量更新已经运行过，以后就走这条路线。1.1.2.2 全量更新的步骤u 实现方式: 把生产库里面的所有有效的数据生成到数据仓库里面.1.1.2.3 增量更新,更新前三个月的数据【升级考虑】u 实现方式: 把生产库里面的前三个月有效的数据生成到数据仓库里面.1.1.2.4 增量更新,更新业务库有改变的数据，机制是CDC模式u 实现方式: 把生产库里面的有修改，有新增的数据生成到数据仓库里面.1.1.2.5 更新最后一次ETL更新的时间，用于下次抽取考虑u 实现方式: 更新最近一次ETL更新时间.1.1.3 SSIS日志记录u SSIS日志表: 可配置，将事件的日志项写入 SQL Server 数据库。包括处理错误事件，信息事件，警告事件2 使用时间戳模式实现增量更新2.1 维度表的增量更新，及全文配对如下,整体框架图设计(包括控制流和数据流)2.1.1 控制流的设计方案2.1.1.1 执行SQL任务之StartTime2.1.1.2 数据流任务2.1.1.3 执行SQL任务之EndTime2.1.1.4 脚本任务2.1.1.5 执行SQL任务之保存日志，根据表达式条件(Insert)2.1.1.6 执行SQL任务之保存日志，根据表达式条件2.1.1.7 执行SQL任务之保存日志，根据表达式条件(Update)2.1.2 数据流的设计方案:2.1.2.1 提取业务库的数据2.1.2.2 数据流控件之查找，看数据仓库是否存在，根据主键来查找，来判断是否为增量更新。2.1.2.3 查找后无匹配输出，说明数据仓库中不存在该数据，需要进行插入的操作。进入数据仓库的数据要判断跟该维度表关联的其他维度是否存在，即使用查找匹配如果其他维度表关联的都存在，需要使用查找匹配输出，进入行计数，获取结果集，用于日志记录的最后都完成的话，把数据加载到数据仓库里面。2.1.2.4 查找后匹配输出，说明数据仓库存在该数据，要进行删除存在的记录操作，重新插入。进入行计数，记录update记录集，供日志记录操作。删除已经存在的数据仓库的记录最后完成的话，把数据加载到数据仓库里面。2.2 事实表的增量更新，使用时间戳的形式。整体框架图2.2.1 控制流的设计方案2.2.1.1 执行SQL任务之StartTime2.2.1.2 执行SQL任务 之CREATE_ERROR_FACT(用于存储事实表没关联到维度表的记录)2.2.1.3 执行SQL任务之Log_Fact2.2.1.4 执行SQL任务之创建对照表2.2.1.5 执行SQL任务之插入 抽取表2.2.1.6 执行SQL任务之判断时间戳2.2.1.7 如果不存在时间戳字段，通过脚本任务VB代码插入该时间戳字段。执行任务之插入时间戳字段的脚本.2.2.1.8 如果存在时间戳字段，通过脚本任务VB代码更新他上一次更新的时间。2.2.1.9 执行SQL任务之更新Nextdatetime2.2.1.10 执行SQL任务之判断是否抽取(根据时间戳的形式加以实现。)2.2.1.11 如果没有改动的保存日志记录。（抽取几张表，保存几张表的日志记录.）2.2.1.12 如果有数据改动得话，通过使用脚本任务，根据改变的时间戳状态，获取改变的数据。2.2.2 数据流任务的设计方案。2.2.2.1 数据流源之OLE DB提取业务库的数据，使用变量中的SQL命令的方式实现2.2.2.2 数据流转换之派生列2.2.2.3 数据流转换之查找维度是否存在。2.2.2.4 数据流之查找维度不存在，是无匹配输出的话，使用UnionALL把该信息保存到日志里面.2.2.2.5 数据流转换之添加派生列2.2.2.6 数据流转换之OLE DB命令，把无匹配输出的数据插入到日志表里面2.2.2.7 数据流转换之查找维度存在，既有匹配输出的话，则使用多播的形式，一种供主表用，另一种供从表用。插入到数据仓库里面去。2.2.2.8 数据流转换之查找判断数据仓库中该值是否存在2.2.2.9 查找无匹配输出的话，说明数据仓库中不存在，要执行插入的操作。使用行计数，记录日志。2.2.2.10 查找匹配输出的话，说明数据仓库已经存在，则需要删除，在重新载入。2.2.2.11 使用多播的形式供从表用，要排序的。2.2.2.12 数据流源之OLE DB 从表提取业务数据2.2.2.13 数据流转换之排序，把从表的数据进行排序 2.2.2.14 使用合并链接把两个排序好的数据集进行排序操作2.2.2.15 数据流转换之添加派生列2.2.2.16 数据流转换之字符映射表2.2.2.17 数据流转换之查找匹配，同从表的操作，把无匹配输出的保持到日志里面，有匹配输出的使用多播插入到从表中，并聚合到其他表中。2.2.2.18 查找匹配输出的话，使用多播的形式，插入到子表中2.2.2.19 数据流转换之查找，查找数据仓库是否存在该数据，如果存在的话，删除，不存在，插入结果同主表的方式。2.2.2.20 使用多播的形式，另一种情况使用聚合来使用的方案如下:2.2.2.21 然后同样使用查找功能，查找数据仓库是否存在，存在删除，不存在插入操作如下。2.2.