ETL项目中管理上百个SSIS包的日志和包配置框架

一直准备写这么一篇有关 SSIS 日志系统的文章，但是发现很难一次写的很完整。因为这篇文章的内容可扩展的性太强，每多扩展一部分就意味着需要更多代码，示例和理论支撑。因此，我选择我觉得比较通用的 LOG 部分，在这里分享一下给大家，希望对大家在设计 ETL 的日志系统时有所启发和帮助。

建网站原本是网站策划师、网络程序员、网页设计师等，应用各种网络程序开发技术和网页设计技术配合操作的协同工作。创新互联专业提供成都网站设计、做网站、成都外贸网站建设公司,网页设计,网站制作(企业站、响应式网站建设、电商门户网站)等服务,从网站深度策划、搜索引擎友好度优化到用户体验的提升,我们力求做到极致!

当然在这里要区分 Logging 和 Auditing 的区别，Logging 主要用来记录发生了什么事情，Auditing 侧重描述过程中产生的数据量，新增了多少，修改了多少等记录条数。

本文主要讲解 Log 部分，以后有时间再来讲解如何在 Log System 中集成 Auditing 的功能。

首先，在这里提出几个问题，可以试着回答一下？

1. 假设我们项目中，一个项目中最终上线的 ETL 包多达上百个，如何对这些包进行统一的日志管理 ？

2. 现在在线运行的 ETL 包有多少个？ 多长时间？ 哪些包的运行时间最长，哪些最少 ？ 项目经理需要一份图表能够反映出这些 KPI 来。

3. 每天运行的 ETL 包有多少个？ 测试环境，开发环境上都跑了多少个 ?

4. 如何快速查询每个包运行的状态，成功否，失败否，失败的原因等等 ？

5. 每个包都有一个配置文件，几百个包的配置文件又是如何进行管理的 ？ 这些配置文件中都有些什么内容 ？现在配置的参数都各自是什么？

6. 这些包各自大概属于哪些部门使用的？业务范围是什么？ 这些包失败了找谁？ 谁开发的？

7. 哪些包是用来加载文件，输出文件的，哪些包只是用来一般的数据转换的 ？ 输入文件在哪里 ？ 输出文件在哪里 ？

8. 随便给出一个 SSIS Package 的名称，你知道它大概是做什么的吗？

9. 每周的项目会议中，考虑过没有拿出上面的这些答案，数据，图表来对付各个老大们的提问 ？

