代码改变世界

如何在 ETL 项目中统一管理上百个 SSIS 包的日志和包配置框架

2014-02-19 02:00  BIWORK  阅读(13837)  评论(25)  编辑  收藏  举报

一直准备写这么一篇有关 SSIS 日志系统的文章，但是发现很难一次写的很完整。因为这篇文章的内容可扩展的性太强，每多扩展一部分就意味着需要更多代码，示例和理论支撑。因此，我选择我觉得比较通用的 LOG 部分，在这里分享一下给大家，希望对大家在设计 ETL 的日志系统时有所启发和帮助。当然在这里要区分 Logging 和 Auditing 的区别，Logging 主要用来记录发生了什么事情，Auditing 侧重描述过程中产生的数据量，新增了多少，修改了多少等记录条数。本文主要讲解 Log 部分，以后有时间再来讲解如何在 Log System 中集成 Auditing 的功能。

  1. 首先，在这里提出几个问题，可以试着回答一下？
  2. 假设我们项目中，一个项目中最终上线的 ETL 包多达上百个，如何对这些包进行统一的日志管理 ？
  3. 现在在线运行的 ETL 包有多少个？ 多长时间？ 哪些包的运行时间最长，哪些最少 ？ 项目经理需要一份图表能够反映出这些 KPI 来。
  4. 每天运行的 ETL 包有多少个？ 测试环境，开发环境上都跑了多少个 ?
  5. 如何快速查询每个包运行的状态，成功否，失败否，失败的原因等等 ？
  6. 每个包都有一个配置文件，几百个包的配置文件又是如何进行管理的 ？ 这些配置文件中都有些什么内容 ？现在配置的参数都各自是什么？
  7. 这些包各自大概属于哪些部门使用的？业务范围是什么？ 这些包失败了找谁？ 谁开发的？
  8. 哪些包是用来加载文件，输出文件的，哪些包只是用来一般的数据转换的 ？ 输入文件在哪里 ？ 输出文件在哪里 ？
  9. 随便给出一个 SSIS Package 的名称，你知道它大概是做什么的吗？
  10. 每周的项目会议中，考虑过没有拿出上面的这些答案，数据，图表来对付各个老大们的提问 ？
  11. .........

这些问题其实都是应该要好好考虑的，并且完全可以在项目开发之初，花上一天或几天时间搭建与适用于当前项目的日志系统，然后再花上一定时间对这套日志系统的报表完成设计和开发。在解决上述问题的同时，我相信受益的不仅仅是各个工作在一线的开发者，不仅仅可以提高他们的工作效率，而且在后期可以减少大量维护运营 ETL 的人力和时间成本。

本文将分为以下几个部分来阐述

  1. 日志系统的在 ETL 项目中的位置
  2. 日志系统的角色构成
  3. 日志系统的数据库对象
  4. 日志系统在 SSIS Package 中的使用
  5. SSIS Package 配置管理在日志系统中的集成
  6. SSIS Package 模板开发
  7. 日志系统的报表开发

一. 日志系统的在 ETL 项目中的位置

日志系统简单来说就是一个数据库，里面有一些维护和管理日志数据的数据表以及一些视图或者存储过程构成。它在 ETL 项目中的位置应该独立于其它任意数据库，比如 BI 项目中的 Staging 数据库，DW 数据库以及各种各样的 Transaction 数据库。在一个服务器中，可能我们因为业务的原因，系统中存在一个或者多个 Staging 数据库，DW 数据库，但是日志数据库只有一个。这个日志数据库是所有包含 ETL Package 项目的真正核心，它管理和维护着各个 ETL Package 中的所有日志状态，包配置，主题划分等信息和内容。 而这些 ETL 所做的事情就是在各个数据源，目的地数据库中间抽取，转换，存储数据的工作，所有的工作操作记录将保存在日志系统中。假设这个日志数据的名称就叫做 BIWorkLog，当然有我在博客园的 ID - BIWork，但字面意思更容易理解 - BI 工作日志 ：）。

 

二. 日志系统的角色构成

 

所谓角色构成即它们在日志系统中扮演的角色。

Business Scope - 我给它的定义就是业务边界，什么叫做业务边界。假设 ETL 系统中开发并部署了上百个 SSIS Package，这些 Package 肯定有来自不同的 Group 的吧，比如市场部门的，财务部门的。自然市场部门的业务主要是围绕市场方面的 ETL 需求，财务部门的主要围绕财务部门的。当然换另外一种说法，我们在开发项目定义需求的时候，会有模块划分，也会定义出业务主题和边界。那么这里的 Business Scope 就是这种作用，对 ETL 做出主题划分，并且这里的 Business Scope 将贯穿 ETL 设计过程的始终。比如 ETL 的命名首先就以 Business Scope 开头，当看到这个 ETL 的时候就大概知道这个 ETL 也什么业务范围的了。 记住：命名规范在哪里都使用。

