Checkpoint를 이용한 패키지 반복 실행 구성

Checkpoint를 이용한 패키지 반복 실행 구성

 

Microsoft Premier Field Engineer

한대성

 

 

SSIS 패키지를 운영하다가 에러가 발생했을 때, 다시 돌리는 방법에 대한 문의가 있어서 간단한 예제로 설명 드리겠습니다.

 

예제 설명을 위해 다음과 같이 테이블을 준비합니다.

 

use tempdb go   CREATE TABLE InputTable (        InputSource varchar(20),        InputData int Check (InputData<>3) -- 3은 입력 불가 제약조건 ) go

 

 

다음과 같은 패키지를 하나 만듭니다.

A.    변수 창에서 InputSource라는 String 변수를 추가합니다.

B.     연결관리자에서 테스트용 DB인 tempdb에 대한 OLE DB 연결을 추가하고, 다음과 같이 네 개의 SQL 실행 작업 태스크를 추가합니다.

C.     각 SQL 실행 태스크에는 다음과 같은 형태로 구성합니다.

Ÿ   SQL Statement

INSERT InputTable values( ?, 1)  -- 첫 번째 SQL 태스크

INSERT InputTable values( ?, 2)  -- 두 번째 SQL 태스크

INSERT InputTable values( ?, 3)  -- 세 번째 SQL 태스크

INSERT InputTable values( ?, 4)  -- 네 번째 SQL 태스크

Ÿ   입력 매개변수 탭(Parameter Mapping)에서 다음과 같이 입력

     

D.    이제, 외부 패키지(부모 패키지)에서 현재 패키지(자식 패키지)에게 InputSource 변수를 넘겨주는 기능을 구현해 봅시다.

Ÿ   상단의 SSIS à 구성(Configuration) 메뉴를 선택합니다.

Ÿ   패키지 구성 사용 체크 박스를 선택한 후, 추가 버튼을 눌러 다음과 같이 구성 설정 창이 나오면, 부모 패키지 변수(parent package variable)을 선택하고, Parent Variable 부분에 InputSource이라고 입력합니다.

Ÿ   다음 버튼을 눌러 입력된 변수가 매핑 될 속성을 지정합니다. 다음과 같이 InputSource 변수의 value 속성에 매핑되도록 설정합니다.

 

Ÿ   구성 설정이 완료된 화면

 

E.     이제 테스트를 수행해 봅시다. InputSource라는 변수에 FromChild라는 값을 입력하고선 패키지를 실행해 봅시다.

InputData라는 열에 3을 입력하지 못하도록 제약 사항으로 설정해 놨기 때문에 위와 같이 세 번째 SQL 실행 작업이 실패하게 됩니다.

 

일단 여기까지 테스트 용 자식 패키지를 완성했습니다.

이제 새로운 패키지를 하나 생성합니다. 이 패키지는 부모 패키지로 앞에서 만든 자식 패키지를 반복해서 호출하는 역할을 수행할 것입니다.

A.    우선 앞에서와 같이 InputSource라는 String형 변수를 추가합니다.

B.     Foreach 루프 컨테이너를 추가한 후, 속성 창을 열어 Collection 탭에서 Enumator를 Foreach Item Enumator로 설정하고, 다음과 같이 값을 입력합니다.

 

C.     Variable Mappings 탭에서 다음과 같이 각 값들이 A단계에서 추가한 변수(InputSource)에 대입되도록 설정합니다.

 

D.    Foreach 루프 컨테이너 내에 패키지 실행 작업을 추가한 후, 앞에서 생성한 패키지를 가리키도록 설정합니다.

 

E.     이제 패키지를 실행시켜 봅니다. 역시나 자식 패키지의 세 번째 작업 때문에 실패가 났습니다. 하지만, 세 번째 작업을 제외한 나머지 작업들은 성공해서 데이터가 입력되었습니다.

 

