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

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

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

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

1. 假設(shè)我們項(xiàng)目中，一個(gè)項(xiàng)目中最終上線的 ETL 包多達(dá)上百個(gè)，如何對(duì)這些包進(jìn)行統(tǒng)一的日志管理 ？

2. 現(xiàn)在在線運(yùn)行的 ETL 包有多少個(gè)？ 多長(zhǎng)時(shí)間？ 哪些包的運(yùn)行時(shí)間最長(zhǎng)，哪些最少 ？ 項(xiàng)目經(jīng)理需要一份圖表能夠反映出這些 KPI 來(lái)。

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

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

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

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

7. 哪些包是用來(lái)加載文件，輸出文件的，哪些包只是用來(lái)一般的數(shù)據(jù)轉(zhuǎn)換的 ？ 輸入文件在哪里 ？ 輸出文件在哪里 ？

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

9. 每周的項(xiàng)目會(huì)議中，考慮過沒有拿出上面的這些答案，數(shù)據(jù)，圖表來(lái)對(duì)付各個(gè)老大們的提問 ？

.........

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

本文將分為以下幾個(gè)部分來(lái)闡述：

1. 日志系統(tǒng)的在 ETL 項(xiàng)目中的位置
2. 日志系統(tǒng)的角色構(gòu)成
3. 日志系統(tǒng)的數(shù)據(jù)庫(kù)對(duì)象
4. 日志系統(tǒng)在 SSIS Package 中的使用
5. SSIS Package 配置管理在日志系統(tǒng)中的集成
6. SSIS Package 模板開發(fā)
7. 日志系統(tǒng)的報(bào)表開發(fā)

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

日志系統(tǒng)簡(jiǎn)單來(lái)說(shuō)就是一個(gè)數(shù)據(jù)庫(kù)，里面有一些維護(hù)和管理日志數(shù)據(jù)的數(shù)據(jù)表以及一些視圖或者存儲(chǔ)過程構(gòu)成。它在 ETL 項(xiàng)目中的位置應(yīng)該獨(dú)立于其它任意數(shù)據(jù)庫(kù)，比如 BI 項(xiàng)目中的 Staging 數(shù)據(jù)庫(kù)，DW 數(shù)據(jù)庫(kù)以及各種各樣的 Transaction 數(shù)據(jù)庫(kù)。

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

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

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

所謂角色構(gòu)成即它們?cè)谌罩鞠到y(tǒng)中扮演的角色。

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

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

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

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

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

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

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

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

三. 日志系統(tǒng)的數(shù)據(jù)庫(kù)對(duì)象

上面的這些角色反映在數(shù)據(jù)庫(kù)中就是下面的這些數(shù)據(jù)庫(kù)對(duì)象了，可以很容易看到它們之間的關(guān)系。

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

詳細(xì)創(chuàng)建這些對(duì)象的腳本也提供給大家使用，在此基礎(chǔ)之上可以擴(kuò)展 Auditing 的工具，包括輸入，輸出文件的記錄，Archive 文件的記錄，表記錄的更改條數(shù)，新增條數(shù)，正確率，錯(cuò)誤率等記錄都可以基于上面的 Process Log 進(jìn)行擴(kuò)展。我可以在以后再單獨(dú)寫一篇 Auditing 方面的內(nèi)容，來(lái)介紹如何集成 SSIS 自身 Log 來(lái)記錄這些 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') 

插入日志的存儲(chǔ)過程 - 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 

更新結(jié)束 Log 的存儲(chǔ)過程 - 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 

插入錯(cuò)誤日志的存儲(chǔ)過程 - 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 中的使用

在每個(gè)項(xiàng)目開發(fā)之初，特別是新項(xiàng)目，按照我們上面提到的內(nèi)容，我們首先應(yīng)該就是定義好我們的 Business Scope, Solution, Solution ETL 這些內(nèi)容。

先后定義:

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

