一直準備寫這么一篇有關 SSIS 日志系統(tǒng)的文章，但是發(fā)現(xiàn)很難一次寫的很完整。因為這篇文章的內容可擴展的性太強，每多擴展一部分就意味著需要更多代碼，示例和理論支撐。因此，我選擇我覺得比較通用的 LOG 部分，在這里分享一下給大家，希望對大家在設計 ETL 的日志系統(tǒng)時有所啟發(fā)和幫助。

當然在這里要區(qū)分 Logging 和 Auditing 的區(qū)別，Logging 主要用來記錄發(fā)生了什么事情，Auditing 側重描述過程中產生的數據量，新增了多少，修改了多少等記錄條數。

本文主要講解 Log 部分，以后有時間再來講解如何在 Log System 中集成 Auditing 的功能。

首先，在這里提出幾個問題，可以試著回答一下？

1. 假設我們項目中，一個項目中最終上線的 ETL 包多達上百個，如何對這些包進行統(tǒng)一的日志管理 ？

2. 現(xiàn)在在線運行的 ETL 包有多少個？ 多長時間？ 哪些包的運行時間最長，哪些最少 ？ 項目經理需要一份圖表能夠反映出這些 KPI 來。

3. 每天運行的 ETL 包有多少個？ 測試環(huán)境，開發(fā)環(huán)境上都跑了多少個 ?

4. 如何快速查詢每個包運行的狀態(tài)，成功否，失敗否，失敗的原因等等 ？

5. 每個包都有一個配置文件，幾百個包的配置文件又是如何進行管理的 ？ 這些配置文件中都有些什么內容 ？現(xiàn)在配置的參數都各自是什么？

6. 這些包各自大概屬于哪些部門使用的？業(yè)務范圍是什么？ 這些包失敗了找誰？ 誰開發(fā)的？

7. 哪些包是用來加載文件，輸出文件的，哪些包只是用來一般的數據轉換的 ？ 輸入文件在哪里 ？ 輸出文件在哪里 ？

8. 隨便給出一個 SSIS Package 的名稱，你知道它大概是做什么的嗎？

9. 每周的項目會議中，考慮過沒有拿出上面的這些答案，數據，圖表來對付各個老大們的提問 ？

.........

這些問題其實都是應該要好好考慮的，并且完全可以在項目開發(fā)之初，花上一天或幾天時間搭建與適用于當前項目的日志系統(tǒng)，然后再花上一定時間對這套日志系統(tǒng)的報表完成設計和開發(fā)。在解決上述問題的同時，我相信受益的不僅僅是各個工作在一線的開發(fā)者，不僅僅可以提高他們的工作效率，而且在后期可以減少大量維護運營 ETL 的人力和時間成本。

本文將分為以下幾個部分來闡述：

1. 日志系統(tǒng)的在 ETL 項目中的位置
2. 日志系統(tǒng)的角色構成
3. 日志系統(tǒng)的數據庫對象
4. 日志系統(tǒng)在 SSIS Package 中的使用
5. SSIS Package 配置管理在日志系統(tǒng)中的集成
6. SSIS Package 模板開發(fā)
7. 日志系統(tǒng)的報表開發(fā)

一. 日志系統(tǒng)的在 ETL 項目中的位置

日志系統(tǒng)簡單來說就是一個數據庫，里面有一些維護和管理日志數據的數據表以及一些視圖或者存儲過程構成。它在 ETL 項目中的位置應該獨立于其它任意數據庫，比如 BI 項目中的 Staging 數據庫，DW 數據庫以及各種各樣的 Transaction 數據庫。

在一個服務器中，可能我們因為業(yè)務的原因，系統(tǒng)中存在一個或者多個 Staging 數據庫，DW 數據庫，但是日志數據庫只有一個。這個日志數據庫是所有包含 ETL Package 項目的真正核心，它管理和維護著各個 ETL Package 中的所有日志狀態(tài)，包配置，主題劃分等信息和內容。 而這些 ETL 所做的事情就是在各個數據源，目的地數據庫中間抽取，轉換，存儲數據的工作，所有的工作操作記錄將保存在日志系統(tǒng)中。