Solution - Solution 和上面的 Business Scope 的含义也比较类似，但是面要更小一些。通常情况下，会把相同业务或者相关业务的 ETL 创建在同一个项目中，这里的解决方案就可以描述为 Business Scope ,而项目的名称可以描述为 Solution，然后下面会有很多的 ETL SSIS Package。ETL 的命名和它也有关系，BusinessScopeName_SolutionName_XXX。当然，这里的 Solution 和 创建项目中的 Solution 含义上有点小小的区别。

Solution ETL - Solution 下的具体 SSIS Package，这是真正的运行数据加载，清洗和转换的包的定义区域。而日志功能就是围绕 Solution ETL 来进行记录的。

Data Flow - 数据流，数据流类型。比如文件的加载有 Input 有 Output 方向的，也有同时兼备 Input 和 Output 的。非文件的 ETL 数据流类型那就是普通的 Transformation 了。任何的 ETL 无非就是这些类型，将 Data Flow 的类型定义在 ETL 的命名上， BusinessScopeName_SolutionName_DataFlowType_XXX。是不是随便在上百个或者上千个 SSIS Package 中挑出一个，一看 ETL 命名就大概知道这个 ETL 属于哪个业务范围，哪个 Solution，是做文件加载还是文件输出 ？ 这些信息是不是一目了然 ？

Execution Status - 执行状态，ETL 的执行状态，就三种 - 成功，失败和执行中。

Configuration - 所有包的配置都应该集中管理，不应该分散到各个 XML 格式的配置文件中，而应该集中到我这里提到的 Configuration 中。

Process Log - 所有包运行的记录也都应该集中管理，它们的运行记录也都应该集中在一个地方进行管理和跟踪。我见过不同公司不同的项目，每创建一个 ETL 就是一份 XML 配置文件，一个 SSIS 自动生成的 Process Log。当整个项目中就几个 ETL Package 的时候，确实没有什么问题，完成部署也很快，很容易。但是如果能稍微花点时间认真搭建一个属于自己项目的日志系统，所有人共同遵守的话，我可以说后续的开发，维护，新人学习，管理等时间和人力成本将会节省更多。

Error Log - 伴随着 Process Log ，但有所区别，它只记录错误消息。

三. 日志系统的数据库对象

上面的这些角色反映在数据库中就是下面的这些数据库对象了，可以很容易看到它们之间的关系。注意 - SSIS Configurations 这张表不是直接创建的，而是第一次在设计 ETL 模板的时候创建的，后面会介绍到。

详细创建这些对象的脚本也提供给大家使用，在此基础之上可以扩展 Auditing 的工具，包括输入，输出文件的记录，Archive 文件的记录，表记录的更改条数，新增条数，正确率，错误率等记录都可以基于上面的 Process Log 进行扩展。我可以在以后再单独写一篇 Auditing 方面的内容，来介绍如何集成 SSIS 自身 Log 来记录这些 Audit Data。

USE BIWORKLOG
GO

----------------------------------------------------------------------
-- Create BIWORK Log System 
-- by BIWORK at http://www.cnblogs.com/biwork
----------------------------------------------------------------------
IF OBJECT_ID('dbo.ERROR_LOG','U') IS NOT NULL
DROP TABLE dbo.ERROR_LOG
GO

IF OBJECT_ID('dbo.[SSIS CONFIGURATIONS]','U') IS NOT NULL
DROP TABLE dbo.[SSIS CONFIGURATIONS] 
GO

IF OBJECT_ID('dbo.PROCESS_LOG','U') IS NOT NULL
DROP TABLE dbo.PROCESS_LOG
GO

IF OBJECT_ID('dbo.SOLUTION_ETL','U') IS NOT NULL
DROP TABLE dbo.SOLUTION_ETL
GO

IF OBJECT_ID('dbo.SOLUTION','U') IS NOT NULL
DROP TABLE dbo.SOLUTION
GO

IF OBJECT_ID('dbo.BUSINESS_SCOPE','U') IS NOT NULL
DROP TABLE dbo.BUSINESS_SCOPE 
GO

IF OBJECT_ID('dbo.EXECUTE_STATUS','U') IS NOT NULL
DROP TABLE dbo.EXECUTE_STATUS 
GO 

IF OBJECT_ID('dbo.DATA_FLOW_TYPE','U') IS NOT NULL
DROP TABLE dbo.DATA_FLOW_TYPE 
GO 

