실습 강좌 8 - 커스터마이징 작업 로그 설정

WebLogLoading_8강.dtsx

제 8강 커스터마이징 작업 로그 설정

 

한대성 (admin@sqlleader.com)

에이디컨설팅 / 책임 컨설턴트

SQLLeader.com / 운영자

 

 

SSIS에서는 기본적으로 다양한 형태의 로깅 기능을 제공합니다.

 

 

이러한 기능을 이용해서 패키지가 수행되는 상태를 기록하고 관리하는 것이 간단하기는 하지만, 이번 강좌에서는 이벤트 처리기를 이용하여 원하는 형태의 로깅 정보를 관리하는 프로세스를 구현해 보도록 하겠습니다.

 

 

7강에서 만들었던 패키지를 이용하여 진행하도록 하겠습니다.

 

웹 로그 테이블이 만들어진 DB에 다음과 같은 쿼리를 실행시켜 로깅 정보를 저장할 테이블을 만듭니다.

 

if exists (select * from dbo.sysobjects where id = object_id(N'[PackageHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [PackageHistory] GO   CREATE TABLE [dbo].[PackageHistory](         SEQ bigint identity(1,1) NOT NULL,         [PackageID] uniqueidentifier not null,         [PackageName] [nvarchar](255)NOT NULL,               [ExecStartTime] [datetime] NULL,         [ExecEndTime] [datetime] NULL,         [Status] char(1) NOT NULL DEFAULT('N')         CONSTRAINT [pk__PackagehISTORY] PRIMARY KEY CLUSTERED (         SEQ ASC, PackageName         ) ) ON [PRIMARY] GO   CREATE INDEX IDX_PackageHistory ON PackageHistory(PackageID, PackageName) GO     if exists (select * from dbo.sysobjects where id = object_id(N'[TaskHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [TaskHistory] GO   CREATE TABLE [dbo].[TaskHistory](             [SEQ] bigint identity not null,         [PackageID] uniqueidentifier not null,         [PackageName] [nvarchar](255)NOT NULL,               [TaskID]  uniqueidentifier not null,         [TaskName] [nvarchar](255)NOT NULL,           [ExecStartTime] [datetime] NULL,         [ExecEndTime] [datetime] NULL,         [Status] char(1) NOT NULL DEFAULT('N')         CONSTRAINT [pk__TaskHistory] PRIMARY KEY CLUSTERED (         SEQ         ) ) ON [PRIMARY] GO   CREATE INDEX IDX_TaskHistory ON TaskHistory(PackageID, TaskID) GO

 

PackageHistory 테이블은 패키지가 시작하고 종료할 때의 정보를 기록하는 테이블이며, TaskHistory 테이블은 패키지 내의 개별 작업들이 시작하고 종료될 때의 정보를 기록하는 테이블입니다.

 

※ 참고 패키지 정보와 작업 개체 정보의 로그를 하나의 테이블로 관리해도 되지만, 실제 작업을 모니터링 할 때, 패키지 전체의 성공여부를 확인한 후 문제가 있는 경우에 세부 작업에 대한 정보를 확인하는 행태에 따라 이렇게 분리하였습니다. 테이블 구조는 필요에 따라 다양하게 변경하여 사용할 수 있습니다.

 

패키지에서 이벤트 처리기 탭을 클릭한 후, OnPreExecute 이벤트(①)를 선택하고 작업 영역을 클릭하여 새로운 이벤트 처리 영역(②) 을 추가합니다. 이 때, 실행 파일 부분은 패키지 전체 수준으로 설정되었는지를 확인합니다.(③)

 

 

OnPreExecute 이벤트 처리기 영역에 SQL 실행 작업을 하나 추가하고, 이름을 로그 입력으로 변경합니다. 위의 그림에서 이벤트 처리기의 범위를 지정하는 실행 파일(③) 부분이 패키지 최상위로 선택되어 있기 때문에 로그 입력 작업은 패키지가 시작되거나 패키지 내의 개별 작업 개체 또는 컨테이너가 시작될 때 모두 호출됩니다.

 

작업 편집기에서 Connection을 WebLogTargetTable DB로 지정합니다. 일반적으로 로깅 정보를 저장하는 DB는 운영 DB와 분리해서 관리하지만, 본 예제에서는 편의상 웹 로그 테이블이 저장되는 DB와 동일한 DB로 사용하도록 하겠습니다.

 

SqlStatement에 다음과 같은 쿼리를 입력합니다.

 

DECLARE @PackageID nvarchar(50), @PackageName nvarchar(255), @TaskID nvarchar(50), @TaskName nvarchar(255) SET @PackageID = ? SET @PackageName = ? SET @TaskID = ? SET @TaskName = ?   IF @PackageID = @TaskID /* 패키지의 실행에 대한 입력인 경우*/  BEGIN   INSERT PackageHistory(PackageID, PackageName, ExecStartTime, Status)   VALUES (@PackageID, @PackageName, GETDATE(), 'R')  END ELSE  BEGIN /* 패키지 내의 작업인 경우 */   INSERT TaskHistory(PackageID, PackageName, TaskID, TaskName, ExecStartTime, Status)   VALUES (@PackageID, @PackageName, @TaskID, @TaskName, GETDATE(), 'R')  END

 

 

 

 

매개 변수 매핑 탭에서 다음과 같이 입력 변수를 지정합니다.

 

변수 이름	방향	데이터 형식	매개 변수 이름
System::PackageID	Input	GUID	0
System::PackageName	Input	NVARCHAR	1
System::SourceID	Input	GUID	2
System::SourceName	Input	NVARCHAR	3

 

 

자, 이제 OnPreExecute 이벤트에 대한 작업 프로세스 정의가 완료되었습니다. 패키지가 실행 될 때, 패키지 내의 여러 작업 개체들이 실행될 때마다 위에서 정의된 쿼리가 실행이 되어 조건에 맞는 테이블에 데이터가 입력됩니다.

 

OnPreExecute 이벤트와 동일한 방식으로 OnPostExecute 이벤트에 대해서도 이벤트 처리기를 생성합니다. 이 이벤트 처리기에도 SQL 실행 작업을 하나 추가한 후, 이름을 로그 기록으로 변경하고, 작업 편집기에서 Connection을 지정한 후, SqlStatement에 다음과 같은 쿼리를 입력합니다.

DECLARE @PackageID nvarchar(50), @PackageName nvarchar(255), @TaskID nvarchar(50), @TaskName nvarchar(255) SET @PackageID = ? SET @PackageName = ? SET @TaskID = ? SET @TaskName = ?     IF @PackageID = @TaskID /* 패키지의 실행에 대한 완료인 경우 */  BEGIN   UPDATE A   SET ExecEndTime = GETDATE(), Status = 'Y'   FROM PackageHistory A   WHERE PackageID = @PackageID AND PackageName = @PackageName AND    SEQ = (SELECT MAX(SEQ) FROM PackageHistory (NOLOCK)     WHERE PackageID = @PackageID AND PackageName = @PackageName      AND Status <> 'Y')  END ELSE  BEGIN /* 패키지 내의 작업의 완료인 경우 */   UPDATE A   SET ExecEndTime = GETDATE(), Status = 'Y'   FROM TaskHistory A   WHERE PackageID = @PackageID AND PackageName = @PackageName AND    TaskID = @TaskID AND TaskName = @TaskName AND    SEQ = (SELECT MAX(SEQ) FROM TaskHistory (NOLOCK)     WHERE PackageID = @PackageID AND PackageName = @PackageName      AND TaskID = @TaskID AND TaskName = @TaskName      AND Status <> 'Y')  END

 

 

OnPreExecute 이벤트에서와 마찬가지로 매개 변수 매핑 탭에서 동일한 형태로 매개 변수를 지정합니다.

 

 

 

자, 이제 로깅 기록 설정이 완료되었습니다.

패키지를 실행시킨 후, 테이블에 저장된 데이터를 확인합니다.

 

 

 

 

 

PackageHistory 테이블에는 패키지 단위로 시작 시간 및 종료 시간, 상태 정보가 기록되며, TaskHistory 테이블에는 패키지(WebLogLoading)내의 개별 작업 단위로 시작 시간 및 종료 시간, 상태 정보가 기록됩니다.

필요에 따라 데이터 처리 건수나 처리 파일 명 등 필요한 열 정보들을 추가할 수도 있습니다.