ここしばらくの間、SSISのロギングシステムについての記事を書く準備をしていましたが、一度にすべてを行うのは難しいことがわかりました。というのも、この記事の内容は拡張性が高すぎるため、拡張部分が増えるたびに、より多くのコードや例、理論的なサポートが必要になるからです。そのため、より一般的だと思われるLOGの部分を選び、ここで共有することで、ETLロギングシステムを設計する際のインスピレーションと助けになればと思います。
もちろん、ロギングと監査を区別することは重要です。ロギングは主に何が起こったかを記録するために使用され、監査はデータ量の生成プロセスを記述することに重点を置いています。
この記事では主にログ部分について説明します。
まず、いくつかの質問にお答えしましょう。
1.プロジェクトで稼働するETLパッケージが数百あると仮定した場合、これらのパッケージのログを統一的に管理する方法は？
2.現在オンラインで稼働しているETLパッケージの数は？ 稼働期間は？ どのパッケージが最も長く稼働し、どのパッケージが最も稼働していないか？ プロジェクトマネージャーには、これらのKPIを反映したチャートが必要です。
3.1日に実行されるETLパッケージの数は？ テスト環境、開発環境でいくつ実行されていますか？
4.各パッケージの実行状況(成功、失敗、失敗の理由など)を素早く確認する方法は？
5.各パッケージには設定ファイルがあります。 これらの設定ファイルの内容は何ですか？設定されるパラメータは何ですか？
6.それぞれのパッケージが属するおおよそのセクターは？事業範囲は？ これらのパッケージが故障した場合、誰が修理に出しますか？ 誰が開発しましたか？
7.ファイルの読み込みと出力にはどのパッケージが使われ、一般的なデータ変換にはどのパッケージが使われますか？ 入力ファイルはどこにありますか？ 出力ファイルはどこにありますか？
8.ランダムなSSISパッケージの名前から、それがおそらく何をするかわかりますか?
9.毎週のプロジェクトミーティングで、上司の質問に対する答えやデータ、グラフを考えていますか？
.........
実際には、これらの問題を適切に考慮する必要があり、完全にプロジェクト開発の初期にすることができます、1日または数日を費やして、現在のプロジェクトのロギングシステムを構築し、適用され、その後、ロギングシステムの設計とレポートの開発を完了するために一定の時間を費やしています。上記の問題を解決する上で、私はその利点は、フロントラインで働いている開発者だけでなく、自分の作業効率を向上させることができますだけでなく、後の段階でETLの工数と時間コストのメンテナンスと操作の多くを減らすことができると信じています。
本稿は以下のセクションに分かれます：
- ETLプロジェクト内のロギングシステムの場所
- ログシステムの役割構成
- ログシステムのデータベースオブジェクト
- SSISパッケージでのロギングシステムの使用
- ロギングシステムにおけるSSISパッケージ構成管理の統合
- SSIS Package テンプレート開発
- ロギングシステムのレポート作成
I. ETLプロジェクトにおけるロギングシステムの位置
ロギング システムとは、ログ データを維持・管理するテーブルとビュー、またはストアド プロシージャを持つデータベースのことです。ETL プロジェクトにおけるその位置づけは、BI プロジェクトにおけるステージング データベース、DW データベース、およびさまざまなトランザクション データベースなど、他のデータベースから独立している必要があります。
サーバー内には、ビジネス上の理由から1つ以上のステージングデータベースやDWデータベースがシステム内に存在する場合がありますが、ログデータベースは1つだけです。このログデータベースは、ETLパッケージを含むすべてのプロジェクトの真の中核であり、各ETLパッケージ内のすべてのログステータス、パッケージ構成、トピック分割、その他の情報およびコンテンツを管理および維持します。 これらのETLが行うことは、様々なデータソースや宛先データベースにデータを抽出、変換、保存することであり、すべての操作記録はログシステムに保存されます。
このログデータの名前がBIWorkLogだとすると、もちろんブログ界には私のIDであるBIWorkもありますが、文字通りの意味であるBI Work Logの方がわかりやすいですね :)。
II. ログシステムの役割構成
役割分担というのは、ロギング・システムで果たす役割のことです。
ビジネススコープ - 私はこれをビジネス境界と定義します。何百ものSSIS Packagesが開発され、ETLシステムにデプロイされたと仮定すると、これらのPackageはマーケティング部門や財務部門といった異なるグループから提供されたものでなければなりません。当然のことながら、マーケティング部門のビジネスはマーケティング部門のETL要件を中心に、財務部門のビジネスは財務部門を中心に展開されます。もちろん、別の言い方をすれば、開発プロジェクトで要件定義を行う際には、モジュール分割が行われ、業務トピックや境界も定義されることになります。ここでいうビジネススコープとは、ETLの役割のことであり、ビジネススコープはETLの設計プロセスを通して使用されます。例えば、ETLの命名がBusiness Scopeで始まっていれば、ETLを見たときに、そのETLがどのようなビジネススコープに対応するものであるかがわかります。 覚えておいてください：命名規則はあらゆるところで使用されています。
ソリューション - ソリューションは、上記のビジネススコープと似ていますが、スコープは小さくなります。通常、同じビジネスや関連するビジネスのETLは同じプロジェクトに作成され、ここでソリューションはBusiness Scopeと記述することができ、プロジェクト名はSolutionと記述することができ、その下に多くのETL SSIS Packagesがあり、ETLのネーミングもそれに関連して、BusinessScopeName_SolutionName_XXXとなります。もちろん、ここでのSolutionと、作成したプロジェクトのSolutionとでは、少し意味が違います。
Solution ETL - Solutionの下にある特定のSSISパッケージで、データのロード、クレンジング、変換を実行するパッケージを実際に定義する領域です。ロギング機能はソリューションETLを中心としています。
Data Flow - データの流れ。例えば、ファイルは入力と出力の両方向、または入力と出力の両方向で読み込むことができます。ファイル以外のETLデータフロータイプは、通常のトランスフォーメーションです。データフローのタイプは、ETLのネーミング、BusinessScopeName_SolutionName_DataFlowType_XXXで定義されます。 何百、何千ものSSISパッケージの中から1つを選ぶことは可能でしょうか、ETLのネーミングを見れば、次のことがわかります。どのETLがどのビジネススコープに属するのか、どのソリューションに属するのか、ファイルロード用なのかファイル出力用なのか。 この情報は一目瞭然でしょうか？
実行ステータス - 実行ステータスには、成功、失敗、実行中の3種類があります。
コンフィギュレーション - すべてのパッケージのコンフィギュレーションは、個々の XML フォーマットのコンフィギュレーションファイルではなく、ここで述べたコンフィギュレーションで一元管理されるべきです。
プロセスログ - すべてのパッケージ実行のログも一元管理されるべきであり、それらの実行ログは一箇所で管理、追跡されるべきです。私は、作成されたすべてのETLがXMLコンフィギュレーションファイルとSSISが自動生成したプロセスログであるような、さまざまな会社のさまざまなプロジェクトを見てきました。しかし、もしあなたが自分のプロジェクトのためにログシステムを構築するために少しの時間を費やすことができ、全員がそれに従うことができるならば、その後の開発、メンテナンス、新人の学習、管理、その他の時間と人的コストはさらに節約されるでしょう。
エラーログ - プロセスログに付随するものですが、エラーメッセージのみを記録するという違いがあります。
III. ログシステムのデータベースオブジェクト
これらのロールは、以下のデータベース・オブジェクトとしてデータベースに反映され、その関係を簡単に確認することができます。
注意 - SSIS Configurationsテーブルは直接作成されるのではなく、ETLテンプレートの設計中に初めて作成されます。
また、これらのオブジェクトを作成するための詳細なスクリプトも提供されており、これをベースに、入出力ファイルのロギング、アーカイブファイルのロギング、変更数、新規エントリ数、正答率、エラー率などのテーブルロギングなど、監査ツールを拡張することができます。これらの監査データを記録するためにSSIS独自のログを統合する方法については、後日監査に関する別記事で紹介します。
USE BIWORKLOG
GO
----------------------------------------------------------------------
-- Create BIWORK Log System
-- by BIWORK at http://..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://..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
終了ログ更新用ストアド・プロシージャ - USP_COMMON_COMBI_UPDATE_END_LOG
USE BIWORKLOG
GO
----------------------------------------------------------------------
-- USP_COMMON_COMBI_UPDATE_END_LOG
-- by BIWORK at http://..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://..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#
IV. SSISパッケージでのロギングシステムの使用
すべてのプロジェクト、特に新規プロジェクトの最初に行うべきことは、前述のように、ビジネススコープ、ソリューション、ソリューションETLを定義することです。
連続する定義。
ビジネススコープ - COMMON_BIWORK_LOG , ショートネーム - COMMON
ソリューション - COMMON_BI、短縮名 - COMBI
フルネーム - COMMON_COMBI_TRANS_ETL_TEMPLATE
その後、このパッケージの名前を見てみましょう、新人でも、簡単なプロジェクトフレームワークのトレーニングの後、このパッケージの名前を見ていない基本的にこのパッケージが何をするために使用されるかを知っています。
以下のスクリプトは、プロジェクト開発中に実行されるべきもので、不足しているものを定義します。
USE BIWORKLOG
GO
----------------------------------------------------------------------
-- Create BIWORK Log System
-- by BIWORK at http://..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
この情報をもとに、SSISパッケージの設計と開発を開始します。
ETL名はCOMMON_COMBI_TRANS_ETL_TEMPLATEです。
これらの変数はパッケージレベルで定義され、値が割り当てられます。
コントロールフローでSQL実行タスクを2つ作成します。
est_insert_start_log
est_update_end_log
SSISパッケージを保存して実行すると、プロセス・ログ・テーブルで結果を確認できます。
最初の2つは私自身のテストログで、3つ目はそのログ情報を見ることができます。最も重要なのは、START TIME、FINISH TIME、EXECUTE STATUSです。
エラー処理もあり、これはSSISパッケージのイベントハンドラで行うべきで、その選択肢はOnErrorです。 OnErrorはうまくいかないすべてのイベントをキャッチし、2つのことを行います。1つ目は、PROCESS_LOGの対応するPROCESSステータスを0から-1に変更することです。もう1つは、エラー・メッセージをエラー・ログに書き込むことです。
est_update_end_log
est_insert_error_log
エラーのSQL実行タスクをコントロール・フローに追加してテストします。
PACKAGEを保存して実行するとエラーが発生し、ON ERRORで捕捉されます。
イベントハンドラの OnError イベント。
データベースのログとエラーレコードを表示します。
その後、小さなロギングフレームワークがセットアップされ、すべてのSSISパッケージはこのフレームワークを使用することができます。SSISパッケージを実行すると、すべてのログとエラーログが同じログシステムに書き込まれます。このログシステムは非常にシンプルに見えますが、実際には何百ものETLパッケージのログ管理をサポートすることができます。100のパッケージで100の異なるログテーブルが存在することはなく、ETLの保守、監視、管理のプロセスが大幅に改善されます。
同時に、このフレームワークに基づいて他のタスクコンポーネントを開発する場合、何か問題が発生するたびに実行結果を開いてエラー情報を探す必要はありません。今はエラーログを照会するだけで、どのコンポーネントでどのようなエラーが発生したかがすぐにわかります。
#p#
V. ロギングシステムにおけるSSISパッケージ構成管理の統合
最初のいくつかの変数を覚えていますか？
PE - Package Environment (パッケージ環境), システム環境におけるパッケージの設定です。
PV - Package Variable、設定不要、ETL Package実行時のみ使用されます。
より一般的なSSISパッケージの構成は、多くの場合、XML構成ファイルで行われます。もちろん、BIDSツールのSQL Server 2012バージョンでは、構成を完了するためにXML構成ファイルを必要としない、それは非常に簡単です。しかし、まだJOB構成プロセスで構成値を提供する必要があります。
つまり、何十、何百のSSIS Packageがあったとしても、それらの構成は1つのテーブルになります。XML設定ファイルは必要ありませんし、JOBでPackageを定義するときにパラメータを設定する必要もありません！
BIWORKLOGはコア・ログ・データベースであり、このデータベースの配置場所を最初に決定し、JOBランタイム・サーバーに配置する必要があります。
システム環境変数で、COMMON BIWORKLOG データベースが配置されている SERVER の名前を定義します。
環境変数の設定を追加し、COMMON_ETL_LOG_SERVERをチェックします。
この環境変数の値を PE_COMMON_SERVER_NAME に代入します。赤枠の値は明らかに環境変数に代入する値として間違っていることに注意してください。 では、PE_COMMON_SERVER_NAME はいつ環境変数の値を取得するのでしょうか? - SSISパッケージが実行されているときです。
SSISパッケージが実行されると、まずパッケージ構成の値の組み立てが完了し、その時点でPE_COMMON_SERVER_NAMEはシステム環境変数COMMON_ETL_LOG_SERVERの値、つまりLOCALHOSTを受け入れます。
同じ理由で、PE_COMMON_DATABASE_NAMEを設定してください。
この2つが設定されているシステム環境変数です。
この方法で、SSISパッケージが実行されると、2つのPE変数がシステム環境変数の値を読み取り、2つのシステム環境変数の値がBIWORLOGログデータベースの接続ソースに正常に設定されます。
最初の設定は完了です！環境変数の設定を有効にするには再起動が必要な場合があるので、以下の操作が終わったらコンピュータを再起動することを忘れないでください！
PC_SOLUTION_ETL_IDをもう一度見てみましょう。
PROCESS_LOG にログを挿入するたびに、どの ETL が実行されているかを示す必要があります。
後で、データベース接続オブジェクトなど、別のデータソースに使用する場合など、どのようにそれを設定するには？ これらのような設定オプションは、管理するテーブルで一元的に設定することもできます。
パッケージ構成を開き、構成タイプでSQL Serverを選択します。
この時点でNEWを選択すると、自動的にSQL Server構成テーブルを作成するためのコードが提供されます。
この画面を見ると、CM_DB_BIWORKLOG Connection Manager で表される BIWORKLOG データベースにテーブルを作成する準備ができたことがわかります。
このテーブルで、今後すべてのパッケージ構成情報を構成できます。 COMMON_COMBI_TRANS_ETL_TEMPLATEはパッケージの名前で、この値は手動で記述する必要があります。
PC_SOLUTION_ETL_ID の値を SQL Server テーブルに構成する場合、適切な命名規則を使用することで、どの値を構成する必要があるかをすぐに確認できるようにすることが重要です。
この設定を保存します。
データベースの構成を確認すると、ここには1つのConfiguration Filterしかなく、今後登場するすべてのSSISパッケージに対して、ここにConfiguration Filterがあるので、何百、何千のETLパッケージを構成しても問題ありません。
特定の値を更新する必要がある場合は、Update する必要があります。 繰り返しになりますが、BIWORKLOG ロギング システムへの読み取り/書き込みアクセスは、非常に非常に高くなければなりません。
UPDATE [dbo].[SSIS CONFIGURATIONS]
SET ConfiguredValue = (
SELECT ETL_ID
FROM dbo.SOLUTION_ETL
WHERE ETL_PACKAGE_NAME = 'COMMON_COMBI_TRANS_ETL_TEMPLATE'
)
WHERE ConfigurationFilter = 'COMMON_COMBI_TRANS_ETL_TEMPLATE'
AND PackagePath = '\Package.Variables[User::PC_SOLUTION_ETL_ID].Properties[Value]'
SELECT * FROM [dbo].[SSIS CONFIGURATIONS]
すべてのコンフィギュレーション・プロセスを再確認
PE値もPC値もここでは関係ありません。
PE の値はシステム環境変数に由来し、BIWORKLOG データベースの SERVER アドレスと DATABASE NAME はこの 2 つの PE によって割り当てられます。
PC_SOLUTION_ETL_ID は、ログの挿入と更新時に繰り返し使用され、現在の ETL の ID を識別します。
つまり、PEがなければBIWORKLOGの接続も成功せず、BIWORKLOGがなければデータベース内のPCの設定もできず、PCがなければログ・ログの挿入もできません。
このコンフィギュレーションチェーンを明確に理解する必要があります。すべてのコンフィギュレーション値は2つの場所に存在し、1つはシステム環境変数です。
これが、このロギングフレームワークが何百ものSSISログとパッケージ構成をサポートできる理由です。
一度構成された後、Packageを再度実行すると、すべての変数は定義されたときの値ではなく、実際に環境変数とSSIS CONFIGURATIONSテーブルから読み込まれます。
VI. SSISパッケージテンプレートの開発
以下は、上記のテンプレートの実用的なアプリケーションのデモンストレーションと、テンプレートに基づいたSSIS Pakcageの開発です。
上記で作成したパッケージを見つけてください。
Windows Server 2008 R2を使用しており、SQL Server 2008 R2データベースがインストールされています。
C:¥Program Files¥Microsoft Visual Studio 9.0¥Common7IDE¥PrivateAssemblies¥ProjectItems¥DataTransformationProjectDataTransformationItems
BIDS開発ツールを再起動すれば、このテンプレートを使って小さなSSIS Packageを開発する準備ができました。
項目名を選択し、右クリック「追加」-「新規項目」、このテンプレートが表示されていることに注意してください。
慌てて名前を記入するのではなく、以下の仕様コードを参考にしてください。
-- Set the SOLUTION ETL ID
IF NOT EXISTS (
SELECT ETL_ID
FROM dbo.SOLUTION_ETL
WHERE ETL_NAME = 'ETL_TEMPLATE_TEST'
)
BEGIN
INSERT INTO dbo.SOLUTION_ETL
(
SOLUTION_ID,
FLOW_TYPE_ID,
ETL_NAME,
ETL_PACKAGE_NAME,
ETL_DESC,
ETL_FST_OWNER,
ETL_FST_OWNER_EMAIL,
ETL_SEC_OWNER,
ETL_SEC_OWNER_EMAIL,
CREATE_USER,
CREATE_TIME
)
VALUES
(
1, -- SOLUTION ID
20, -- Flow type is common data transformation
'ETL_TEMPLATE_TEST',
-- SSIS Package naming rule [SCOPE_SHORT_NAME]_[SOLUTION_SHORT_NAME]_[FLOW_TYPE]_[ETL_NAME]
'COMMON_COMBI_TRANS_ETL_TEMPLATE_TEST',
'ETL log template package',
'BIWORK',
'biwork@126.com',
NULL,
NULL,
SYSTEM_USER,
GETDATE()
)
END
その名前でパッケージを作成しますが、急いで実行しないように注意してください。というのも、PC_SOLUTION_IDは上記の2と表示され、SSIS CONFIGURATIONSテーブルのConfiguration Filterは明らかに異なるからです。
PC_SOLUTION_ETL_IDの値を2に設定すると、しばらくしてパッケージ構成を変更したときに、新しい構成フィルタを最初に作成したときにこの値がデータベースに書き込まれます。
パッケージSQLSERVER CONFIGを変更すると、ここの構成フィルタが新しいパッケージ名COMMON_COMBI_TRANS_ETL_TEMPLATE_TESTに変更されます。
データベース・テーブル「SSIS CONFIGURATIONS」を保存して表示すると、この構成は完了です。
後で何らかの変数設定が必要になった場合、このようにテーブルで一元管理することで、XMLファイルに変数を設定する必要がなくなりますが、一つには、設定内容の確認が不便であること、もう一つは、ETLの数が増えてくると、XML設定ファイルの管理や設定に気を配らないと、カオスになりやすいことです。
#p#
VII. ログシステムのレポート作成
上記のテーブルオブジェクトとログレコードを使用すると、一般的に使用されるログレポートを、より詳細なカスタマイズを行うことができます。
以下、ランダムに2つか2つのレポートを書きますが、実際にできるレポート、役に立つレポートはたくさんあります。
冒頭の問いに立ち返れば、その答えとなるような文章を書くことは可能でしょうか？
1.プロジェクトで稼働するETLパッケージが数百あると仮定した場合、これらのパッケージのログを統一的に管理する方法は？
2.現在オンラインで稼働しているETLパッケージの数は？ 稼働期間は？ どのパッケージが最も長く稼働し、どのパッケージが最も稼働していないか？ プロジェクトマネージャーには、これらのKPIを反映したチャートが必要です。
3.1日に実行されるETLパッケージの数は？ テスト環境、開発環境でいくつ実行されていますか？
4.各パッケージの実行状況(成功、失敗、失敗の理由など)を素早く確認する方法は？
5.各パッケージには設定ファイルがあります。 これらの設定ファイルの内容は何ですか？設定されるパラメータは何ですか？
6.それぞれのパッケージが属するおおよそのセクターは？事業範囲は？ これらのパッケージが故障した場合、誰が修理に出しますか？ 誰が開発しましたか？
7.ファイルの読み込みと出力にはどのパッケージが使われ、一般的なデータ変換にはどのパッケージが使われますか？ 入力ファイルはどこにありますか？ 出力ファイルはどこにありますか？
8.ランダムなSSISパッケージの名前から、それがおそらく何をするかわかりますか?
9.毎週のプロジェクトミーティングで、上司の質問に対する答えやデータ、グラフを考えていますか？
実際、上記のログシステムに基づいて、多くのことを行うことができ、特にAUDITINGコンテンツは非常に豊かに拡張することができます。この部分は、ログファイルの入力と出力の場所、インポートおよびエクスポートされたレコードの数、テーブルレコードの数、成長の数、変更の数など、上記のLog Systemに統合することができ、すべての使用するLog Systemを構成するために統合することができます。
ロギングシステム＋監査システムは、ETLパッケージを維持・管理するための完全なフレームワークを構成しており、開発、テスト、オンラインメンテナンスやチューニングにおいて非常に重要です。
次回は監査について詳しく書きたいと思います。
注目のポイント
1.最後に、COMMON BIWORK LOGは、権限を制御する必要があります、少数の人々だけがコア設定テーブルを変更することが許可され、他の人が唯一のクエリをすることができ、同時にバックアップ作業の良い仕事を行う必要があります強調しなければなりません。
2.システム環境変数の設定はサーバーを再起動する必要があり、多くのオンライン環境では難しいかもしれないので、柔軟に変更することができます。 SQL Server 2008環境ではXML Package Configuration、2010と2012開発環境ではPackage Parameterを利用して設定することができ、これも非常に便利で柔軟です。
3.子親パッケージをサポートしていない場合、子親パッケージの呼び出しをサポートする必要がある場合は、テーブル構造を変更し、PROCESS_LOGにPARENT_LOG_ID列を追加し、対応するプロシージャを変更することができます。
私は、いくつかの重要なポイントを明確にするためにベストを尽くしたつもりですし、注意深く読んでいただければ非常に理解しやすいと思います。
あなたがダウンして行うには、この記事に従っている場合は、30分以上の小さなBIWORKLOGフレームワークを構築するために、実際のプロジェクトによると、拡張し、独自の機能を充実させる必要があります。
何百ものETLを管理するために、ロギングシステムを統一することは可能かという質問に対して、私は確信を持って答えることができます：それは、その場所自体でログを記録するために使用されるだけなので、問題はありません。統一管理の利点は、命名規則やパッケージ構成を含め、すべてのETLの設計をトップダウンで標準化できることです。私は、数百のSSISパッケージがあるETLプロジェクトが3年間よく管理されているのを見たことがありますし、数十のETLしかないけれども、ロギングと構成が非常に混乱しているプロジェクトも見たことがあります。
この小さな枠組みがお役に立てば幸いです！もっとシェアしてください！