.........

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

 
 
    
  
  
  1. USE BIWORKLOG 
    2. 
  
  
  2. GO 
    3. 
  
  
  3. 
  
  
  4. ---------------------------------------------------------------------- 
    5. 
  
  
  5. -- Create BIWORK Log System  
    6. 
  
  
  6. -- by BIWORK at http://www.cnblogs.com/biwork 
    7. 
  
  
  7. ---------------------------------------------------------------------- 
    8. 
  
  
  8. IF OBJECT_ID('dbo.ERROR_LOG','U') IS NOT NULL 
    9. 
  
  
  9. DROP TABLE dbo.ERROR_LOG 
    10. 
  
  
  10. GO 
    11. 
  
  
  11. 
  
  
  12. IF OBJECT_ID('dbo.[SSIS CONFIGURATIONS]','U') IS NOT NULL 
    13. 
  
  
  13. DROP TABLE dbo.[SSIS CONFIGURATIONS]  
    14. 
  
  
  14. GO 
    15. 
  
  
  15. 
  
  
  16. IF OBJECT_ID('dbo.PROCESS_LOG','U') IS NOT NULL 
    17. 
  
  
  17. DROP TABLE dbo.PROCESS_LOG 
    18. 
  
  
  18. GO 
    19. 
  
  
  19. 
  
  
  20. IF OBJECT_ID('dbo.SOLUTION_ETL','U') IS NOT NULL 
    21. 
  
  
  21. DROP TABLE dbo.SOLUTION_ETL 
    22. 
  
  
  22. GO 
    23. 
  
  
  23. 
  
  
  24. IF OBJECT_ID('dbo.SOLUTION','U') IS NOT NULL 
    25. 
  
  
  25. DROP TABLE dbo.SOLUTION 
    26. 
  
  
  26. GO 
    27. 
  
  
  27. 
  
  
  28. IF OBJECT_ID('dbo.BUSINESS_SCOPE','U') IS NOT NULL 
    29. 
  
  
  29. DROP TABLE dbo.BUSINESS_SCOPE  
    30. 
  
  
  30. GO 
    31. 
  
  
  31. 
  
  
  32. IF OBJECT_ID('dbo.EXECUTE_STATUS','U') IS NOT NULL 
    33. 
  
  
  33. DROP TABLE dbo.EXECUTE_STATUS  
    34. 
  
  
  34. GO  
    35. 
  
  
  35. 
  
  
  36. IF OBJECT_ID('dbo.DATA_FLOW_TYPE','U') IS NOT NULL 
    37. 
  
  
  37. DROP TABLE dbo.DATA_FLOW_TYPE  
    38. 
  
  
  38. GO  
    39. 
  
  
  39. 
  
  
  40. CREATE TABLE dbo.BUSINESS_SCOPE 
    41. 
  
  
  41. ( 
    42. 
  
  
  42.     SCOPE_ID INT PRIMARY KEY IDENTITY(1,1), 
    43. 
  
  
  43.     SCOPE_NAME NVARCHAR(255), 
    44. 
  
  
  44.     SCOPE_SHORT_NAME NVARCHAR(10), 
    45. 
  
  
  45.     SCOPE_DESC NVARCHAR(255), 
    46. 
  
  
  46.     SCOPE_OWNER NVARCHAR(255), 
    47. 
  
  
  47.     SCOPE_OWNER_EMAIL NVARCHAR(255) NULL, 
    48. 
  
  
  48.     CREATE_USER NVARCHAR(255) NOT NULL, 
    49. 
  
  
  49.     CREATE_TIME DATETIME NOT NULL 
    50. 
  
  
  50. )  
    51. 
  
  
  51. CREATE TABLE dbo.SOLUTION 
    52. 
  
  
  52. ( 
    53. 
  
  
  53.     SOLUTION_ID INT PRIMARY KEY IDENTITY(1,1), 
    54. 
  
  
  54.     SCOPE_ID INT FOREIGN KEY REFERENCES dbo.BUSINESS_SCOPE(SCOPE_ID), 
    55. 
  
  
  55.     SOLUTION_NAME NVARCHAR(255) NOT NULL, 
    56. 
  
  
  56.     SOLUTION_SHORT_NAME NVARCHAR(10) NOT NULL, 
    57. 
  
  
  57.     SOLUTION_DESC NVARCHAR(2000) NULL, 
    58. 
  
  
  58.     SOLUTION_OWNER NVARCHAR(255) NULL, 
    59. 
  
  
  59.     OWNER_EMAIL NVARCHAR(255) NULL, 
    60. 
  
  
  60.     SOLUTION_START DATETIME NULL, 
    61. 
  
  
  61.     CREATE_USER NVARCHAR(255) NOT NULL, 
    62. 
  
  
  62.     CREATE_TIME DATETIME NOT NULL 
    63. 
  
  
  63. )  
    64. 
  
  
  64. 
  
  
  65. CREATE TABLE dbo.DATA_FLOW_TYPE 
    66. 
  
  
  66. ( 
    67. 
  
  
  67.     FLOW_TYPE_ID INT PRIMARY KEY, 
    68. 
  
  
  68.     FLOW_TYPE NVARCHAR(10) NOT NULL, 
    69. 
  
  
  69.     FLOW_DESC NVARCHAR(255) NOT NULL, 
    70. 
  
  
  70.     CREATE_USER NVARCHAR(255) NOT NULL, 
    71. 
  
  
  71.     CREATE_TIME DATETIME NOT NULL 
    72. 
  
  
  72. ) 
    73. 
  
  
  73. 
  
  
  74. INSERT INTO dbo.DATA_FLOW_TYPE VALUES 
    75. 
  
  
  75. (10,'OUTPUT','To output data from database tables to destination files.',SYSTEM_USER,GETDATE()),  
    76. 
  
  
  76. (11,'INPUT','To load data from files to destination database tables.',SYSTEM_USER,GETDATE()), 
    77. 
  
  
  77. (12,'INOUTPUT','To load data from files and output data to files.',SYSTEM_USER,GETDATE()), 
    78. 
  
  
  78. (20,'TRANS','Data transformation without files',SYSTEM_USER,GETDATE()) 
    79. 
  
  
  79. 
  
  
  80. CREATE TABLE dbo.SOLUTION_ETL 
    81. 
  
  
  81. ( 
    82. 
  
  
  82.     ETL_ID INT PRIMARY KEY IDENTITY(1,1), 
    83. 
  
  
  83.     SOLUTION_ID INT FOREIGN KEY REFERENCES dbo.SOLUTION(SOLUTION_ID), 
    84. 
  
  
  84.     FLOW_TYPE_ID INT FOREIGN KEY REFERENCES dbo.DATA_FLOW_TYPE(FLOW_TYPE_ID), 
    85. 
  
  
  85.     ETL_NAME NVARCHAR(255) NOT NULL, 
    86. 
  
  
  86.     ETL_PACKAGE_NAME NVARCHAR(255) NOT NULL, 
    87. 
  
  
  87.     ETL_DESC NVARCHAR(2000) NULL, 
    88. 
  
  
  88.     ETL_FST_OWNER NVARCHAR(255) NOT NULL, 
    89. 
  
  
  89.     ETL_FST_OWNER_EMAIL NVARCHAR(255) NOT NULL, 
    90. 
  
  
  90.     ETL_SEC_OWNER NVARCHAR(255) NULL, 
    91. 
  
  
  91.     ETL_SEC_OWNER_EMAIL NVARCHAR(255) NULL, 
    92. 
  
  
  92.     CREATE_USER NVARCHAR(255) NOT NULL, 
    93. 
  
  
  93.     CREATE_TIME DATETIME NOT NULL 
    94. 
  
  
  94. ) 
    95. 
  
  
  95. 
  
  
  96. CREATE TABLE dbo.EXECUTE_STATUS 
    97. 
  
  
  97. ( 
    98. 
  
  
  98.     STATUS_ID INT PRIMARY KEY, 
    99. 
  
  
  99.     STATUS_DESC NVARCHAR(25) 
    100. 
  
  
  100. ) 
    101. 
  
  
  101. 
  
  
  102. CREATE TABLE dbo.PROCESS_LOG 
    103. 
  
  
  103. ( 
    104. 
  
  
  104.     PROCESS_LOG_ID INT PRIMARY KEY IDENTITY(1,1), 
    105. 
  
  
  105.     ETL_ID INT FOREIGN KEY REFERENCES dbo.SOLUTION_ETL(ETL_ID), 
    106. 
  
  
  106.     PACKAGE_NAME NVARCHAR(255) NOT NULL, 
    107. 
  
  
  107.     MACHINE_NAME NVARCHAR(255) NOT NULL, 
    108. 
  
  
  108.     EXECUTE_USER NVARCHAR(255) NOT NULL, 
    109. 
  
  
  109.     START_TIME DATETIME NOT NULL, 
    110. 
  
  
  110.     FINISH_TIME DATETIME NULL, 
    111. 
  
  
  111.     EXECUTE_STATUS_ID INT FOREIGN KEY REFERENCES dbo.EXECUTE_STATUS(STATUS_ID) 
    112. 
  
  
  112. ) 
    113. 
  
  
  113. 
  
  
  114. CREATE TABLE dbo.ERROR_LOG 
    115. 
  
  
  115. ( 
    116. 
  
  
  116.     ERROR_LOG_ID INT PRIMARY KEY IDENTITY(1,1), 
    117. 
  
  
  117.     PROCESS_LOG_ID INT FOREIGN KEY REFERENCES dbo.PROCESS_LOG(PROCESS_LOG_ID), 
    118. 
  
  
  118.     ERROR_MSG NVARCHAR(MAX) NOT NULL,  
    119. 
  
  
  119.     COMPONENT_NAME NVARCHAR(255) NOT NULL, 
    120. 
  
  
  120.     CREATE_TIME DATETIME NOT NULL 
    121. 
  
  
  121. ) 
    122. 
  
  
  122. 
  
  
  123. INSERT INTO dbo.EXECUTE_STATUS VALUES 
    124. 
  
  
  124. (-1,'ERROR'), 
    125. 
  
  
  125. (0,'IN PROCESS'), 
    126. 
  
  
  126. (1,'FINISH')
    127.