那么一看這個(gè)包的名稱，即使是一個(gè)新人，經(jīng)過簡(jiǎn)單的項(xiàng)目框架培訓(xùn)，是不是一看這個(gè)包的名稱就基本知道這個(gè)包是用來(lái)做什么的了。 

下面這個(gè)腳本，應(yīng)該在項(xiàng)目開發(fā)過程之前來(lái)執(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 

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

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

ETL 名稱就是 COMMON_COMBI_TRANS_ETL_TEMPLATE。

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

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

EST_INSERT_START_LOG

EST_UPDATE_END_LOG

保存并運(yùn)行 SSIS Package ，并可以到 Process Log 表中查看結(jié)果。

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

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

EST_UPDATE_END_LOG

 EST_INSERT_ERROR_LOG

在控制流中添加一個(gè)錯(cuò)誤的 Execute SQL Task來(lái)測(cè)試一下 -

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

Event Handlers 中的 OnError 事件。

查看數(shù)據(jù)庫(kù)中的日志和錯(cuò)誤記錄。

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

同時(shí)，基于這個(gè)框架之上來(lái)開發(fā)我們的其它 Task 組件，每次出錯(cuò)的時(shí)候不需要再次打開 Execution Results 去找 Error Information。倘若一個(gè) Package 有幾十個(gè) Task，這個(gè)執(zhí)行的記錄將會(huì)非常長(zhǎng)，查錯(cuò)誤很麻煩。而現(xiàn)在只需要去查詢一下 Error Log 就可以了，可以非?？斓恼业皆谀膫€(gè) Component 出現(xiàn)的什么錯(cuò)誤。

#p#

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

還記得前面的幾個(gè)變量嗎？

PC - Package Configuration，也就是說(shuō)這是 Package 配置級(jí)別的，會(huì)在 Package 配置中完成。

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

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

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

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

首先，在這里要搞清楚一個(gè)概念 - BIWORKLOG 是核心日志數(shù)據(jù)庫(kù)，這個(gè)數(shù)據(jù)庫(kù)部署的位置先要確定好，應(yīng)該部署在 JOB 運(yùn)行時(shí)的 SERVER。

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

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

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

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

同樣的道理，配置 PE_COMMON_DATABASE_NAME。

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

配置好了這兩個(gè)系統(tǒng)環(huán)境變量之后，我們?cè)賮?lái)更改 Connection Manager 下的 CM_DB_BIWORK 的 Expression。

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

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

再來(lái)看 PC_SOLUTION_ETL_ID 是怎么回事？

每次往 PROCESS_LOG 插入日志的時(shí)候就需要提供是哪個(gè) ETL 運(yùn)行的標(biāo)志，我們應(yīng)該這樣來(lái)獲取 SOLUTION_ETL_ID。

包括以后，如果我們要使用到另外的數(shù)據(jù)源，比如數(shù)據(jù)庫(kù)連接對(duì)象等等如何配置呢？ 像這些配置選項(xiàng)也可以集中配置在一張表中來(lái)進(jìn)行管理。

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

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

看到這個(gè)界面就知道這個(gè)表已經(jīng)在 CM_DB_BIWORKLOG 連接管理器中所表示的 BIWORKLOG 數(shù)據(jù)庫(kù)中準(zhǔn)備創(chuàng)建了。

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

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

保存這個(gè)配置。

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

如果需要更新某一個(gè)具體的值的話，需要 Update 就應(yīng)該這樣來(lái)更新，再次強(qiáng)調(diào) - BIWORKLOG 日志系統(tǒng)的讀寫權(quán)限一定是非常非常高的。

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

PC 的值來(lái)自于 BIWORKLOG 中的 SSIS CONFIGURATIONS 表，PC_SOLUTION_ETL_ID 在插入日志和更新日志的時(shí)候會(huì)反復(fù)用到，它標(biāo)識(shí)了當(dāng)前 ETL 的ID。

