建站
咨詢
售后
建站咨詢
新聞中心
一直準(zhǔn)備寫這么一篇有關(guān) SSIS 日志系統(tǒng)的文章，但是發(fā)現(xiàn)很難一次寫的很完整。因為這篇文章的內(nèi)容可擴(kuò)展的性太強(qiáng)，每多擴(kuò)展一部分就意味著需要更多代碼，示例和理論支撐。因此，我選擇我覺得比較通用的 LOG 部分，在這里分享一下給大家，希望對大家在設(shè)計 ETL 的日志系統(tǒng)時有所啟發(fā)和幫助。
建網(wǎng)站原本是網(wǎng)站策劃師、網(wǎng)絡(luò)程序員、網(wǎng)頁設(shè)計師等，應(yīng)用各種網(wǎng)絡(luò)程序開發(fā)技術(shù)和網(wǎng)頁設(shè)計技術(shù)配合操作的協(xié)同工作。創(chuàng)新互聯(lián)專業(yè)提供成都網(wǎng)站設(shè)計、做網(wǎng)站、成都外貿(mào)網(wǎng)站建設(shè)公司,網(wǎng)頁設(shè)計,網(wǎng)站制作(企業(yè)站、響應(yīng)式網(wǎng)站建設(shè)、電商門戶網(wǎng)站)等服務(wù),從網(wǎng)站深度策劃、搜索引擎友好度優(yōu)化到用戶體驗的提升,我們力求做到極致!
當(dāng)然在這里要區(qū)分 Logging 和 Auditing 的區(qū)別，Logging 主要用來記錄發(fā)生了什么事情，Auditing 側(cè)重描述過程中產(chǎn)生的數(shù)據(jù)量，新增了多少，修改了多少等記錄條數(shù)。
本文主要講解 Log 部分，以后有時間再來講解如何在 Log System 中集成 Auditing 的功能。
首先，在這里提出幾個問題，可以試著回答一下？
1. 假設(shè)我們項目中，一個項目中最終上線的 ETL 包多達(dá)上百個，如何對這些包進(jìn)行統(tǒng)一的日志管理 ？
2. 現(xiàn)在在線運(yùn)行的 ETL 包有多少個？ 多長時間？ 哪些包的運(yùn)行時間最長，哪些最少 ？ 項目經(jīng)理需要一份圖表能夠反映出這些 KPI 來。
3. 每天運(yùn)行的 ETL 包有多少個？ 測試環(huán)境，開發(fā)環(huán)境上都跑了多少個 ?
4. 如何快速查詢每個包運(yùn)行的狀態(tài)，成功否，失敗否，失敗的原因等等 ？
5. 每個包都有一個配置文件，幾百個包的配置文件又是如何進(jìn)行管理的 ？ 這些配置文件中都有些什么內(nèi)容 ？現(xiàn)在配置的參數(shù)都各自是什么？
6. 這些包各自大概屬于哪些部門使用的？業(yè)務(wù)范圍是什么？ 這些包失敗了找誰？ 誰開發(fā)的？
7. 哪些包是用來加載文件，輸出文件的，哪些包只是用來一般的數(shù)據(jù)轉(zhuǎn)換的 ？ 輸入文件在哪里 ？ 輸出文件在哪里 ？
8. 隨便給出一個 SSIS Package 的名稱，你知道它大概是做什么的嗎？
9. 每周的項目會議中，考慮過沒有拿出上面的這些答案，數(shù)據(jù)，圖表來對付各個老大們的提問 ？
.........
這些問題其實都是應(yīng)該要好好考慮的，并且完全可以在項目開發(fā)之初，花上一天或幾天時間搭建與適用于當(dāng)前項目的日志系統(tǒng)，然后再花上一定時間對這套日志系統(tǒng)的報表完成設(shè)計和開發(fā)。在解決上述問題的同時，我相信受益的不僅僅是各個工作在一線的開發(fā)者，不僅僅可以提高他們的工作效率，而且在后期可以減少大量維護(hù)運(yùn)營 ETL 的人力和時間成本。
本文將分為以下幾個部分來闡述：
- 日志系統(tǒng)的在 ETL 項目中的位置
- 日志系統(tǒng)的角色構(gòu)成
- 日志系統(tǒng)的數(shù)據(jù)庫對象
- 日志系統(tǒng)在 SSIS Package 中的使用
- SSIS Package 配置管理在日志系統(tǒng)中的集成
- SSIS Package 模板開發(fā)
- 日志系統(tǒng)的報表開發(fā)
一. 日志系統(tǒng)的在 ETL 項目中的位置
日志系統(tǒng)簡單來說就是一個數(shù)據(jù)庫，里面有一些維護(hù)和管理日志數(shù)據(jù)的數(shù)據(jù)表以及一些視圖或者存儲過程構(gòu)成。它在 ETL 項目中的位置應(yīng)該獨(dú)立于其它任意數(shù)據(jù)庫，比如 BI 項目中的 Staging 數(shù)據(jù)庫，DW 數(shù)據(jù)庫以及各種各樣的 Transaction 數(shù)據(jù)庫。
在一個服務(wù)器中，可能我們因為業(yè)務(wù)的原因，系統(tǒng)中存在一個或者多個 Staging 數(shù)據(jù)庫，DW 數(shù)據(jù)庫，但是日志數(shù)據(jù)庫只有一個。這個日志數(shù)據(jù)庫是所有包含 ETL Package 項目的真正核心，它管理和維護(hù)著各個 ETL Package 中的所有日志狀態(tài)，包配置，主題劃分等信息和內(nèi)容。 而這些 ETL 所做的事情就是在各個數(shù)據(jù)源，目的地數(shù)據(jù)庫中間抽取，轉(zhuǎn)換，存儲數(shù)據(jù)的工作，所有的工作操作記錄將保存在日志系統(tǒng)中。
假設(shè)這個日志數(shù)據(jù)的名稱就叫做 BIWorkLog，當(dāng)然有我在博客園的 ID - BIWork，但字面意思更容易理解 - BI 工作日志 ：）。
二. 日志系統(tǒng)的角色構(gòu)成
所謂角色構(gòu)成即它們在日志系統(tǒng)中扮演的角色。
Business Scope - 我給它的定義就是業(yè)務(wù)邊界，什么叫做業(yè)務(wù)邊界。假設(shè) ETL 系統(tǒng)中開發(fā)并部署了上百個 SSIS Package，這些 Package 肯定有來自不同的 Group 的吧，比如市場部門的，財務(wù)部門的。自然市場部門的業(yè)務(wù)主要是圍繞市場方面的 ETL 需求，財務(wù)部門的主要圍繞財務(wù)部門的。當(dāng)然換另外一種說法，我們在開發(fā)項目定義需求的時候，會有模塊劃分，也會定義出業(yè)務(wù)主題和邊界。那么這里的 Business Scope 就是這種作用，對 ETL 做出主題劃分，并且這里的 Business Scope 將貫穿 ETL 設(shè)計過程的始終。比如 ETL 的命名首先就以 Business Scope 開頭，當(dāng)看到這個 ETL 的時候就大概知道這個 ETL 也什么業(yè)務(wù)范圍的了。 記?。好?guī)范在哪里都使用。
Solution - Solution 和上面的 Business Scope 的含義也比較類似，但是面要更小一些。通常情況下，會把相同業(yè)務(wù)或者相關(guān)業(yè)務(wù)的 ETL 創(chuàng)建在同一個項目中，這里的解決方案就可以描述為 Business Scope ,而項目的名稱可以描述為 Solution，然后下面會有很多的 ETL SSIS Package。ETL 的命名和它也有關(guān)系，BusinessScopeName_SolutionName_XXX。當(dāng)然，這里的 Solution 和 創(chuàng)建項目中的 Solution 含義上有點(diǎn)小小的區(qū)別。
Solution ETL - Solution 下的具體 SSIS Package，這是真正的運(yùn)行數(shù)據(jù)加載，清洗和轉(zhuǎn)換的包的定義區(qū)域。而日志功能就是圍繞 Solution ETL 來進(jìn)行記錄的。
Data Flow - 數(shù)據(jù)流，數(shù)據(jù)流類型。比如文件的加載有 Input 有 Output 方向的，也有同時兼?zhèn)?Input 和 Output 的。非文件的 ETL 數(shù)據(jù)流類型那就是普通的 Transformation 了。任何的 ETL 無非就是這些類型，將 Data Flow 的類型定義在 ETL 的命名上， BusinessScopeName_SolutionName_DataFlowType_XXX。是不是隨便在上百個或者上千個 SSIS Package 中挑出一個，一看 ETL 命名就大概知道這個 ETL 屬于哪個業(yè)務(wù)范圍，哪個 Solution，是做文件加載還是文件輸出 ？ 這些信息是不是一目了然 ？
Execution Status - 執(zhí)行狀態(tài)，ETL 的執(zhí)行狀態(tài)，就三種 - 成功，失敗和執(zhí)行中。
Configuration - 所有包的配置都應(yīng)該集中管理，不應(yīng)該分散到各個 XML 格式的配置文件中，而應(yīng)該集中到我這里提到的 Configuration 中。
Process Log - 所有包運(yùn)行的記錄也都應(yīng)該集中管理，它們的運(yùn)行記錄也都應(yīng)該集中在一個地方進(jìn)行管理和跟蹤。我見過不同公司不同的項目，每創(chuàng)建一個 ETL 就是一份 XML 配置文件，一個 SSIS 自動生成的 Process Log。當(dāng)整個項目中就幾個 ETL Package 的時候，確實沒有什么問題，完成部署也很快，很容易。但是如果能稍微花點(diǎn)時間認(rèn)真搭建一個屬于自己項目的日志系統(tǒng)，所有人共同遵守的話，我可以說后續(xù)的開發(fā)，維護(hù)，新人學(xué)習(xí)，管理等時間和人力成本將會節(jié)省更多。
Error Log - 伴隨著 Process Log ，但有所區(qū)別，它只記錄錯誤消息。
三. 日志系統(tǒng)的數(shù)據(jù)庫對象
上面的這些角色反映在數(shù)據(jù)庫中就是下面的這些數(shù)據(jù)庫對象了，可以很容易看到它們之間的關(guān)系。
注意 - SSIS Configurations 這張表不是直接創(chuàng)建的，而是第一次在設(shè)計 ETL 模板的時候創(chuàng)建的，后面會介紹到。
詳細(xì)創(chuàng)建這些對象的腳本也提供給大家使用，在此基礎(chǔ)之上可以擴(kuò)展 Auditing 的工具，包括輸入，輸出文件的記錄，Archive 文件的記錄，表記錄的更改條數(shù)，新增條數(shù)，正確率，錯誤率等記錄都可以基于上面的 Process Log 進(jìn)行擴(kuò)展。我可以在以后再單獨(dú)寫一篇 Auditing 方面的內(nèi)容，來介紹如何集成 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
更新結(jié)束 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ā)之初，特別是新項目，按照我們上面提到的內(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
那么一看這個包的名稱，即使是一個新人，經(jīng)過簡單的項目框架培訓(xùn)，是不是一看這個包的名稱就基本知道這個包是用來做什么的了。
下面這個腳本，應(yīng)該在項目開發(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',
- '[email protected]',
- 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',
- '[email protected]',
- 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',
- '[email protected]',
- NULL,
- NULL,
- SYSTEM_USER,
- GETDATE()
- )
- END
有了這些信息，我們就可以開始設(shè)計和開發(fā)我們的 SSIS Package 了。
解決方案名稱可以使用 Business Scope 的名稱 COMMON_BIWORK_LOG，項目名稱就叫做 COMMON_BI。
ETL 名稱就是 COMMON_COMBI_TRANS_ETL_TEMPLATE。
先在包級別定義好這些變量并賦予一定的值，后面我會詳細(xì)解釋到它們各自的用途。
在 Control Flow 中創(chuàng)建這兩個 Execute SQL Task (EST) -
EST_INSERT_START_LOG
EST_UPDATE_END_LOG
保存并運(yùn)行 SSIS Package ，并可以到 Process Log 表中查看結(jié)果。
前兩條是之前我自己測試的日志，第三條可以看到它的日志信息，最主要的就是 START TIME，F(xiàn)INISH TIME 以及 EXECUTE STATUS，1 表示執(zhí)行成功。
還有錯誤處理，對于錯誤的處理應(yīng)該在 SSIS Package 的 Event Handler 中來完成，并且選擇的是 OnError。 OnError 將捕獲所有出錯的事件，那么我們就會做兩件事情。第一件就是在 PROCESS_LOG 中將對應(yīng)的 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 事件。
查看數(shù)據(jù)庫中的日志和錯誤記錄。
那么這樣的一個小型日志框架就算搭建起來了，之后所有的 SSIS Package 都可以使用到這套框架。執(zhí)行 SSIS Package 時，所有的日志，錯誤日志都會集中寫到同一個 Log System 中。這套日志系統(tǒng)雖然看起來功能很簡單，但是能夠?qū)崒嵲谠诘闹С稚习賯€ ETL Package 的日志管理。不會出現(xiàn) 100 個 Package 有 100 套不同日志表的情況，極大的改善了我們維護(hù)，監(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 來完成。當(dāng)然現(xiàn)在在 SQL Server 2012 版本 BIDS 工具里已經(jīng)不需要任何的 XML Configuration File 就可以完成配置了，非常容易。但是，在 JOB 的配置過程中還是需要提供配置的值。
而我希望的是，所有的配置都能夠集中在一張數(shù)據(jù)表中完成，也就是即使以后有幾十個，上百個 SSIS Package 它們的配置也都集中在一張表中完成的。不需要 XML 配置文件，不需要在 JOB 定義 Package 時配置任何參數(shù) - 一旦發(fā)布，在外無參數(shù)配置！
首先，在這里要搞清楚一個概念 - BIWORKLOG 是核心日志數(shù)據(jù)庫，這個數(shù)據(jù)庫部署的位置先要確定好，應(yīng)該部署在 JOB 運(yùn)行時的 SERVER。
在系統(tǒng)環(huán)境變量中定義好 COMMON BIWORKLOG 數(shù)據(jù)庫所在 SERVER 的名稱，數(shù)據(jù)庫的名稱。
添加一個環(huán)境變量配置，選中 COMMON_ETL_LOG_SERVER
將這個環(huán)境變量的值賦值于 PE_COMMON_SERVER_NAME, 注意紅色框內(nèi)的值明顯是我賦值錯了，在環(huán)境變量中它的值是 LOCALHOST。 那什么時候 PE_COMMON_SERVER_NAME 將獲取環(huán)境變量的值呢？ - 在 SSIS Package 運(yùn)行的時候。
當(dāng) SSIS Package 運(yùn)行的時候，包配置將首先完成包配置中各個值的裝配，此時的 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 運(yùn)行的時候，兩個 PE 變量將讀取系統(tǒng)環(huán)境變量的值，然后將兩個系統(tǒng)環(huán)境變量的值成功配置到了 BIWORLOG 日志數(shù)據(jù)庫的連接源了。
第一個配置就算完成了！記得，在完成下面操作的時候一定要重啟一下電腦，因為環(huán)境變量的配置有時需要重啟之后才能生效！
再來看 PC_SOLUTION_ETL_ID 是怎么回事？
每次往 PROCESS_LOG 插入日志的時候就需要提供是哪個 ETL 運(yùn)行的標(biāo)志，我們應(yīng)該這樣來獲取 SOLUTION_ETL_ID。
包括以后，如果我們要使用到另外的數(shù)據(jù)源，比如數(shù)據(jù)庫連接對象等等如何配置呢？ 像這些配置選項也可以集中配置在一張表中來進(jìn)行管理。
仍然打開 Package Configuration，然后在 Configuration Type 中選擇 SQL Server。
Configuration Table 這時選擇 NEW , 它將自動提供創(chuàng)建 SQL Server 配置表的代碼，可以修改一下表名稱。
看到這個界面就知道這個表已經(jīng)在 CM_DB_BIWORKLOG 連接管理器中所表示的 BIWORKLOG 數(shù)據(jù)庫中準(zhǔn)備創(chuàng)建了。
那么以后所有的包配置信息都可以配置在這一張表中，通過什么區(qū)分是哪一個包的配置呢？ 通過 COMMON_COMBI_TRANS_ETL_TEMPLATE 這個包的名稱來區(qū)分，這個值是需要手工寫上去的。
選擇將 PC_SOLUTION_ETL_ID 的 Value 給配置到 SQL Server 表中，這時需要注意良好的命名規(guī)范可以讓你快速的知道哪些值是需要被配置的。
保存這個配置。
查看數(shù)據(jù)庫中的配置，這里只有一個 Configuration Filter，以后每來一個 SSIS Package 就會在這里出現(xiàn)一個 Configuration Filter，配置上百個甚至上千個 ETL 包完全沒有問題。
如果需要更新某一個具體的值的話，需要 Update 就應(yīng)該這樣來更新，再次強(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 的值來自于系統(tǒng)環(huán)境變量，BIWORKLOG 數(shù)據(jù)庫的 SERVER 地址和 DATABASE NAME 都是由這兩個 PE 來賦值的。
PC 的值來自于 BIWORKLOG 中的 SSIS CONFIGURATIONS 表，PC_SOLUTION_ETL_ID 在插入日志和更新日志的時候會反復(fù)用到，它標(biāo)識了當(dāng)前 ETL 的ID。
也就是說沒有 PE 就不會有 BIWORKLOG 的成功連接，沒有 BIWORKLOG 就不會有 PC 在數(shù)據(jù)庫中的配置，沒有 PC 就不會有 Log 日志的插入。
這個配置鏈一定要理解清楚，我們所有的配置值就存在兩個地方，一個是系統(tǒng)環(huán)境變量，這個值需要配置一次以后就不用配了。
以后所有 Package 級別的變量配置就都有 SSIS CONFIGURATIONS 表來維護(hù)，通過 Configuration Filter 來過濾。
這就是這套日志框架為什么可以支持成百上千個 SSIS 日志和包配置的原因。
配置完畢后，再次運(yùn)行 Package ，這時所有變量的值都不是在定義它們時候的值了，而是真正讀取于環(huán)境變量和 SSIS CONFIGURATIONS 表。
六. SSIS Package 模板開發(fā)
其實在我的這篇文章 - SSIS 系列 - 利用 SSIS 模板快速開發(fā) SSIS Package 中已經(jīng)提到了如何使用 SSIS Package 模板快速開發(fā)。
下面就使用上面的模板來演示實際運(yùn)用，并在模板基礎(chǔ)之上來完成一個 SSIS Pakcage 的開發(fā)。
找到我們上面創(chuàng)建的 Package。
我使用的是 Windows Server 2008 R2 操作系統(tǒng)，安裝的是 SQL Server 2008 R2 數(shù)據(jù)庫，默認(rèn)的環(huán)境應(yīng)該是 -
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，看到了這個模板了嗎？
不要著急填寫名稱，而應(yīng)該參照下面規(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,
- &n
分享題目：ETL項目中管理上百個SSIS包的日志和包配置框架
網(wǎng)址分享：http://fisionsoft.com.cn/article/cojjejd.html