자, 만약에 이 상태에서 또 패키지가 실행이 된다면 1,2,4의 값은 또 들어가고 3은 여전히 실패하겠지요. 반복해서 실행될 때 또는 자식 패키지만 다시 수행하더라도 패키지가 다시 실행될 때 성공한 작업을 제외한 실패한 작업만 수행하도록 구현하는 것이 본 글에서 설명하는 기능입니다.

 

다시 자식 패키지로 돌아가서, 패키지의 제어 흐름 영역 아무 곳을 선택한 후, 속성 창에서 다음과 같은 속성 값을 설정합니다.

 

그런 다음, 모든 SQL 작업들을 선택한 후, FailPackageOnFailure 속성을 True로 변경합니다.

이제 패키지를 저장하고 부모 패키지를 실행시켜 봅시다.

마찬가지로 에러가 발생했지만, CheckpointFileName 속성에서 지정한 위치에 ChildPackageCheckFile.chk라는 파일이 생성되었습니다.

 

현재 상황을 잠깐 정리하자면, 부모 패키지에서 FromParentLoop1이라는 값을 자식 패키지에게 넘겨주면서 실행을 시켰는데, 자식 패키지의 세 번째 SQL 실행 작업에서는 에러가 발생하였습니다.

 

Q1. 이 상태에서 다시 부모 패키지를 실행시키면?

Ans) CheckFile이 있기 때문에 Checkfile의 내용을 읽어서 실패한 작업 단계인 세 번째 작업만 수행하게 됩니다. 1,2,4의 값이 중복으로 입력되지 않습니다.

 

Q2. 에러가 발생한 부분을 수정하였습니다. 그런데 이 상태에서 자식 패키지만 다시 실행시키면? 즉, 에러가 발생했을 때에는 부모 패키지로부터 InputSource라는 변수의 값에 FromParentLoop1이라는 값을 받아왔었는데, 자식 패키지만 따로 실행시키면 이 값이 없어지지 않나요?

Ans) 자식 패키지만 따로 실행시키더라도 패키지가 에러가 났을 때의 변수 설정이 그대로 유지됩니다.

 

에러가 나는 원인인 제약 조건을 해제하고 자식 패키지만 따로 실행시켜 봅시다.

sp_helpconstraint InputTable GO ALTER TABLE InputTable DROP CONSTRAINT CK__InputTabl__Input__7F60ED59 GO

 

 

패키지가 성공적으로 처리되면서 Check 파일은 삭제됩니다.

 

 

자, 그럼 이걸 어떻게 응용하면 될까요? (이 부분은 설명만 대충 하겠습니다.)

문의하신 고객의 상황은 네트워크 상태가 좋지 않아 간혹 끊어지면서 오류가 발생한다고 합니다. 그래서 다시 패키지를 돌리면 대부분 성공합니다.

 

이를 해결하는 방법은 여러 가지가 있겠지만, 부모 패키지에서 Foreach 루프 컨테이너의 자식 패키지를 호출하는 부분에 Check 파일이 생성되었는지를 확인하는 부분을 구성해서 만들면 될 것 같습니다.

위와 같이 CHK 파일을 검사하는 스크립트 작업을 추가합니다. 간단히. File.Exists(…) 형태의 구문으로 작성 가능하며, CHK 파일이 존재할 때에는 변수에 CHKFileYN값을 True로 설정하고, 파일이 없는 경우에는 False로 설정하는 것이지요. 이 변수의 기본 값은 True입니다.

For 루프 컨테이너에서는 CHKFileYN이 True일 때에만 수행하도록 설정하면 됩니다. 추가로 반복 횟수를 지정할 수도 있겠지요. (예 : 10번 만 Retry 해라)

패키지가 에러가 없이 성공적으로 종료되면 For 루프 컨테이너는 종료되고, Foreach 루프 컨테이너에서 다른 입력 값이 설정되어 패키지가 수행됩니다.

 

복잡하게 보일지 모르겠지만, 구현할 만한 기능입니다.^^