也就是說(shuō)沒有 PE 就不會(huì)有 BIWORKLOG 的成功連接，沒有 BIWORKLOG 就不會(huì)有 PC 在數(shù)據(jù)庫(kù)中的配置，沒有 PC 就不會(huì)有 Log 日志的插入。

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

以后所有 Package 級(jí)別的變量配置就都有 SSIS CONFIGURATIONS 表來(lái)維護(hù)，通過 Configuration Filter 來(lái)過濾。

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

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

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

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

下面就使用上面的模板來(lái)演示實(shí)際運(yùn)用，并在模板基礎(chǔ)之上來(lái)完成一個(gè) SSIS Pakcage 的開發(fā)。

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

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

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

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

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

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

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

由于這個(gè) SSIS Package 和 COMMON_COMBI_TRANS_ETL_TEMPLATE 屬于同一個(gè) 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 

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

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

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

保存后查看數(shù)據(jù)庫(kù)表 SSIS CONFIGURATIONS，這個(gè)配置就完成了。

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

#p#

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

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

下面我隨便寫兩兩張報(bào)表，但是實(shí)際上可做的報(bào)表，實(shí)用的報(bào)表很多。

如果再回頭看看文本開頭時(shí)的幾個(gè)問題的話，我們是不是完全開發(fā)一些報(bào)表來(lái)回答？

1. 假設(shè)我們項(xiàng)目中，一個(gè)項(xiàng)目中最終上線的 ETL 包多達(dá)上百個(gè)，如何對(duì)這些包進(jìn)行統(tǒng)一的日志管理 ？

2. 現(xiàn)在在線運(yùn)行的 ETL 包有多少個(gè)？ 多長(zhǎng)時(shí)間？ 哪些包的運(yùn)行時(shí)間最長(zhǎng)，哪些最少 ？ 項(xiàng)目經(jīng)理需要一份圖表能夠反映出這些 KPI 來(lái)。

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

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

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

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

7. 哪些包是用來(lái)加載文件，輸出文件的，哪些包只是用來(lái)一般的數(shù)據(jù)轉(zhuǎn)換的 ？ 輸入文件在哪里 ？ 輸出文件在哪里 ？

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

9. 每周的項(xiàng)目會(huì)議中，考慮過沒有拿出上面的這些答案，數(shù)據(jù)，圖表來(lái)對(duì)付各個(gè)老大們的提問 ？

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

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

關(guān)于 Auditing 的部分下次再慢慢寫。

注意的地方：

1. 最后一定要強(qiáng)調(diào)，COMMON BIWORK LOG 一定要控制好權(quán)限，只能允許少數(shù)人對(duì)核心配置表進(jìn)行修改，他人只能進(jìn)行查詢，并同時(shí)應(yīng)做好備份工作。 

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

3. 不支持子父 Package，如果需要支持子父 Package 的調(diào)用，可以自行修改表結(jié)構(gòu)，在 PROCESS_LOG 中加上一列 PARENT_LOG_ID 然后再修改相應(yīng)的存儲(chǔ)過程。

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

如果跟著這篇文章做下來(lái)，超不過30分鐘就可以搭建一個(gè)小的 BIWORKLOG 框架，可以根據(jù)實(shí)際項(xiàng)目需要大家自行擴(kuò)展和豐富功能。

關(guān)于能不能統(tǒng)一管理上百個(gè)的 ETL的日志系統(tǒng) ，我可以肯定的回答：沒有問題，因?yàn)樗旧碇皇怯脕?lái)記錄日志的地方。并且統(tǒng)一管理的好處就是自上而下規(guī)范了所有 ETL 的設(shè)計(jì)，包括命名規(guī)范和包配置。我見過有上百個(gè) SSIS Package 三年以來(lái)還管理很好的 ETL 項(xiàng)目，也見過只有幾十個(gè) ETL 但日志，配置非常混亂的項(xiàng)目。

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

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