OnError 이벤트 처리기를 이용하여 에러 정보 남기기

20070104_에러정보남기기.dtsx

OnError 이벤트 처리기를 이용하여 에러 정보 남기기

한대성

Microsoft Premier Field Engineer

 

 

 

SSIS에서 하나의 패키지 내에 여러 개의 데이터 흐름 작업을 정의하여 실행 할 때, 여러 개 중에서 하나의 데이터 흐름 작업에서 에러가 발생하면 작업이 멈추게 되는데, 이 때 에러 이벤트를 DB에 저장하고 싶습니다. 이벤트처리기에서 ONERROR 이벤트를 어떻게 처리해야 DB에 해당 변수를 저장할 수 있는지에 대한 간단한 예제나 자료 있으시면 부탁 드리겠습니다. 기존에 올라와 있는 자료만 가지고는 잘 이해가 되지 않아서요...

 

 

이번에 A모 회사에서 SSIS 프로젝트를 하면서 생각했었던 여러 방법들이 있었습니다.

어떻게 하면 에러 로깅을 잘 & 정확히 & 효율적으로 & 간단히 할까 생각을 좀 했었는데 여러 방법들이 있을 것 같더라고요.

 

간단히 먼저 방법만 설명 드리면,

1.       에러가 발생했을 때의 이벤트 처리기인 OnError 이벤트 처리기에서 에러 정보를 테이블에 정보를 넣는 방법 - 위의 질문과 같은 사항이겠지요.

2.       SSIS의 로깅을 이용해서 OnError 이벤트에 대해서만 정보를 저장하는 방법 – 가장 간단하긴 하지만, 작업 개체 명이라든지 변수 등과 같은 추가적인 정보를 저장시킬 수 없어서 뭔가 부족함.

3.       별도의 파일로 남기는 방법 – 이건 1번과 비슷할 수 있습니다. 테이블에 넣는 대신 파일로 떨구는(@.@) 방법으로 수행

 

잠시 삼천포로 빠지겠습니다. 혹시 스크립트 작업(데이터 흐름 변환 작업 내의 스크립트 변환 말고 제어 흐름 작업 내의 작업)에서 OLE DB나 ODBC, ADO.NET 등과 같은 DB 작업을 해 보신 분. 간단하게는 다음의 링크에 있는 [따라하기]처럼 하면 됩니다.

 

그런데… 가급적이면, SSIS에서 정의되어 있는 연결 정보를 그대로 이용하고 싶다면..

데이터 흐름의 스크립트 변환에서는 이러한 작업 형태를 쉽게 구현할 수 있지만… 제어 흐름 영역의 스크립트 작업에서는 안되더라고요~.

제가 몰라서일 수도 있지만.. 여러 날 시도해봐도, 그리고 찾아봐도 안되더라고요..

그래서 내린 결론은. 제어 흐름 영역에 있는 스크립트 작업에서는 SSIS 패키지의 연결 관리자에 정의되어 있는 연결을 이용할 수 없다. (없지 않을까? 없겠지모..) 입니다.

 

갑자기 OnError 이벤트 정보를 다루면서 왜 이런 소리를 할까.. 그냥 해 봤습니다..참고하시고..

처음에 구현하려고 했었던 방법은, OnError 이벤트 처리기 부분에 스크립트 작업을 하나 박아 넣고(^^), 스크립트 작업에서 에러 정보가 담긴 변수들을 좌르르 읽어 들여(DTS.Variable 를 쓰면 되겠지요~) 바로 로그 테이블에 Insert 하기.

 

안되더군요.. 그 이유가 바로 위에서 말한 것 때문입니다. 그렇다고 스크립트 작업 내에다가 DB 연결 문자열을 하드 코딩해서 넣기도 그렇고.

 

그래서 다른 무쉭한 방법을 생각했습니다. (물론 이것 때문에 또 새로운 기능을 생각할 수 있게 되어 얻은 것이 많았습니다.)

『테이블에 바로 못 넣는다면, 파일에 해당 정보를 떨어뜨리고, 그 파일을 데이터 흐름 작업을 이용해서 로그 테이블에 넣자. 』 라는 것이지요. (구현은 직접 해보세요~..)

그런데.. 이 때 주의할 것 두 가지.

1. 우선 패키지가 여러 개가 돌 수 있기 때문에 떨어뜨리는 로그 파일은 다 달라야 한다는 거.

그리고, 로그 파일이기 때문에 지저분하게 계속 발생시키면 안되겠지요.

2. 또 다른 고려 사항은, 만약 패키지가 다른 서버로 갔을 때 문제없이 돌려면?

예를 들어, A라는 서버에서 F:\SSISLog\ 라는 폴더에 로그를 남기도록 설정했는데, 이 패키지들을 테스트 서버 또는 다른 운영 서버로 옮긴다고 했을 때, 그 서버에 F:\ 드라이브가 없다면?? 에러 나겠지요~^^ 에러 안 나도록 파일 위치를 잡아야 하는 문제가 또 있겠지요.. 해법은? 여러 가지가 있을 수 있습니다..이것도 직접~

 

실컷 이런 식으로 구현해 보니깐 잘 돌아가긴 하다가 가끔씩 이상 에러가 발생하기도 합니다..(발생 안 하면 다행이고요~^^)

 

 