假設這個日志數據的名稱就叫做 BIWorkLog，當然有我在博客園的 ID - BIWork，但字面意思更容易理解 - BI 工作日志 ：）。

 

二. 日志系統(tǒng)的角色構成

 

所謂角色構成即它們在日志系統(tǒng)中扮演的角色。

Business Scope - 我給它的定義就是業(yè)務邊界，什么叫做業(yè)務邊界。假設 ETL 系統(tǒng)中開發(fā)并部署了上百個 SSIS Package，這些 Package 肯定有來自不同的 Group 的吧，比如市場部門的，財務部門的。自然市場部門的業(yè)務主要是圍繞市場方面的 ETL 需求，財務部門的主要圍繞財務部門的。當然換另外一種說法，我們在開發(fā)項目定義需求的時候，會有模塊劃分，也會定義出業(yè)務主題和邊界。那么這里的 Business Scope 就是這種作用，對 ETL 做出主題劃分，并且這里的 Business Scope 將貫穿 ETL 設計過程的始終。比如 ETL 的命名首先就以 Business Scope 開頭，當看到這個 ETL 的時候就大概知道這個 ETL 也什么業(yè)務范圍的了。 記住：命名規(guī)范在哪里都使用。

Solution - Solution 和上面的 Business Scope 的含義也比較類似，但是面要更小一些。通常情況下，會把相同業(yè)務或者相關業(yè)務的 ETL 創(chuàng)建在同一個項目中，這里的解決方案就可以描述為 Business Scope ,而項目的名稱可以描述為 Solution，然后下面會有很多的 ETL SSIS Package。ETL 的命名和它也有關系，BusinessScopeName_SolutionName_XXX。當然，這里的 Solution 和 創(chuàng)建項目中的 Solution 含義上有點小小的區(qū)別。

Solution ETL - Solution 下的具體 SSIS Package，這是真正的運行數據加載，清洗和轉換的包的定義區(qū)域。而日志功能就是圍繞 Solution ETL 來進行記錄的。

Data Flow - 數據流，數據流類型。比如文件的加載有 Input 有 Output 方向的，也有同時兼?zhèn)?Input 和 Output 的。非文件的 ETL 數據流類型那就是普通的 Transformation 了。任何的 ETL 無非就是這些類型，將 Data Flow 的類型定義在 ETL 的命名上， BusinessScopeName_SolutionName_DataFlowType_XXX。是不是隨便在上百個或者上千個 SSIS Package 中挑出一個，一看 ETL 命名就大概知道這個 ETL 屬于哪個業(yè)務范圍，哪個 Solution，是做文件加載還是文件輸出 ？ 這些信息是不是一目了然 ？

Execution Status - 執(zhí)行狀態(tài)，ETL 的執(zhí)行狀態(tài)，就三種 - 成功，失敗和執(zhí)行中。

Configuration - 所有包的配置都應該集中管理，不應該分散到各個 XML 格式的配置文件中，而應該集中到我這里提到的 Configuration 中。

Process Log - 所有包運行的記錄也都應該集中管理，它們的運行記錄也都應該集中在一個地方進行管理和跟蹤。我見過不同公司不同的項目，每創(chuàng)建一個 ETL 就是一份 XML 配置文件，一個 SSIS 自動生成的 Process Log。當整個項目中就幾個 ETL Package 的時候，確實沒有什么問題，完成部署也很快，很容易。但是如果能稍微花點時間認真搭建一個屬于自己項目的日志系統(tǒng)，所有人共同遵守的話，我可以說后續(xù)的開發(fā)，維護，新人學習，管理等時間和人力成本將會節(jié)省更多。

Error Log - 伴隨著 Process Log ，但有所區(qū)別，它只記錄錯誤消息。