CREATE TABLE dbo.BUSINESS_SCOPE
(
    SCOPE_ID INT PRIMARY KEY IDENTITY(1,1),
    SCOPE_NAME NVARCHAR(255),
    SCOPE_SHORT_NAME NVARCHAR(10),
    SCOPE_DESC NVARCHAR(255),
    SCOPE_OWNER NVARCHAR(255),
    SCOPE_OWNER_EMAIL NVARCHAR(255) NULL,
    CREATE_USER NVARCHAR(255) NOT NULL,
    CREATE_TIME DATETIME NOT NULL
) 
CREATE TABLE dbo.SOLUTION
(
    SOLUTION_ID INT PRIMARY KEY IDENTITY(1,1),
    SCOPE_ID INT FOREIGN KEY REFERENCES dbo.BUSINESS_SCOPE(SCOPE_ID),
    SOLUTION_NAME NVARCHAR(255) NOT NULL,
    SOLUTION_SHORT_NAME NVARCHAR(10) NOT NULL,
    SOLUTION_DESC NVARCHAR(2000) NULL,
    SOLUTION_OWNER NVARCHAR(255) NULL,
    OWNER_EMAIL NVARCHAR(255) NULL,
    SOLUTION_START DATETIME NULL,
    CREATE_USER NVARCHAR(255) NOT NULL,
    CREATE_TIME DATETIME NOT NULL
) 

CREATE TABLE dbo.DATA_FLOW_TYPE
(
    FLOW_TYPE_ID INT PRIMARY KEY,
    FLOW_TYPE NVARCHAR(10) NOT NULL,
    FLOW_DESC NVARCHAR(255) NOT NULL,
    CREATE_USER NVARCHAR(255) NOT NULL,
    CREATE_TIME DATETIME NOT NULL
)

INSERT INTO dbo.DATA_FLOW_TYPE VALUES
(10,'OUTPUT','To output data from database tables to destination files.',SYSTEM_USER,GETDATE()), 
(11,'INPUT','To load data from files to destination database tables.',SYSTEM_USER,GETDATE()),
(12,'INOUTPUT','To load data from files and output data to files.',SYSTEM_USER,GETDATE()),
(20,'TRANS','Data transformation without files',SYSTEM_USER,GETDATE())

CREATE TABLE dbo.SOLUTION_ETL
(
    ETL_ID INT PRIMARY KEY IDENTITY(1,1),
    SOLUTION_ID INT FOREIGN KEY REFERENCES dbo.SOLUTION(SOLUTION_ID),
    FLOW_TYPE_ID INT FOREIGN KEY REFERENCES dbo.DATA_FLOW_TYPE(FLOW_TYPE_ID),
    ETL_NAME NVARCHAR(255) NOT NULL,
    ETL_PACKAGE_NAME NVARCHAR(255) NOT NULL,
    ETL_DESC NVARCHAR(2000) NULL,
    ETL_FST_OWNER NVARCHAR(255) NOT NULL,
    ETL_FST_OWNER_EMAIL NVARCHAR(255) NOT NULL,
    ETL_SEC_OWNER NVARCHAR(255) NULL,
    ETL_SEC_OWNER_EMAIL NVARCHAR(255) NULL,
    CREATE_USER NVARCHAR(255) NOT NULL,
    CREATE_TIME DATETIME NOT NULL
)

CREATE TABLE dbo.EXECUTE_STATUS
(
    STATUS_ID INT PRIMARY KEY,
    STATUS_DESC NVARCHAR(25)
)

CREATE TABLE dbo.PROCESS_LOG
(
    PROCESS_LOG_ID INT PRIMARY KEY IDENTITY(1,1),
    ETL_ID INT FOREIGN KEY REFERENCES dbo.SOLUTION_ETL(ETL_ID),
    PACKAGE_NAME NVARCHAR(255) NOT NULL,
    MACHINE_NAME NVARCHAR(255) NOT NULL,
    EXECUTE_USER NVARCHAR(255) NOT NULL,
    START_TIME DATETIME NOT NULL,
    FINISH_TIME DATETIME NULL,
    EXECUTE_STATUS_ID INT FOREIGN KEY REFERENCES dbo.EXECUTE_STATUS(STATUS_ID)
)

CREATE TABLE dbo.ERROR_LOG
(
    ERROR_LOG_ID INT PRIMARY KEY IDENTITY(1,1),
    PROCESS_LOG_ID INT FOREIGN KEY REFERENCES dbo.PROCESS_LOG(PROCESS_LOG_ID),
    ERROR_MSG NVARCHAR(MAX) NOT NULL, 
    COMPONENT_NAME NVARCHAR(255) NOT NULL,
    CREATE_TIME DATETIME NOT NULL
)

INSERT INTO dbo.EXECUTE_STATUS VALUES
(-1,'ERROR'),
(0,'IN PROCESS'),
(1,'FINISH')
View Code

插入日志的存储过程 - USP_COMMON_COMBI_INSERT_START_LOG

USE BIWORKLOG
GO

