Data Science Lab

SSIS 실행 로그 남기기

-       Version : SSIS Server 2005, 2008, 2008R2, 2012, 2014, 2015

SSIS 패키지를 운영할 때 중요한 요소 중 하나는 로깅이다. SSIS에서는 다양한 로깅 방법을 지원한다.

l  텍스트 파일 – 가장 기본적인 로깅

l  SQL Server 테이블 – 가장 큰 장점은 쉽게 로그 결과를 조회할 수 있다.

l  SQL Server 프로파일러 – 이벤트 프로파일 캡처할 수 있다. 추적에 기록된다.

l  Windows 이벤트 로그

l  XML 파일

[SQL Server 테이블에 로깅]

SQL Server 테이블에 로깅하는 방법에 대해서 알아본다. (다른 옵션들을 선택해도 설정 방법은 유사하기 때문에 응용하여 사용할 수 있다.) 로깅을 테스트하기 위해 SSIS 제어흐름에서 SQL 실행 작업과 매우 간단한 패키지를 생성하였다. 이 SQL 구문은 0으로 나누기하여 오류를 발생시키도록 하였다.

제어흐름에서 마우스 오른쪽 버튼을 클릭하고 로깅을 선택하거나 작업표시줄에서 SSIS 메뉴로 이동하여 로깅을 선택한다.

로깅의 공급자 메뉴에서SSIS log provider for SQL Server 을 선택한다.

로그 공급자를 추가한다. 하나의 패키지에 여러 공급자를 추가하는 것이 가능하다. 로그 테이블을 유지하려는 데이터베이스 연결관리자를 지정한다. 테이블을 지정하는 것은 불가능하다. SSIS에서는 특정 이름으로 테이블을 자동으로 생성한다.

작업의 왼쪽 창에서 작업을 취소하여 구성할 수 있다. 오른쪽 창에서 로그 공급자를 선택해야 한다.

마지막 단계는 로그 공급자가 SQL Server에 기록할 이벤트를 선택한다. 세부정보는 아래 탭에서 선택할 수 있다.

설정이 완료되면 SSIS는 dbo.sysssislog 이름으로 데이터베이스에 테이블을 생성한다.

각 열의 정의는 다음과 같다.

l  ID : 테이블의 기본 ID 열 (기본키)

l  Event : 로그 이벤트 유형 (예: OnError)

l  Computer : 패키지를 실행한 호스트명

l  Operator : 패키지를 실행한 사용자 ID

l  Source : 이벤트를 생성한 작업 또는 패키지명

l  Sourceid : 소스 작업 도는 패키지의 GUID

l  Executionid : 패키지를 실행한 GUID

l  Starttime : 작업의 시작시간

l  Endtime : 작업의 종료 시간

l  Datacode : 태스크 또는 컨테이너의 실행 결과를 포함할 수 있는 임의의 번호 (예 0 성공, 1 실패)

l  Databytes : 로그메시지에 대한 바이트 배열

l  Message : 로그 이벤트 메시지

로그 테이블 조회는 아래 스크립트로 확인할 수 있다.

[스크립트 작업에서 로깅]

일부 작업은 특정이벤트가 포함되어 있는 경우가 있다. 스크립트 작업에서도 ScriptTaskLogEntry 이벤트를 사용하여 로깅을할 수 있다.

실제로 스크립트 작업에서 로깅하려면 세개의 매개 변수를 받아들이는 Dts.log 이벤트를 사용한다.

l  Log message

l  Datacode

l  Databytes

첫 번째 값은 매우 중요하여 나머지 두 값은 더미 값이 될 수 있다.

패키지를 실행할 때 로깅 테이블에서 사용자 지정 로그를 확인할 수 있다.

[참고자료]

https://www.mssqltips.com/sqlservertip/4070/integrated-logging-with-the-integration-services-package-log-providers/

2015-11-09 / 강성욱 / http://sqlmvp.kr

MS SQL Server, MS SQL, SSIS, SSIS 로깅, Integrated Logging, SQL Server Integration Services,ETL, BI

SSIS 패지키의 메타데이터 유효성 검사 중지하기

• Version : SSIS Server 2005, 2008, 2008R2, 2012, 2014

SSIS패키지는 모든 연결 및 관련 구성요소의 메타데이터가 유효한지 확인한다. 이 때문에 SSDT를 실행할 때마다 유효성 검사로 인해 오랜시간동안 기다려야하는 경우가 있다. 패키지내의 작업이 많을수록 해당 작업에 대한 유효성 검사 시간이 오래 걸린다.(길게는 몇 분이상 기다려야 한다.) 이러한 유효성 검사를 중지하는 방법에 대해서 알아본다.

[Work Offline]

SSDT 프로그램의 메뉴바에서 [SSIS] – [Work Offline]를 체크하면 유효성 검사를 방지할 수 있다. 패키지를 열기전에 설정해야 유효성 검사를 방지할 수 있다.

