原创

SSIS工具的ETL过程,全量ETL和增量ETL过程教程

测试环境:SQL SERVER 2012

Microsoft Visual Studio 2012版本

本教程为记录本人的学习关键部分,故如果看不懂可以联系博主,只讲关键ETL操作部分


1.全量ETL过程

示例:

(1)项目创建完成后,创建一个新的SSIS包

我这里就叫ODSCustomer2.dtsx好了

(2)创建数据连接

①在下方的连接管理器里面点击右键,新建OLE DB连接

②我这里是已经建好了,如果没有的话就点击新建,如果有的话直接点选-->确定

③连接管理器里面确保是连接的SQL Server Native Client 11.0,服务器名设置“.”,选择数据库,我这里是ADM_BF,选了之后下一步确定

④同样的方法创建目标ADM_ODS_BF

(3)使用到的控件有执行SQL任务,数据流任务

(4)整个过程分为3个步骤

①初始化数据表  Truncate table ODSCustomer

②创建数据流任务  Sourcedata to DW

③写入/更新操作时间日志 INSERT INTO ETLRunLog

第一个和第三个为   执行SQL任务 控件,第二个为数据流任务控件,下图是我对控件更名了的

tips:ETLRunLog是记录每次操作时的时间以及上一次操作的时间,方便做增量数据,表结构如下图

接下来是上面三步骤的详细描述:

①初始化数据表:

双击第一个控件(执行SQL任务)

在图中,1的地方选择OLE DB ,2的地方选择目标数据库,3的地方填写SQL   TRUNCATE TABLE ODSCustomer

②双击第二个控件数据流任务

1)此处进入第二个界面,该界面的控件与控制流里面的控件不一样,本次将会使用的控件有2个,一个是OLEDB源,一个是OLEDB目标

2)将两个控件拖动到数据流里面去,然后连接起来

3)双击OLE DB 源,连接管理器选择LocalHost.ADM_BF,表或视图的名称选择 [dbo].[Customer] ,点击确定

4)双击目标,连接管理器选择LocalHost.ADM_ODS_BF,表或视图的名称选择 [dbo].[ODSCustomer] ,然后将下面的勾上的表锁和检查约束去掉,然后点击映射

映射界面观察字段是否全部映射正确

点击确定,数据流任务配置完成

(5)返回到控制流,双击第三个控件  执行SQL任务,我这里改名叫 INSERT INTO ETLRunLog

前面两个的选择如上一个,sql参照下面

if exists(select * from ETLRunLog where packageName='ODSCustomer') update ETLRunLog set PreSuccessTime = LastSuccessTime,LastSuccessTime = getdate() where packageName = 'ODSCustomer' else insert into ETLRunLog select 'ODSCustomer',null,getdate()

然后点击确定即可

大功告成!




2.增量ETL过程

先看个流程图

增量ETL过程分为2种情况,其一是直接全量导入,其二是增量导入,描述下上面的流程图;

第一种情况是全量导入,第一步获得当前的时间,第二步Truncate Table ODSADLog,第三步将所有数据导过去(参考全量导入的数据流任务)第四步记入日志

第二种情况是增量导入,第一步是获得当前时间,第二步是获得上次执行时间(从ETLRunLog)里面获取,第三步增量加载到临时表,第四步Merge到ODSADLog表里面去,最后记入日志,第一种和第二种的分叉使用流程线的条件,看我的图里面有个函数,具体配置可以双击绿色的线条,如下图,求值运算选项选择    表达式和约束,表达式这里调用了变量,如果不懂变量去百度一下吧,或者加我QQ(在我的个人资料里面),我这里走哪个分支采用的变量取值,变量为1则全量导入,变量为0则增量导入

(1)我们只讲第二种情况(第一种就是全量导入),拖5个控件进去,其中第三个为数据流任务,其他的为 执行SQL任务控件,我这里给他们都命名了,添加数据源同全量导入的添加方式

接下来分别取编码操作

(2)

①创建变量,点击图中所示位置,选择变量

②点击左上角的新建按钮创建如下图所示的几个变量,数据类型已经列出来

(3)获取当前时间

双击第一个控件,里面的参数分别是 OLE DB,LocalHost.ADM_ODS_BF,直接输入,select getdate()(这个就是查询数据库的当前时间),下面的结果集的ResultSet字段选择单行,然后点击左侧菜单的结果集

然后在结果集里面,添加一个结果集接收,结果名称填0,变量名选择CurrentTime

(4)获取上一次操作的时间

①双击第二个控件,在画框的地方,第一个选择OLE DB,第二个选择LocalHost.ADM_ODS_BF,第三个的SQL填写查询ETLRunLog表中的对应ODSADLog数据的LastCurrentTime的值,sql如下,第四个结果集同上面一步,选择单行,然后切换到左侧的菜单里面的结果集页面

select LastCurrentTime from ETLRunLog where packageName='ODSADLog'



②按照如下图的数据进行操作,点击确定完成

(5)双击第三个控件(数据流任务)

①进入数据流页面,控件选择OLE DB源和OLE DB目标,然后将他们连接起来

②双击OLE DB源

此处不同于全量操作,这里是采用SQL命令来获取数据

sql如下,此处使用了2个问号,在右边的参数,可以点击进去对参数进行赋值

select * from ADLog where createtime >= ? and createtime < ?

点击参数按钮,进入对参数赋值的页面,在变量的地方选择参数0为LastSuccessTime,参数1为CurrentTime,点击确定,对数据源完成处理

③对OLE DB目标处理,双击OLE DB目标,然后按照如下图的进行选择,然后点击映射进入映射页面观察字段是否映射正确,注意表锁和表结构的复选去掉

点击确定即可

(6)返回控制流页面,双击第四个控件

进入之后,按照如图所示的进行填写,sql的地方填写如下的sql

merge into ODSADLog a
using TempODSADLog t on a.SourceAdLogID = t.SourceAdLogID 
and a.SourceADMLOGIP = t.SourceADMLOGIP 
when matched then
update set
     a.[UID] = t.[UID]
      ,a.[SourceADTagID] = t.[SourceADTagID]
      ,a.[SourceADID] = t.[SourceADID]
      ,a.[Referer] = t.[Referer]
      ,a.[Type] = t.[Type]
      ,a.[IP] = t.[IP]
when not matched then
insert (
	[SourceAdLogID]
      ,[SourceADMLOGIP]
      ,[UID]
      ,[SourceADTagID]
      ,[SourceADID]
      ,[Referer]
      ,[Type]
      ,[IP]
      ,[CreationTime]
) values(
	t.[SourceAdLogID]
      ,t.[SourceADMLOGIP]
      ,t.[UID]
      ,t.[SourceADTagID]
      ,t.[SourceADID]
      ,t.[Referer]
      ,t.[Type]
      ,t.[IP]
      ,t.[CreationTime]
);

(7)最后双击最后一个控件,对操作进行日志写入

按照如下图进行操作,sql如下

if exists(select * from ETLRunLog where packageName='ODSADLog') update ETLRunLog set PreSuccessTime = LastSuccessTime,LastSuccessTime = @curr where packageName = 'ODSADLog' else insert into ETLRunLog select 'ODSADLog',null,getdate()

正文到此结束
本文目录