插入日志的存储过程 - USP_COMMON_COMBI_INSERT_START_LOG

 
 
    
  
  
  1. USE BIWORKLOG 
    2. 
  
  
  2. GO 
    3. 
  
  
  3. 
  
  
  4. ---------------------------------------------------------------------- 
    5. 
  
  
  5. -- USP_COMMON_COMBI_INSERT_START_LOG  
    6. 
  
  
  6. -- by BIWORK at http://www.cnblogs.com/biwork 
    7. 
  
  
  7. ---------------------------------------------------------------------- 
    8. 
  
  
  8. SET NOCOUNT ON 
    9. 
  
  
  9. 
  
  
  10. IF OBJECT_ID('USP_COMMON_COMBI_INSERT_START_LOG','P') IS NOT NULL 
    11. 
  
  
  11. DROP PROCEDURE USP_COMMON_COMBI_INSERT_START_LOG 
    12. 
  
  
  12. GO 
    13. 
  
  
  13. 
  
  
  14. CREATE PROCEDURE USP_COMMON_COMBI_INSERT_START_LOG 
    15. 
  
  
  15.     @ETL_ID INTEGER, 
    16. 
  
  
  16.     @PACKAGE_NAME NVARCHAR(255), 
    17. 
  
  
  17.     @MACHINE_NAME NVARCHAR(255), 
    18. 
  
  
  18.     @EXECUTE_USER NVARCHAR(255), 
    19. 
  
  
  19.     @START_TIME DATETIME, 
    20. 
  
  
  20.     @PROCESS_LOG_ID INTEGER OUTPUT 
    21. 
  
  
  21. AS 
    22. 
  
  
  22. BEGIN 
    23. 
  
  
  23.      
    24. 
  
  
  24.     INSERT INTO dbo.PROCESS_LOG 
    25. 
  
  
  25.     ( 
    26. 
  
  
  26.         ETL_ID, 
    27. 
  
  
  27.         PACKAGE_NAME, 
    28. 
  
  
  28.         MACHINE_NAME, 
    29. 
  
  
  29.         EXECUTE_USER, 
    30. 
  
  
  30.         START_TIME, 
    31. 
  
  
  31.         EXECUTE_STATUS_ID 
    32. 
  
  
  32.     ) 
    33. 
  
  
  33.     VALUES 
    34. 
  
  
  34.     ( 
    35. 
  
  
  35.         @ETL_ID, 
    36. 
  
  
  36.         @PACKAGE_NAME, 
    37. 
  
  
  37.         @MACHINE_NAME, 
    38. 
  
  
  38.         @EXECUTE_USER, 
    39. 
  
  
  39.         @START_TIME, 
    40. 
  
  
  40. -- IN PROCESS 
    41. 
  
  
  41.     ) 
    42. 
  
  
  42. 
  
  
  43.     SELECT @PROCESS_LOG_ID = @@IDENTITY 
    44. 
  
  
  44. END
    45.

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

 
 
    
  
  
  1. USE BIWORKLOG 
    2. 
  
  
  2. GO 
    3. 
  
  
  3. ---------------------------------------------------------------------- 
    4. 
  
  
  4. -- USP_COMMON_COMBI_UPDATE_END_LOG  
    5. 
  
  
  5. -- by BIWORK at http://www.cnblogs.com/biwork 
    6. 
  
  
  6. ---------------------------------------------------------------------- 
    7. 
  
  
  7. SET NOCOUNT ON 
    8. 
  
  
  8. 
  
  
  9. IF OBJECT_ID('USP_COMMON_COMBI_UPDATE_END_LOG','P') IS NOT NULL 
    10. 
  
  
  10. DROP PROCEDURE USP_COMMON_COMBI_UPDATE_END_LOG 
    11. 
  
  
  11. GO 
    12. 
  
  
  12. 
  
  
  13. CREATE PROCEDURE USP_COMMON_COMBI_UPDATE_END_LOG  
    14. 
  
  
  14.     @EXECUTE_STATUS_ID INTEGER, 
    15. 
  
  
  15.     @PROCESS_LOG_ID INTEGER 
    16. 
  
  
  16. AS 
    17. 
  
  
  17. BEGIN 
    18. 
  
  
  18.      
    19. 
  
  
  19.     UPDATE dbo.PROCESS_LOG 
    20. 
  
  
  20.     SET FINISH_TIME = GETDATE(), 
    21. 
  
  
  21.         EXECUTE_STATUS_ID = @EXECUTE_STATUS_ID 
    22. 
  
  
  22.     WHERE PROCESS_LOG_ID = @PROCESS_LOG_ID 
    23. 
  
  
  23.      
    24. 
  
  
  24. END
    25.

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

 
 
    
  
  
  1. USE BIWORKLOG 
    2. 
  
  
  2. GO 
    3. 
  
  
  3. ---------------------------------------------------------------------- 
    4. 
  
  
  4. -- USP_COMMON_COMBI_INSERT_ERROR_LOG 
    5. 
  
  
  5. -- by BIWORK at http://www.cnblogs.com/biwork 
    6. 
  
  
  6. ---------------------------------------------------------------------- 
    7. 
  
  
  7. SET NOCOUNT ON 
    8. 
  
  
  8. 
  
  
  9. IF OBJECT_ID('USP_COMMON_COMBI_INSERT_ERROR_LOG','P') IS NOT NULL 
    10. 
  
  
  10. DROP PROCEDURE USP_COMMON_COMBI_INSERT_ERROR_LOG 
    11. 
  
  
  11. GO 
    12. 
  
  
  12. 
  
  
  13. CREATE PROCEDURE USP_COMMON_COMBI_INSERT_ERROR_LOG 
    14. 
  
  
  14.     @PROCESS_LOG_ID INTEGER, 
    15. 
  
  
  15.     @ERROR_MESSAGE NVARCHAR(255), 
    16. 
  
  
  16.     @COMPONENT_NAME NVARCHAR(255)  
    17. 
  
  
  17. AS 
    18. 
  
  
  18. BEGIN 
    19. 
  
  
  19.      
    20. 
  
  
  20.     INSERT INTO dbo.ERROR_LOG 
    21. 
  
  
  21.     ( 
    22. 
  
  
  22.         PROCESS_LOG_ID, 
    23. 
  
  
  23.         ERROR_MSG, 
    24. 
  
  
  24.         COMPONENT_NAME, 
    25. 
  
  
  25.         CREATE_TIME 
    26. 
  
  
  26.     )  
    27. 
  
  
  27.     VALUES 
    28. 
  
  
  28.     ( 
    29. 
  
  
  29.         @PROCESS_LOG_ID, 
    30. 
  
  
  30.         @ERROR_MESSAGE, 
    31. 
  
  
  31.         @COMPONENT_NAME, 
    32. 
  
  
  32.         GETDATE() 
    33. 
  
  
  33.     ) 
    34. 
  
  
  34. END
    35.