三. 日志系統(tǒng)的數據庫對象

上面的這些角色反映在數據庫中就是下面的這些數據庫對象了，可以很容易看到它們之間的關系。

注意 - SSIS Configurations 這張表不是直接創(chuàng)建的，而是第一次在設計 ETL 模板的時候創(chuàng)建的，后面會介紹到。

詳細創(chuàng)建這些對象的腳本也提供給大家使用，在此基礎之上可以擴展 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') 

插入日志的存儲過程 - 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,  
-- IN PROCESS  
    )  
 
    SELECT @PROCESS_LOG_ID = @@IDENTITY  
END 

更新結束 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 

插入錯誤日志的存儲過程 - 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 

#p#

四. 日志系統(tǒng)在 SSIS Package 中的使用

在每個項目開發(fā)之初，特別是新項目，按照我們上面提到的內容，我們首先應該就是定義好我們的 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

那么一看這個包的名稱，即使是一個新人，經過簡單的項目框架培訓，是不是一看這個包的名稱就基本知道這個包是用來做什么的了。 

下面這個腳本，應該在項目開發(fā)過程之前來執(zhí)行，缺什么就定義什么。

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 

有了這些信息，我們就可以開始設計和開發(fā)我們的 SSIS Package 了。

解決方案名稱可以使用 Business Scope 的名稱 COMMON_BIWORK_LOG，項目名稱就叫做 COMMON_BI。

ETL 名稱就是 COMMON_COMBI_TRANS_ETL_TEMPLATE。

先在包級別定義好這些變量并賦予一定的值，后面我會詳細解釋到它們各自的用途。

在 Control Flow 中創(chuàng)建這兩個 Execute SQL Task (EST) -

EST_INSERT_START_LOG

EST_UPDATE_END_LOG

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

前兩條是之前我自己測試的日志，第三條可以看到它的日志信息，最主要的就是 START TIME，F(xiàn)INISH TIME 以及 EXECUTE STATUS，1 表示執(zhí)行成功。

還有錯誤處理，對于錯誤的處理應該在 SSIS Package 的 Event Handler 中來完成，并且選擇的是 OnError。 OnError 將捕獲所有出錯的事件，那么我們就會做兩件事情。第一件就是在 PROCESS_LOG 中將對應的 PROCESS 狀態(tài)從 0 改成 - 1，即表示失敗。第二件事情即寫入錯誤消息到錯誤日志中。

EST_UPDATE_END_LOG

 

 EST_INSERT_ERROR_LOG

 

在控制流中添加一個錯誤的 Execute SQL Task來測試一下 -

保存并執(zhí)行 PACKAGE，出現(xiàn)錯誤并被 ON ERROR 捕獲。

Event Handlers 中的 OnError 事件。

查看數據庫中的日志和錯誤記錄。

那么這樣的一個小型日志框架就算搭建起來了，之后所有的 SSIS Package 都可以使用到這套框架。執(zhí)行 SSIS Package 時，所有的日志，錯誤日志都會集中寫到同一個 Log System 中。這套日志系統(tǒng)雖然看起來功能很簡單，但是能夠實實在在的支持上百個 ETL Package 的日志管理。不會出現(xiàn) 100 個 Package 有 100 套不同日志表的情況，極大的改善了我們維護，監(jiān)控和管理 ETL 的過程。

同時，基于這個框架之上來開發(fā)我們的其它 Task 組件，每次出錯的時候不需要再次打開 Execution Results 去找 Error Information。倘若一個 Package 有幾十個 Task，這個執(zhí)行的記錄將會非常長，查錯誤很麻煩。而現(xiàn)在只需要去查詢一下 Error Log 就可以了，可以非常快的找到在哪個 Component 出現(xiàn)的什么錯誤。

#p#

五. SSIS Package 配置管理在日志系統(tǒng)中的集成

還記得前面的幾個變量嗎？