----------------------------------------------------------------------
-- USP_COMMON_COMBI_INSERT_START_LOG 
-- by BIWORK at http://www.cnblogs.com/biwork
----------------------------------------------------------------------
SET NOCOUNT ON

IF OBJECT_ID('USP_COMMON_COMBI_INSERT_START_LOG','P') IS NOT NULL
DROP PROCEDURE USP_COMMON_COMBI_INSERT_START_LOG
GO

CREATE PROCEDURE USP_COMMON_COMBI_INSERT_START_LOG
    @ETL_ID INTEGER,
    @PACKAGE_NAME NVARCHAR(255),
    @MACHINE_NAME NVARCHAR(255),
    @EXECUTE_USER NVARCHAR(255),
    @START_TIME DATETIME,
    @PROCESS_LOG_ID INTEGER OUTPUT
AS
BEGIN
    
    INSERT INTO dbo.PROCESS_LOG
    (
        ETL_ID,
        PACKAGE_NAME,
        MACHINE_NAME,
        EXECUTE_USER,
        START_TIME,
        EXECUTE_STATUS_ID
    )
    VALUES
    (
        @ETL_ID,
        @PACKAGE_NAME,
        @MACHINE_NAME,
        @EXECUTE_USER,
        @START_TIME,
        0 -- IN PROCESS
    )

    SELECT @PROCESS_LOG_ID = @@IDENTITY
END
View Code

更新结束 Log 的存储过程 - USP_COMMON_COMBI_UPDATE_END_LOG

USE BIWORKLOG
GO
----------------------------------------------------------------------
-- USP_COMMON_COMBI_UPDATE_END_LOG 
-- by BIWORK at http://www.cnblogs.com/biwork
----------------------------------------------------------------------
SET NOCOUNT ON

IF OBJECT_ID('USP_COMMON_COMBI_UPDATE_END_LOG','P') IS NOT NULL
DROP PROCEDURE USP_COMMON_COMBI_UPDATE_END_LOG
GO

CREATE PROCEDURE USP_COMMON_COMBI_UPDATE_END_LOG 
    @EXECUTE_STATUS_ID INTEGER,
    @PROCESS_LOG_ID INTEGER
AS
BEGIN
    
    UPDATE dbo.PROCESS_LOG
    SET FINISH_TIME = GETDATE(),
        EXECUTE_STATUS_ID = @EXECUTE_STATUS_ID
    WHERE PROCESS_LOG_ID = @PROCESS_LOG_ID
    
END
View Code

插入错误日志的存储过程 - USP_COMMON_COMBI_INSERT_ERROR_LOG

USE BIWORKLOG
GO
----------------------------------------------------------------------
-- USP_COMMON_COMBI_INSERT_ERROR_LOG
-- by BIWORK at http://www.cnblogs.com/biwork
----------------------------------------------------------------------
SET NOCOUNT ON

IF OBJECT_ID('USP_COMMON_COMBI_INSERT_ERROR_LOG','P') IS NOT NULL
DROP PROCEDURE USP_COMMON_COMBI_INSERT_ERROR_LOG
GO

CREATE PROCEDURE USP_COMMON_COMBI_INSERT_ERROR_LOG
    @PROCESS_LOG_ID INTEGER,
    @ERROR_MESSAGE NVARCHAR(255),
    @COMPONENT_NAME NVARCHAR(255) 
AS
BEGIN
    
    INSERT INTO dbo.ERROR_LOG
    (
        PROCESS_LOG_ID,
        ERROR_MSG,
        COMPONENT_NAME,
        CREATE_TIME
    ) 
    VALUES
    (
        @PROCESS_LOG_ID,
        @ERROR_MESSAGE,
        @COMPONENT_NAME,
        GETDATE()
    )
END
View Code

 

四. 日志系统在 SSIS Package 中的使用

在每个项目开发之初，特别是新项目，按照我们上面提到的内容，我们首先应该就是定义好我们的 Business Scope, Solution, Solution ETL 这些内容。

先后定义:

  1. Business Scope -  COMMON_BIWORK_LOG ，短名称 - COMMON
  2. Solution - COMMON_BI，短名称 - COMBI
  3. ETL - ETL_TEMPLATE， ETL 包的全名即 SSIS Package 的文件名在这里就定义成了  BusinessScope_Solution_DataType_ETL 全名即 -  COMMON_COMBI_TRANS_ETL_TEMPLATE

那么一看这个包的名称，即使是一个新人，经过简单的项目框架培训，是不是一看这个包的名称就基本知道这个包是用来做什么的了。 下面这个脚本，应该在项目开发过程之前来执行，缺什么就定义什么。

USE BIWORKLOG
GO