Work Offline를 체크하면 프로젝트.dtproj.user 파일의 속성에 <OfflineMode> 속성이 True로변경된다. 해당 파일을 편집하여 실행해도 유효성 검사를 중지할 수 있다.

[Offline Connection Managers]

SSIS 2012의 새로운 옵션이다. 해당 연결관리자에서 단일 연결에 대해 오프라인으로 작업할 수 있도록 설정할 수 있다. 이 옵션의 단점으로는 패키지를 열어야 옵션을 변경할 수 있다. (패키지를 여는 순간 유효성 검사는 시작된다.) 프로젝트를 닫으면 모든 연결관리자는 온라인 작업으로 재설정 된다.

[참고자료]

http://microsoft-ssis.blogspot.kr/2011/11/long-validation-of-ssis-packages-in.html

2015-06-30 / 강성욱 / http://sqlmvp.kr

SSIS, SQL Server, SSIS Validation, SSIS 유효성 검사, SSIS 커넥션 체크, SSIS패키지 실행

SSIS 로그 보관기간 설정 (CleanUp 기간 설정)

• Version : SSIS Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2012에서 SSIS 패키지를 생성하고 1분마다 실행하도록 Job Agent에 등록하였다. 어느날 SQL Server의 디스크 부족 경고가 발생하여 확인해본 결과 SSISDB가 매우 큰 용량을 차지하고 있음을 알게 되었다.

처음엔 SSIS의 대규모 배치성 작업으로 인한 트랜잭션 로그 증가 때문인것으로 판단하여 SSISDB의 데이터베이스 속성을 Simple(단순)으로 변경하였는데 트랜잭션 로그는 더 이상 쌓이지 않았지만 데이터의 크기는 계속해서 증가하고 있었다.

SSISDB의 각 테이블에 대한 사용량을 확인한 결과 다음과 같은 테이블에서 많은 데이터가 생성되어 있음을 확인할 수 있었다. Internal.event_messages 의 경우 20GB이상 사용하고 있는 상태였다.

(아래 그림은 이미 최소 로깅을 적용한 후의 그림이다.)

SSIS는 실행 될 때 각종 실행 로그를 저장하게 되는데 로그를 보관하는 기간 및 로깅 수준 옵션을 설정하여 일정 기간이 지난 로그 삭제를 진행하거나 불필요한 로그를 쌓지 않게 할 수 있다. 속성을 변경하는 방법으로는 SSIDB에서 마우스 오른쪽을 클릭하여 [속성]을 선택한다.

카탈로그 속성을 확인해보면 [작업 로그] 항목에서 보존 기간 및 로깅 수준을 변경한다. 보존 기간의 경우 기본값으로 [365]일이 지정되어 있다. 로깅 수준은 [기본]으로 되어 있는데 보관주기를 설정하고 (필자의 경우 2일로 설정) 로깅 또한 없음으로 설정하여 로그를 남기지 않았다(단순 작업이어서 로그가 필요 없는 상태였음).

주기적으로 로그 정리를 활성화 하여 사용자가 설정한 기간 이후의 로그는 자동으로 삭제 되도록 하여 로그가 계속해서 증가하는 것을 방지 할 수 있다. 여기서 주의할 점은 대규모 데이터베이스에서 보존 기간을 크게 변경하여 정리할 경우(기존 보존 기간보다 많이 축소하였을 경우) 삭제하는데 오랜 시간이 걸릴 수 있다.

[참고자료]

http://benevold.blogspot.kr/2013/08/ssisdb-performance-problem.html

2015-04-21 / 강성욱 / http://sqlmvp.kr

SQL Server, ETL, SSIS, SSIS 로깅, SSIS 로그 보관기간 설정, SSIS 설정, SSISDB, SSISDB Performance, SSIS Catalog

SSIS 패키지 보호 레벨

• Version : SSIS Server 2005, 2008, 2008R2, 2012

SSIS 패키지를 개발하고 빌드하는 과정에 다음과 같은 오류가 발생 하였다.

우선 패키지의 속성에서 ProtectionLevel의 속성을 확인 하였다.

그리고 프로젝트 레벨에서 프로젝트의 보호 수준 속성을 확인 한다.

프로젝트의 ProtectionLevel과 패키지의 ProtectionLevel 속성이 다르게 지정되어 있어 발생한 에러였다. 프로젝트의 보호 속성에 따라 패키지의 보호 속성을 "EncryptionSensitiveWithUserKey"로 보호 수준을 변경하여 리빌드 결과 정상적으로 패키지가 완성 되었다. SSIS 2012에서 일관성 검사는 프로젝트의 모든 패키지에 적용해야 한다.

[참고자료]

http://intelligentsql.wordpress.com/2013/12/30/ssis-package-has-a-different-protection-level-than-the-project/

2014-03-13 / 강성욱 / http://sqlmvp.kr