PC - Package Configuration，也就是說這是 Package 配置級別的，會在 Package 配置中完成。

PE - Package Environment，Package 在系統(tǒng)環(huán)境中的配置。

PV - Package Variable ，無需配置，只是在 ETL Package 執(zhí)行過程中使用到。

比較常見的 SSIS Package Configuration 往往會選擇 XML Configuration File 來完成。當然現(xiàn)在在 SQL Server 2012 版本 BIDS 工具里已經不需要任何的 XML Configuration File 就可以完成配置了，非常容易。但是，在 JOB 的配置過程中還是需要提供配置的值。

而我希望的是，所有的配置都能夠集中在一張數據表中完成，也就是即使以后有幾十個，上百個 SSIS Package 它們的配置也都集中在一張表中完成的。不需要 XML 配置文件，不需要在 JOB 定義 Package 時配置任何參數 - 一旦發(fā)布，在外無參數配置！

首先，在這里要搞清楚一個概念 - BIWORKLOG 是核心日志數據庫，這個數據庫部署的位置先要確定好，應該部署在 JOB 運行時的 SERVER。

在系統(tǒng)環(huán)境變量中定義好 COMMON BIWORKLOG 數據庫所在 SERVER 的名稱，數據庫的名稱。

添加一個環(huán)境變量配置，選中 COMMON_ETL_LOG_SERVER

將這個環(huán)境變量的值賦值于 PE_COMMON_SERVER_NAME, 注意紅色框內的值明顯是我賦值錯了，在環(huán)境變量中它的值是 LOCALHOST。 那什么時候 PE_COMMON_SERVER_NAME 將獲取環(huán)境變量的值呢？ - 在 SSIS Package 運行的時候。

當 SSIS Package 運行的時候，包配置將首先完成包配置中各個值的裝配，此時的 PE_COMMON_SERVER_NAME 將接受系統(tǒng)環(huán)境變量 COMMON_ETL_LOG_SERVER 的值即 LOCALHOST。

同樣的道理，配置 PE_COMMON_DATABASE_NAME。

這是這兩個配置好的系統(tǒng)環(huán)境變量。

配置好了這兩個系統(tǒng)環(huán)境變量之后，我們再來更改 Connection Manager 下的 CM_DB_BIWORK 的 Expression。

這樣一來，在 SSIS Package 運行的時候，兩個 PE 變量將讀取系統(tǒng)環(huán)境變量的值，然后將兩個系統(tǒng)環(huán)境變量的值成功配置到了 BIWORLOG 日志數據庫的連接源了。

第一個配置就算完成了！記得，在完成下面操作的時候一定要重啟一下電腦，因為環(huán)境變量的配置有時需要重啟之后才能生效！

再來看 PC_SOLUTION_ETL_ID 是怎么回事？

每次往 PROCESS_LOG 插入日志的時候就需要提供是哪個 ETL 運行的標志，我們應該這樣來獲取 SOLUTION_ETL_ID。

包括以后，如果我們要使用到另外的數據源，比如數據庫連接對象等等如何配置呢？ 像這些配置選項也可以集中配置在一張表中來進行管理。

仍然打開 Package Configuration，然后在 Configuration Type 中選擇 SQL Server。

Configuration Table 這時選擇 NEW , 它將自動提供創(chuàng)建 SQL Server 配置表的代碼，可以修改一下表名稱。

看到這個界面就知道這個表已經在 CM_DB_BIWORKLOG 連接管理器中所表示的 BIWORKLOG 數據庫中準備創(chuàng)建了。

那么以后所有的包配置信息都可以配置在這一張表中，通過什么區(qū)分是哪一個包的配置呢？ 通過 COMMON_COMBI_TRANS_ETL_TEMPLATE 這個包的名稱來區(qū)分，這個值是需要手工寫上去的。

選擇將 PC_SOLUTION_ETL_ID 的 Value 給配置到 SQL Server 表中，這時需要注意良好的命名規(guī)范可以讓你快速的知道哪些值是需要被配置的。