----------------------------------------------------------------------
-- Create BIWORK Log System 
-- by BIWORK at http://www.cnblogs.com/biwork
----------------------------------------------------------------------
DECLARE @BUSINESS_SCOPE_ID INT
DECLARE @SOLUTION_ID INT
DECLARE @SOLUTION_ETL_ID INT

-- Set the BUSINESS SCOPE ID
IF NOT EXISTS(
                SELECT SCOPE_ID
                FROM dbo.BUSINESS_SCOPE 
                WHERE SCOPE_NAME = 'COMMON_BIWORK_LOG'
             )
BEGIN
    INSERT INTO dbo.BUSINESS_SCOPE
    (
        SCOPE_NAME,
        SCOPE_SHORT_NAME,
        SCOPE_DESC,
        SCOPE_OWNER,
        SCOPE_OWNER_EMAIL,
        CREATE_USER,
        CREATE_TIME
    )VALUES
    (
        'COMMON_BIWORK_LOG',
        'COMMON',
        'Common BIWORK ETL log system',
        'BIWORK',
        'biwork@126.com',
        SYSTEM_USER,
        GETDATE()
    )  
END

SELECT @BUSINESS_SCOPE_ID = SCOPE_ID
FROM dbo.BUSINESS_SCOPE 
WHERE SCOPE_NAME = 'COMMON_BIWORK_LOG'

-- Set the SOLUTION ID
IF NOT EXISTS(
                SELECT SOLUTION_ID
                FROM dbo.SOLUTION 
                WHERE SOLUTION_NAME = 'COMMON_BI'
             ) 
BEGIN
    INSERT INTO dbo.SOLUTION 
    (
        SCOPE_ID,
        SOLUTION_NAME,
        SOLUTION_SHORT_NAME,
        SOLUTION_DESC,
        SOLUTION_OWNER,
        OWNER_EMAIL,
        SOLUTION_START,
        CREATE_USER,
        CREATE_TIME
    )
    VALUES
    (
        @BUSINESS_SCOPE_ID,
        'COMMON_BI',
        'COMBI',
        'ETL log framework record the execution information and error message for SSIS packages',
        'BIWORK',
        'biwork@126.com',
        GETDATE(),
        SYSTEM_USER,
        GETDATE()
    )
END

SELECT @SOLUTION_ID = SOLUTION_ID
FROM dbo.SOLUTION 
WHERE SOLUTION_NAME = 'COMMON_BI'

-- Set the SOLUTION ETL ID
IF NOT EXISTS (
                 SELECT ETL_ID
                 FROM dbo.SOLUTION_ETL 
                 WHERE ETL_NAME = 'ETL_TEMPLATE'
              )
BEGIN
    INSERT INTO dbo.SOLUTION_ETL
    (
        SOLUTION_ID,
        FLOW_TYPE_ID,
        ETL_NAME,
        ETL_PACKAGE_NAME,
        ETL_DESC,
        ETL_FST_OWNER,
        ETL_FST_OWNER_EMAIL,
        ETL_SEC_OWNER,
        ETL_SEC_OWNER_EMAIL,
        CREATE_USER,
        CREATE_TIME
    )
    VALUES
    (
        @SOLUTION_ID,
        20, -- Flow type is common data transformation
        'ETL_TEMPLATE',
        -- SSIS Package naming rule  [SCOPE_SHORT_NAME]_[SOLUTION_SHORT_NAME]_[FLOW_TYPE]_[ETL_NAME]
        'COMMON_COMBI_TRANS_ETL_TEMPLATE',
        'ETL log template package',
        'BIWORK',
        'biwork@126.com',
        NULL,
        NULL,
        SYSTEM_USER,
        GETDATE()
    )
END
View Code

有了这些信息，我们就可以开始设计和开发我们的 SSIS Package 了。

解决方案名称可以使用 Business Scope 的名称 COMMON_BIWORK_LOG，项目名称就叫做 COMMON_BI。

ETL 名称就是 COMMON_COMBI_TRANS_ETL_TEMPLATE。

先在包级别定义好这些变量并赋予一定的值，后面我会详细解释到它们各自的用途。

在 Control Flow 中创建这两个 Execute SQL Task (EST) - EST_INSERT_START_LOG

EST_UPDATE_END_LOG

保存并运行 SSIS Package ，并可以到 Process Log 表中查看结果。

前两条是之前我自己测试的日志，第三条可以看到它的日志信息，最主要的就是 START TIME，FINISH TIME 以及 EXECUTE STATUS，1 表示执行成功。

还有错误处理，对于错误的处理应该在 SSIS Package 的 Event Handler 中来完成，并且选择的是 OnError。 OnError 将捕获所有出错的事件，那么我们就会做两件事情。第一件就是在 PROCESS_LOG 中将对应的 PROCESS 状态从 0 改成 - 1，即表示失败。第二件事情即写入错误消息到错误日志中。

EST_UPDATE_END_LOG

 

 EST_INSERT_ERROR_LOG

 