#p#

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

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

先后定义:

Business Scope -  COMMON_BIWORK_LOG ，短名称 - COMMON

Solution - COMMON_BI，短名称 - COMBI

ETL - ETL_TEMPLATE， ETL 包的全名即 SSIS Package 的文件名在这里就定义成了  BusinessScope_Solution_DataType_ETL

全名即 -  COMMON_COMBI_TRANS_ETL_TEMPLATE

那么一看这个包的名称，即使是一个新人，经过简单的项目框架培训，是不是一看这个包的名称就基本知道这个包是用来做什么的了。

下面这个脚本，应该在项目开发过程之前来执行，缺什么就定义什么。

 
 
    
  
  
  1. USE BIWORKLOG 
    2. 
  
  
  2. GO 
    3. 
  
  
  3. 
  
  
  4. ---------------------------------------------------------------------- 
    5. 
  
  
  5. -- Create BIWORK Log System  
    6. 
  
  
  6. -- by BIWORK at http://www.cnblogs.com/biwork 
    7. 
  
  
  7. ---------------------------------------------------------------------- 
    8. 
  
  
  8. DECLARE @BUSINESS_SCOPE_ID INT 
    9. 
  
  
  9. DECLARE @SOLUTION_ID INT 
    10. 
  
  
  10. DECLARE @SOLUTION_ETL_ID INT 
    11. 
  
  
  11. 
  
  
  12. -- Set the BUSINESS SCOPE ID 
    13. 
  
  
  13. IF NOT EXISTS( 
    14. 
  
  
  14.                 SELECT SCOPE_ID 
    15. 
  
  
  15.                 FROM dbo.BUSINESS_SCOPE  
    16. 
  
  
  16.                 WHERE SCOPE_NAME = 'COMMON_BIWORK_LOG'
    17. 
  
  
  17.              ) 
    18. 
  
  
  18. BEGIN 
    19. 
  
  
  19.     INSERT INTO dbo.BUSINESS_SCOPE 
    20. 
  
  
  20.     ( 
    21. 
  
  
  21.         SCOPE_NAME, 
    22. 
  
  
  22.         SCOPE_SHORT_NAME, 
    23. 
  
  
  23.         SCOPE_DESC, 
    24. 
  
  
  24.         SCOPE_OWNER, 
    25. 
  
  
  25.         SCOPE_OWNER_EMAIL, 
    26. 
  
  
  26.         CREATE_USER, 
    27. 
  
  
  27.         CREATE_TIME 
    28. 
  
  
  28.     )VALUES 
    29. 
  
  
  29.     ( 
    30. 
  
  
  30.         'COMMON_BIWORK_LOG', 
    31. 
  
  
  31.         'COMMON', 
    32. 
  
  
  32.         'Common BIWORK ETL log system', 
    33. 
  
  
  33.         'BIWORK', 
    34. 
  
  
  34.         'biwork@126.com', 
    35. 
  
  
  35.         SYSTEM_USER, 
    36. 
  
  
  36.         GETDATE() 
    37. 
  
  
  37.     )   
    38. 
  
  
  38. END 
    39. 
  
  
  39. 
  
  
  40. SELECT @BUSINESS_SCOPE_ID = SCOPE_ID 
    41. 
  
  
  41. FROM dbo.BUSINESS_SCOPE  
    42. 
  
  
  42. WHERE SCOPE_NAME = 'COMMON_BIWORK_LOG'
    43. 
  
  
  43. 
  
  
  44. -- Set the SOLUTION ID 
    45. 
  
  
  45. IF NOT EXISTS( 
    46. 
  
  
  46.                 SELECT SOLUTION_ID 
    47. 
  
  
  47.                 FROM dbo.SOLUTION  
    48. 
  
  
  48.                 WHERE SOLUTION_NAME = 'COMMON_BI'
    49. 
  
  
  49.              )  
    50. 
  
  
  50. BEGIN 
    51. 
  
  
  51.     INSERT INTO dbo.SOLUTION  
    52. 
  
  
  52.     ( 
    53. 
  
  
  53.         SCOPE_ID, 
    54. 
  
  
  54.         SOLUTION_NAME, 
    55. 
  
  
  55.         SOLUTION_SHORT_NAME, 
    56. 
  
  
  56.         SOLUTION_DESC, 
    57. 
  
  
  57.         SOLUTION_OWNER, 
    58. 
  
  
  58.         OWNER_EMAIL, 
    59. 
  
  
  59.         SOLUTION_START, 
    60. 
  
  
  60.         CREATE_USER, 
    61. 
  
  
  61.         CREATE_TIME 
    62. 
  
  
  62.     ) 
    63. 
  
  
  63.     VALUES 
    64. 
  
  
  64.     ( 
    65. 
  
  
  65.         @BUSINESS_SCOPE_ID, 
    66. 
  
  
  66.         'COMMON_BI', 
    67. 
  
  
  67.         'COMBI', 
    68. 
  
  
  68.         'ETL log framework record the execution information and error message for SSIS packages', 
    69. 
  
  
  69.         'BIWORK', 
    70. 
  
  
  70.         'biwork@126.com', 
    71. 
  
  
  71.         GETDATE(), 
    72. 
  
  
  72.         SYSTEM_USER, 
    73. 
  
  
  73.         GETDATE() 
    74. 
  
  
  74.     ) 
    75. 
  
  
  75. END 
    76. 
  
  
  76. 
  
  
  77. SELECT @SOLUTION_ID = SOLUTION_ID 
    78. 
  
  
  78. FROM dbo.SOLUTION  
    79. 
  
  
  79. WHERE SOLUTION_NAME = 'COMMON_BI'
    80. 
  
  
  80. 
  
  
  81. -- Set the SOLUTION ETL ID 
    82. 
  
  
  82. IF NOT EXISTS ( 
    83. 
  
  
  83.                  SELECT ETL_ID 
    84. 
  
  
  84.                  FROM dbo.SOLUTION_ETL  
    85. 
  
  
  85.                  WHERE ETL_NAME = 'ETL_TEMPLATE'
    86. 
  
  
  86.               ) 
    87. 
  
  
  87. BEGIN 
    88. 
  
  
  88.     INSERT INTO dbo.SOLUTION_ETL 
    89. 
  
  
  89.     ( 
    90. 
  
  
  90.         SOLUTION_ID, 
    91. 
  
  
  91.         FLOW_TYPE_ID, 
    92. 
  
  
  92.         ETL_NAME, 
    93. 
  
  
  93.         ETL_PACKAGE_NAME, 
    94. 
  
  
  94.         ETL_DESC, 
    95. 
  
  
  95.         ETL_FST_OWNER, 
    96. 
  
  
  96.         ETL_FST_OWNER_EMAIL, 
    97. 
  
  
  97.         ETL_SEC_OWNER, 
    98. 
  
  
  98.         ETL_SEC_OWNER_EMAIL, 
    99. 
  
  
  99.         CREATE_USER, 
    100. 
  
  
  100.         CREATE_TIME 
    101. 
  
  
  101.     ) 
    102. 
  
  
  102.     VALUES 
    103. 
  
  
  103.     ( 
    104. 
  
  
  104.         @SOLUTION_ID, 
    105. 
  
  
  105.         20, -- Flow type is common data transformation 
    106. 
  
  
  106.         'ETL_TEMPLATE', 
    107. 
  
  
  107.         -- SSIS Package naming rule  [SCOPE_SHORT_NAME]_[SOLUTION_SHORT_NAME]_[FLOW_TYPE]_[ETL_NAME] 
    108. 
  
  
  108.         'COMMON_COMBI_TRANS_ETL_TEMPLATE', 
    109. 
  
  
  109.         'ETL log template package', 
    110. 
  
  
  110.         'BIWORK', 
    111. 
  
  
  111.         'biwork@126.com', 
    112. 
  
  
  112.         NULL, 
    113. 
  
  
  113.         NULL, 
    114. 
  
  
  114.         SYSTEM_USER, 
    115. 
  
  
  115.         GETDATE() 
    116. 
  
  
  116.     ) 
    117. 
  
  
  117. END
    118.