保存這個配置。

查看數據庫中的配置，這里只有一個 Configuration Filter，以后每來一個 SSIS Package 就會在這里出現(xiàn)一個 Configuration Filter，配置上百個甚至上千個 ETL 包完全沒有問題。

如果需要更新某一個具體的值的話，需要 Update 就應該這樣來更新，再次強調 - BIWORKLOG 日志系統(tǒng)的讀寫權限一定是非常非常高的。

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 的值來自于系統(tǒng)環(huán)境變量，BIWORKLOG 數據庫的 SERVER 地址和 DATABASE NAME 都是由這兩個 PE 來賦值的。

PC 的值來自于 BIWORKLOG 中的 SSIS CONFIGURATIONS 表，PC_SOLUTION_ETL_ID 在插入日志和更新日志的時候會反復用到，它標識了當前 ETL 的ID。

也就是說沒有 PE 就不會有 BIWORKLOG 的成功連接，沒有 BIWORKLOG 就不會有 PC 在數據庫中的配置，沒有 PC 就不會有 Log 日志的插入。

這個配置鏈一定要理解清楚，我們所有的配置值就存在兩個地方，一個是系統(tǒng)環(huán)境變量，這個值需要配置一次以后就不用配了。

以后所有 Package 級別的變量配置就都有 SSIS CONFIGURATIONS 表來維護，通過 Configuration Filter 來過濾。

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

 

配置完畢后，再次運行 Package ，這時所有變量的值都不是在定義它們時候的值了，而是真正讀取于環(huán)境變量和 SSIS CONFIGURATIONS 表。

六. SSIS Package 模板開發(fā)

其實在我的這篇文章 - SSIS 系列 - 利用 SSIS 模板快速開發(fā) SSIS Package 中已經提到了如何使用 SSIS Package 模板快速開發(fā)。

下面就使用上面的模板來演示實際運用，并在模板基礎之上來完成一個 SSIS Pakcage 的開發(fā)。

找到我們上面創(chuàng)建的 Package。

 

我使用的是 Windows Server 2008 R2 操作系統(tǒng)，安裝的是 SQL Server 2008 R2 數據庫，默認的環(huán)境應該是 -

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

把包拷貝到這里。其它環(huán)境請參考 - SSIS 系列 - 利用 SSIS 模板快速開發(fā) SSIS Package

重啟一下 BIDS 開發(fā)工具，然后就可以使用這個模板了，我將使用這個模板開發(fā)一個小 SSIS Package。

一定要注意，是選擇項目名稱右鍵添加 - New Item，看到了這個模板了嗎？

不要著急填寫名稱，而應該參照下面規(guī)范代碼 -

由于這個 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 

按照這個名稱創(chuàng)建包，但注意不要急著運行。因為很顯然，PC_SOLUTION_ID 在上面顯示的為 2，并且在 SSIS CONFIGURATIONS 表中的 Configuration Filter 也不一樣。

先將 PC_SOLUTION_ETL_ID 的值設置為2，這樣一會修改 Package Configuration 的時候，在第一次創(chuàng)建新的 Configuration Filter 的時候就會將這個值寫到數據庫中。

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

保存后查看數據庫表 SSIS CONFIGURATIONS，這個配置就完成了。

如果后面還需要任何的變量配置，都可以通過這種方式集中配置在這張表中，這樣我們的變量就不需要配置到 XML 文件中，一來是不方便檢查配置的內容，二來當 ETL 數量增多的時候， XML 配置文件的管理和配置稍不注意也容易出現(xiàn)混亂。

#p#

七. 日志系統(tǒng)的報表開發(fā)

 

有了上面的這些表對象和日志記錄，其實很容易開發(fā)出一些常用的日志報表，并且自定義的功能更完善更細致。

下面我隨便寫兩兩張報表，但是實際上可做的報表，實用的報表很多。