在控制流中添加一个错误的 Execute SQL Task来测试一下 -

保存并执行 PACKAGE，出现错误并被 ON ERROR 捕获。

Event Handlers 中的 OnError 事件。

查看数据库中的日志和错误记录。

那么这样的一个小型日志框架就算搭建起来了，之后所有的 SSIS Package 都可以使用到这套框架。执行 SSIS Package 时，所有的日志，错误日志都会集中写到同一个 Log System 中。这套日志系统虽然看起来功能很简单，但是能够实实在在的支持上百个 ETL Package 的日志管理。不会出现 100 个 Package 有 100 套不同日志表的情况，极大的改善了我们维护，监控和管理 ETL 的过程。

同时，基于这个框架之上来开发我们的其它 Task 组件，每次出错的时候不需要再次打开 Execution Results 去找 Error Information。倘若一个 Package 有几十个 Task，这个执行的记录将会非常长，查错误很麻烦。而现在只需要去查询一下 Error Log 就可以了，可以非常快的找到在哪个 Component 出现的什么错误。

SSIS Package 配置管理在日志系统中的集成

还记得前面的几个变量吗？

PC - Package Configuration - 也就是说这是 Package 配置级别的，会在 Package 配置中完成。

PE - Package Environment - Package 在系统环境中的配置。

PV - Package Variable - 无需配置，只是在 ETL Package 执行过程中使用到。

比较常见的 SSIS Package Configuration 往往会选择 XML Configuration File 来完成。当然现在在 SQL Server 2012 版本 BIDS 工具里已经不需要任何的 XML Configuration File 就可以完成配置了，非常容易。但是，在 JOB 的配置过程中还是需要提供配置的值。而我希望的是，所有的配置都能够集中在一张数据表中完成，也就是即使以后有几十个，上百个 SSIS Package 它们的配置也都集中在一张表中完成的。不需要 XML 配置文件，不需要在 JOB 定义 Package 时配置任何参数 - 一旦发布，在外无参数配置！首先，在这里要搞清楚一个概念 - BIWORKLOG 是核心日志数据库，这个数据库部署的位置先要确定好，应该部署在 JOB 运行时的 SERVER。在系统环境变量中定义好 COMMON BIWORKLOG 数据库所在 SERVER 的名称，数据库的名称。

添加一个环境变量配置，选中 COMMON_ETL_LOG_SERVER

将这个环境变量的值赋值于 PE_COMMON_SERVER_NAME, 注意红色框内的值明显是我赋值错了，在环境变量中它的值是 LOCALHOST。 那什么时候 PE_COMMON_SERVER_NAME 将获取环境变量的值呢？ - 在 SSIS Package 运行的时候。当 SSIS Package 运行的时候，包配置将首先完成包配置中各个值的装配，此时的 PE_COMMON_SERVER_NAME 将接受系统环境变量 COMMON_ETL_LOG_SERVER 的值即 LOCALHOST。

同样的道理，配置 PE_COMMON_DATABASE_NAME。

这是这两个配置好的系统环境变量。

配置好了这两个系统环境变量之后，我们再来更改 Connection Manager 下的 CM_DB_BIWORK 的 Expression。

这样一来，在 SSIS Package 运行的时候，两个 PE 变量将读取系统环境变量的值，然后将两个系统环境变量的值成功配置到了 BIWORLOG 日志数据库的连接源了。

第一个配置就算完成了！记得，在完成下面操作的时候一定要重启一下电脑，因为环境变量的配置有时需要重启之后才能生效！

再来看 PC_SOLUTION_ETL_ID 是怎么回事？

每次往 PROCESS_LOG 插入日志的时候就需要提供是哪个 ETL 运行的标志，我们应该这样来获取 SOLUTION_ETL_ID。

包括以后，如果我们要使用到另外的数据源，比如数据库连接对象等等如何配置呢？ 像这些配置选项也可以集中配置在一张表中来进行管理。

仍然打开 Package Configuration，然后在 Configuration Type 中选择 SQL Server。

Configuration Table 这时选择 NEW , 它将自动提供创建 SQL Server 配置表的代码，可以修改一下表名称。

看到这个界面就知道这个表已经在 CM_DB_BIWORKLOG 连接管理器中所表示的 BIWORKLOG 数据库中准备创建了。

那么以后所有的包配置信息都可以配置在这一张表中，通过什么区分是哪一个包的配置呢？ 通过 COMMON_COMBI_TRANS_ETL_TEMPLATE 这个包的名称来区分，这个值是需要手工写上去的。

选择将 PC_SOLUTION_ETL_ID 的 Value 给配置到 SQL Server 表中，这时需要注意良好的命名规范可以让你快速的知道哪些值是需要被配置的。

保存这个配置。