有了这些信息，我们就可以开始设计和开发我们的 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 出现的什么错误。

#p#

五. 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 日志系统的读写权限一定是非常非常高的。

 
 
    
  
  
  1. UPDATE [dbo].[SSIS CONFIGURATIONS] 
    2. 
  
  
  2. SET ConfiguredValue = ( 
    3. 
  
  
  3.                         SELECT ETL_ID  
    4. 
  
  
  4.                         FROM dbo.SOLUTION_ETL  
    5. 
  
  
  5.                         WHERE ETL_PACKAGE_NAME = 'COMMON_COMBI_TRANS_ETL_TEMPLATE'
    6. 
  
  
  6.                       ) 
    7. 
  
  
  7. WHERE ConfigurationFilter = 'COMMON_COMBI_TRANS_ETL_TEMPLATE'
    8. 
  
  
  8. AND PackagePath = '\Package.Variables[User::PC_SOLUTION_ETL_ID].Properties[Value]'
    9. 
  
  
  9. 
  
  
  10. SELECT * FROM [dbo].[SSIS CONFIGURATIONS] 
    11.

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

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，因此。

 
 
    
  
  
  1. -- Set the SOLUTION ETL ID 
    2. 
  
  
  2. IF NOT EXISTS ( 
    3. 
  
  
  3.                  SELECT ETL_ID 
    4. 
  
  
  4.                  FROM dbo.SOLUTION_ETL  
    5. 
  
  
  5.                  WHERE ETL_NAME = 'ETL_TEMPLATE_TEST'
    6. 
  
  
  6.               ) 
    7. 
  
  
  7. BEGIN
    8. 
  
  
  8.     INSERT INTO dbo.SOLUTION_ETL 
    9. 
  
  
  9.     ( 
    10. 
  
  
  10.         SOLUTION_ID, 
    11. 
  
  
  11.         FLOW_TYPE_ID, 
    12. 
  
  
  12.         ETL_NAME, 
    13. 
  
  
  13.         ETL_PACKAGE_NAME, 
    14. 
  
  
  14.       &n            
    
                网页题目：ETL项目中管理上百个SSIS包的日志和包配置框架
                
    
                分享路径：http://www.shufengxianlan.com/qtweb/news39/252089.html
            
    
            
    
                网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等
                                
    
            
    
                广告
                
            
    
            
    
                声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：
                创新互联

猜你还喜欢下面的内容

网站营销知识

行业网站建设

成都网站维护　　 　盐亭做网站　　 　微官网开发　　 　达州服务器托管　　 　达州网站设计　　 　广安网站建设　　 　广安网站建设　　 　响应式建站　　 　定制网站建设　　 　成都网站设计　　 　成都主机托管　　 　成都h5网站建设　　 　成都网站推广公司　　 　盐亭网站设计　　 　内江服务器托管　　 　百度推广公司　　 　成都服务器租用　　 　网站搜索引擎优化　　 　网站域名　　 　电信机房托管