如果再回頭看看文本開頭時的幾個問題的話，我們是不是完全開發(fā)一些報表來回答？

1. 假設我們項目中，一個項目中最終上線的 ETL 包多達上百個，如何對這些包進行統(tǒng)一的日志管理 ？

2. 現(xiàn)在在線運行的 ETL 包有多少個？ 多長時間？ 哪些包的運行時間最長，哪些最少 ？ 項目經理需要一份圖表能夠反映出這些 KPI 來。

3. 每天運行的 ETL 包有多少個？ 測試環(huán)境，開發(fā)環(huán)境上都跑了多少個 ?

4. 如何快速查詢每個包運行的狀態(tài)，成功否，失敗否，失敗的原因等等 ？

5. 每個包都有一個配置文件，幾百個包的配置文件又是如何進行管理的 ？ 這些配置文件中都有些什么內容 ？現(xiàn)在配置的參數都各自是什么？

6. 這些包各自大概屬于哪些部門使用的？業(yè)務范圍是什么？ 這些包失敗了找誰？ 誰開發(fā)的？

7. 哪些包是用來加載文件，輸出文件的，哪些包只是用來一般的數據轉換的 ？ 輸入文件在哪里 ？ 輸出文件在哪里 ？

8. 隨便給出一個 SSIS Package 的名稱，你知道它大概是做什么的嗎？

9. 每周的項目會議中，考慮過沒有拿出上面的這些答案，數據，圖表來對付各個老大們的提問 ？

實際上基于上面的日志系統(tǒng)還有很多東西可以做，特別是 AUDITING 的內容可以擴展的非常豐富。這一部分可以集成到上述的 Log System 中，比如記錄文件的輸入，輸出位置，導入導出的記錄條數；表記錄的增長條數，修改條數等等，這一切都可以集成起來配置 Log System 來使用。

Logging System + Auditing System 構成一個完整的維護和管理 ETL 包的運行記錄的框架體系，對于我們開發(fā)，測試，以及線上的維護，調優(yōu)等工作都是非常非常重要的。

關于 Auditing 的部分下次再慢慢寫。

注意的地方：

1. 最后一定要強調，COMMON BIWORK LOG 一定要控制好權限，只能允許少數人對核心配置表進行修改，他人只能進行查詢，并同時應做好備份工作。 

2. 系統(tǒng)環(huán)境變量的配置會要求服務器重啟，這個在很多已上線的環(huán)境可能很難做到，因此可以靈活改變。 在 SQL Server 2008 環(huán)境下，可以使用 XML Package Configuration。在 2010 、2012 開發(fā)環(huán)境下，可以使用 Package Parameter 來進行配置，也非常方便和靈活。

3. 不支持子父 Package，如果需要支持子父 Package 的調用，可以自行修改表結構，在 PROCESS_LOG 中加上一列 PARENT_LOG_ID 然后再修改相應的存儲過程。

這篇文章一字一字敲從晚上8點敲到現(xiàn)在將近 6 個鐘頭，我相信我已經盡量做到把一些關鍵點交待清楚，相信只要認真看下來是非常容易理解的。

如果跟著這篇文章做下來，超不過30分鐘就可以搭建一個小的 BIWORKLOG 框架，可以根據實際項目需要大家自行擴展和豐富功能。

關于能不能統(tǒng)一管理上百個的 ETL的日志系統(tǒng) ，我可以肯定的回答：沒有問題，因為它本身只是用來記錄日志的地方。并且統(tǒng)一管理的好處就是自上而下規(guī)范了所有 ETL 的設計，包括命名規(guī)范和包配置。我見過有上百個 SSIS Package 三年以來還管理很好的 ETL 項目，也見過只有幾十個 ETL 但日志，配置非?；靵y的項目。

希望這套小框架對大家有所幫助！歡迎多多交流！

原文鏈接：http://www.cnblogs.com/biwork/p/biworklog.html