查看数据库中的配置，这里只有一个 Configuration Filter，以后每来一个 SSIS Package 就会在这里出现一个 Configuration Filter，配置上百个甚至上千个 ETL 包完全没有问题。

如果需要更新某一个具体的值的话，需要 Update 就应该这样来更新，再次强调 - BIWORKLOG 日志系统的读写权限一定是非常非常高的。

UPDATE [dbo].[SSIS CONFIGURATIONS]
SET ConfiguredValue = (
                        SELECT ETL_ID 
                        FROM dbo.SOLUTION_ETL 
                        WHERE ETL_PACKAGE_NAME = 'COMMON_COMBI_TRANS_ETL_TEMPLATE'
                      )
WHERE ConfigurationFilter = 'COMMON_COMBI_TRANS_ETL_TEMPLATE'
AND PackagePath = '\Package.Variables[User::PC_SOLUTION_ETL_ID].Properties[Value]'

SELECT * FROM [dbo].[SSIS CONFIGURATIONS]

再次回顾我们所有的配置过程 -

PE 和 PC 在这里的值都是不起到任何作用的。

PE 的值来自于系统环境变量，BIWORKLOG 数据库的 SERVER 地址和 DATABASE NAME 都是由这两个 PE 来赋值的。

PC 的值来自于 BIWORKLOG 中的 SSIS CONFIGURATIONS 表，PC_SOLUTION_ETL_ID 在插入日志和更新日志的时候会反复用到，它标识了当前 ETL 的ID。

也就是说没有 PE 就不会有 BIWORKLOG 的成功连接，没有 BIWORKLOG 就不会有 PC 在数据库中的配置，没有 PC 就不会有 Log 日志的插入。

这个配置链一定要理解清楚，我们所有的配置值就存在两个地方，一个是系统环境变量，这个值需要配置一次以后就不用配了。

以后所有 Package 级别的变量配置就都有 SSIS CONFIGURATIONS 表来维护，通过 Configuration Filter 来过滤。

这就是这套日志框架为什么可以支持成百上千个 SSIS 日志和包配置的原因。

配置完毕后，再次运行 Package ，这时所有变量的值都不是在定义它们时候的值了，而是真正读取于环境变量和 SSIS CONFIGURATIONS 表。

六. SSIS Package 模板开发

其实在我的这篇文章 - SSIS 系列 - 利用 SSIS 模板快速开发 SSIS Package 中已经提到了如何使用 SSIS Package 模板快速开发。

下面就使用上面的模板来演示实际运用，并在模板基础之上来完成一个 SSIS Pakcage 的开发。

找到我们上面创建的 Package。

 

我使用的是 Windows Server 2008 R2 操作系统，安装的是 SQL Server 2008 R2 数据库，默认的环境应该是 -

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

把包拷贝到这里。其它环境请参考 - SSIS 系列 - 利用 SSIS 模板快速开发 SSIS Package

重启一下 BIDS 开发工具，然后就可以使用这个模板了，我将使用这个模板开发一个小 SSIS Package。

一定要注意，是选择项目名称右键添加 - New Item，看到了这个模板了吗？

不要着急填写名称，而应该参照下面规范代码 -

由于这个 SSIS Package 和 COMMON_COMBI_TRANS_ETL_TEMPLATE 属于同一个 SOLUTION，所以 SOLUTION 是已知的 1，因此。

 

-- Set the SOLUTION ETL ID
IF NOT EXISTS (
                 SELECT ETL_ID
                 FROM dbo.SOLUTION_ETL 
                 WHERE ETL_NAME = 'ETL_TEMPLATE_TEST'
              )
BEGIN
    INSERT INTO dbo.SOLUTION_ETL
    (
        SOLUTION_ID,
        FLOW_TYPE_ID,
        ETL_NAME,
        ETL_PACKAGE_NAME,
        ETL_DESC,
        ETL_FST_OWNER,
        ETL_FST_OWNER_EMAIL,
        ETL_SEC_OWNER,
        ETL_SEC_OWNER_EMAIL,
        CREATE_USER,
        CREATE_TIME
    )
    VALUES
    (
        1, -- SOLUTION ID
        20, -- Flow type is common data transformation
        'ETL_TEMPLATE_TEST',
        -- SSIS Package naming rule  [SCOPE_SHORT_NAME]_[SOLUTION_SHORT_NAME]_[FLOW_TYPE]_[ETL_NAME]
        'COMMON_COMBI_TRANS_ETL_TEMPLATE_TEST',
        'ETL log template package',
        'BIWORK',
        'biwork@126.com',
        NULL,
        NULL,
        SYSTEM_USER,
        GETDATE()
    )
END

 

按照这个名称创建包，但注意不要急着运行。因为很显然，PC_SOLUTION_ID 在上面显示的为 2，并且在 SSIS CONFIGURATIONS 表中的 Configuration Filter 也不一样。