이제까지가 잡설이고요..(그래도 찬찬히 보시면 패키지 작성하실 때 참고될 부분들이 조금은 있지 않을까 합니다~^^)

갑자기..허무하게도 매우 간단한 방법이 있었습니다..

 

 

 

다음과 같이 에러 정보 기록용 테이블을 만듭니다. 여기서는 데모이기 때문에 아주 간단히 만들겠습니다.

본 예제에서는 tempdb 에다가 만들었습니다.

CREATE TABLE [dbo].[ErrorHistory](         [Seq] [int] IDENTITY(1,1) NOT NULL,           [PackageID] [varchar](45) COLLATE Korean_Wansung_CI_AS NOT NULL,         [PackageName] [varchar](200) COLLATE Korean_Wansung_CI_AS NULL,         [TaskID] [varchar](45) COLLATE Korean_Wansung_CI_AS NULL,         [TaskName] [varchar](200) COLLATE Korean_Wansung_CI_AS NULL,         [TaskDescription] [varchar](200) COLLATE Korean_Wansung_CI_AS NULL,         [ErrorCode] [int]NULL,         [ErrorDescription] [varchar](500) COLLATE Korean_Wansung_CI_AS NULL,         [ErrorTime] [datetime] NULL  CONSTRAINT [pk__ErrorHistory] PRIMARY KEY CLUSTERED (         [Seq] ASC ) ) GO

 

패키지를 만들고선, OnError 이벤트 처리기에 SQL 실행 작업을 하나 추가합니다.

 

 

 

SQL 실행 작업의 편집기에서 연결을 선택하고, 다음과 같은 쿼리를 SQLStatement에 입력합니다.

INSERT ErrorHistory (PackageID, PackageName, TaskID, TaskName, ErrorCode, ErrorDescription, ErrorTime) SELECT ?, ?, ?, ?, ?, ?, ?

 

그리고.. ByPassPrepare 옵션을 True로 변경합니다.(★★★★★)

매개 변수 매핑 탭에서 이제 다음과 같이 찬찬히 입력합니다.

여기서.. NVARCHAR, VARCHAR 등등.. 잘 입력하시기 바랍니다. 이 부분에 대해서는 별도로 언급하도록 하겠습니다..추후에

 

 

이제 테스트로 에러 발생해 봅시다.

 

 

에러가 발생했을 때, 이벤트 처리기 한 번 확인해 보면.. 잘 들어갔네요~^^

 

 

 

이와 같은 방법으로 여러 형태로 확장 해 보시기 바랍니다.

감사합니다...저도 위와 같은 방법으로 테스트를 진행해 보았었는데, 안되길래, 다른 문제가 남아있다고 생각하고 질문을 올린거였는데...역시 별표 다섯개짜리 'ByPassPrepare 옵션'을 'True로 변경'...요게 있었네요....테스트 한 번 해 보겠습니다....감사합니다....

간단하게 따라해 보았는데요...
오류: 0xC002F210(SQL 실행 작업, SQL 실행 작업: 다음 오류로 인해 쿼리 "insert into OnError(errDesc) select ?"을(를) 실행하지 못했습니다: "문이 종료되었습니다.". 가능한 실패 원인: 쿼리에 문제가 있거나 "ResultSet" 속성, 매개 변수 또는 연결을 올바르게 설정하지 않았을 수 있습니다.
이런 오류가 나오네요...
매개변수 매핑은 System::ErrorDescription 방향은 input 타입은 nvarchar 매개변수이름은 0 이렇게 했구요.
그런데 실행쿼리문에서 insert into ..... select ? 라고 했는데... 여기서 이 ?(물음표)가 매개변수 0 이라는것은 어떻게 알 수 있는거죠?
매개변수 0 이라고 하는것과 select 에서 사용한 ? 가 서로 동일한것을 가리키는것은 확실한가요?

자답이네요...
위의경우 문제는 컬럼에 varchar(64)로 정한게 문제였습니다. 데이터가 잘려서 문제가 된거였구요.. 255정도로 늘리니깐 이상없군요
그리고 매개변수매핑시 변수이름은 ?,?,? ... 물음표 순서대로 0,1,2 ... 주면 되는것 같네요.
하여튼 강의 잘 보고 있습니다. 올려주셔서 감사~

우선, 서비스팩은 설치하셨는지요..SP1 이상이 설치되어 있어야 매개변수를 사용하는 쿼리가 제대로 작동합니다

그리고, 매개 변수를 지정하는 방법은 Provider에 따라 다른데, 위의 예제와 같이 OLE DB Provider인 경우에는 차례대로 앞에서부터 0, 1, 2,.. 순으로 설정됩니다

개개변수매핑에서 방향을 INPUT으로 하잖아요. 반대로 OUTPUT은 어떻게 사용하나요?
즉, 쿼리에서 SET @VAR1 = (SELECT MAX(COL1) FROM TAB1) 이렇게 구한 @VAR1을
SSIS 사용자변수로 할당하는 방법이요....


결과 집합을 이용하셔서 설정하셔면 됩니다.
http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=342
글을 참고하시기 바랍니다.