先将 PC_SOLUTION_ETL_ID 的值设置为2，这样一会修改 Package Configuration 的时候，在第一次创建新的 Configuration Filter 的时候就会将这个值写到数据库中。

修改 Package SQLSERVER CONFIG, 这里的 Configuration Filter 将改成新的包名 COMMON_COMBI_TRANS_ETL_TEMPLATE_TEST 。

保存后查看数据库表 SSIS CONFIGURATIONS，这个配置就完成了。

如果后面还需要任何的变量配置，都可以通过这种方式集中配置在这张表中，这样我们的变量就不需要配置到 XML 文件中，一来是不方便检查配置的内容，二来当 ETL 数量增多的时候， XML 配置文件的管理和配置稍不注意也容易出现混乱。

七. 日志系统的报表开发

 

有了上面的这些表对象和日志记录，其实很容易开发出一些常用的日志报表，并且自定义的功能更完善更细致。

下面我随便写两两张报表，但是实际上可做的报表，实用的报表很多。

如果再回头看看文本开头时的几个问题的话，我们是不是完全开发一些报表来回答？

  1. 首先，在这里提出几个问题，可以试着回答一下？
  2. 假设我们项目中，一个项目中最终上线的 ETL 包多达上百个，如何对这些包进行统一的日志管理 ？
  3. 现在在线运行的 ETL 包有多少个？ 多长时间？ 哪些包的运行时间最长，哪些最少 ？ 项目经理需要一份图表能够反映出这些 KPI 来。
  4. 每天运行的 ETL 包有多少个？ 测试环境，开发环境上都跑了多少个 ?
  5. 如何快速查询每个包运行的状态，成功否，失败否，失败的原因等等 ？
  6. 每个包都有一个配置文件，几百个包的配置文件又是如何进行管理的 ？ 这些配置文件中都有些什么内容 ？现在配置的参数都各自是什么？
  7. 这些包各自大概属于哪些部门使用的？业务范围是什么？ 这些包失败了找谁？ 谁开发的？
  8. 哪些包是用来加载文件，输出文件的，哪些包只是用来一般的数据转换的 ？ 输入文件在哪里 ？ 输出文件在哪里 ？
  9. 随便给出一个 SSIS Package 的名称，你知道它大概是做什么的吗？
  10. 每周的项目会议中，考虑过没有拿出上面的这些答案，数据，图表来对付各个老大们的提问 ？
  11. .........

实际上基于上面的日志系统还有很多东西可以做，特别是 AUDITING 的内容可以扩展的非常丰富。这一部分可以集成到上述的 Log System 中，比如记录文件的输入，输出位置，导入导出的记录条数；表记录的增长条数，修改条数等等，这一切都可以集成起来配置 Log System 来使用。

Logging System + Auditing System 构成一个完整的维护和管理 ETL 包的运行记录的框架体系，对于我们开发，测试，以及线上的维护，调优等工作都是非常非常重要的。

关于 Auditing 的部分下次再慢慢写。

注意的地方

1. 最后一定要强调，COMMON BIWORK LOG 一定要控制好权限，只能允许少数人对核心配置表进行修改，他人只能进行查询，并同时应做好备份工作。 

2. 系统环境变量的配置会要求服务器重启，这个在很多已上线的环境可能很难做到，因此可以灵活改变。 在 SQL Server 2008 环境下，可以使用 XML Package Configuration。在 2010 、2012 开发环境下，可以使用 Package Parameter 来进行配置，也非常方便和灵活。

3. 不支持子父 Package，如果需要支持子父 Package 的调用，可以自行修改表结构，在 PROCESS_LOG 中加上一列 PARENT_LOG_ID 然后再修改相应的存储过程。

总结

这篇文章一字一字敲从晚上8点敲到现在将近 6 个钟头，我相信我已经尽量做到把一些关键点交待清楚，相信只要认真看下来是非常容易理解的。

如果跟着这篇文章做下来，超不过30分钟就可以搭建一个小的 BIWORKLOG 框架，可以根据实际项目需要大家自行扩展和丰富功能。

关于能不能统一管理上百个的 ETL的日志系统 ，我可以肯定的回答：没有问题，因为它本身只是用来记录日志的地方。并且统一管理的好处就是自上而下规范了所有 ETL 的设计，包括命名规范和包配置。我见过有上百个 SSIS Package 三年以来还管理很好的 ETL 项目，也见过只有几十个 ETL 但日志，配置非常混乱的项目。

希望这套小框架对大家有所帮助！欢迎多多交流！

与这篇文章可搭配的解决方案

如何管理和记录 SSIS 各个 Task 的开始执行时间和结束时间以及 Task 中添加|删除|修改的记录数 

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)    如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。