SQL Agent에서 SSIS 패키지가 실행되지 않는 문제

SQL Agent에서 SSIS 패키지가 실행되지 않는 문제

 

2007.05.14 Updated Version : http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=1238

 

 

현상

로컬 PC 또는 서버에서는 패키지를 실행하면 제대로 수행이 되는데, 패키지 파일(*.dtsx)을 서버에 저장한 후 SQL Agent에 등록하여  실행하면 에러가 발생할 수 있습니다.

다음과 같이 개발 환경에서 직접 패키지를 실행하면 정상적으로 수행됩니다.

 

이 패키지를 SQL Agent에 다음과 같이 등록한 후 실행하면 에러가 발생합니다.

 

 

 

원인

SSIS 패키지의 기본 보안 수준인 ProtectionLevel은 EncryptSensitiveWithUserKey로 설정이 되어 있습니다.

이 값은 패키지의 제어 흐름 영역에서 빈 영역을 클릭한 후, 오른쪽에 있는 속성 창에서 확인할 수 있습니다.

EncryptSensitiveWithUserKey는 중요한 데이터(Sensitive Data)를 현재 패키지가 개발되는 사용자 프로필 기반의 키를 이용하여 암호화 한다는 의미입니다. 여기서 말하는 중요한 데이터란 DB의 연결 정보 또는 패스워드, XML의 노드 정보 등과 같은 값입니다. 따라서 윈도우 인증이 아닌 패스워드를 이용한 OLE DB 연결이나 XML 작업등이 포함된 경우, 중요한 데이터가 패키지에 포함된 상태이기 때문에 패키지가 암호화 됩니다.

수동으로 실행할 때에는 패키지가 암호화된 개인키와 실행 환경의 개인키가 동일하기 때문에 정상적으로 실행됩니다. 하지만, 서버로 파일을 복사한 후, SQL Agent에서 실행하거나, 현재의 개발 PC가 아닌 다른 곳에 패키지를 복사해서 수행하게 되면, 패키지가 암호화된 개인키와 실행 환경의 개인키가 다르기 때문에 오류가 발생합니다.

해결 방안

방법 1) 패키지의 ProtectionLevel을 EncryptSensitiveWithUserKey 대신, EncryptSensitiveWithPassword 또는 EncryptAllWithPassword로 설정한 후, PackagePassword 항목에 암호를 설정합니다.

이런 방식으로 저장하게 되면 패키지의 중요한 정보 또는 패키지 정보 전체가 개인키 대신 암호를 기반으로 암호화 시키게 됩니다.

이후, SQL Agent에서 이 패키지를 실행하기 위해 설정하는 단계에서는 다음과 같은 암호 입력 창이 나타납니다.

(일반 탭에서 실행할 패키지를 지정한 후, 다른 탭을 클릭할 경우 나타납니다.)

 

위와 같은 패키지 암호 설정 창에서 지정한 암호를 입력한 후, 패키지를 실행하면 정상적으로 수행됩니다.

 

방법 2) 패키지의 ProtectionLevel을 ServerStorage로 임시 변경한 후, 패키지를 실행할 SQL Server에 저장시킵니다.

 

ProtectionLevel을 ServerStorage로 설정하는 것은 패키지의 보안과 관련된 사항을 SQL Server에게 관리하도록 넘기는 것입니다. 이 때, 주의할 것은 이 설정으로 변경한 경우에는 패키지는 파일 형태로 저장할 수 없고 SQL Server로만 저장할 수 있습니다.

개발 화면의 상단 파일(F) 메뉴 중, 다른 이름으로 ….dtsx의 복사본 저장(C) 메뉴를 클릭합니다.

 

패키지 위치를 SQL Server로 지정한 후, 인증 유형을 설정합니다. Windows 인증이나 SQL Server 인증이나 상관없이 연결할 수 있는 적절한 권한(msdb의 db_dtsadmin 역할 또는 db_dtsltduser 역할)이 있는 계정으로 지정하면 됩니다. 패키지 경로(K) 부분에서는 저장할 패키지 명을 지정합니다. 옆의 ... 버튼을 눌러 폴더 형식과 같은 적절한 경로를 지정할 수도 있습니다.

  

단, 이 때 SQL Server에 저장시킨 후, 다시 로컬 파일로 저장할 경우 저장이 되지 않습니다. 이는 앞서 언급한 바와 같이 ProtectionLevel을 ServerStorage로 설정하였기 때문입니다. 따라서 패키지를 별도의 파일로 저장하기 위해서는 ProtectionLevel의 속성값을 ServerStorage 이외의 값으로 변경하셔야 합니다.

이후, SQL Agent에서 패키지를 실행하는 작업을 등록할 경우, 다음 그림과 같이 패키지 원본을 SQL Server로 지정한 후, 저장한 패키지를 선택하면 됩니다.

 

 

-------------------------------------------------------------

SQLLeader.com / ADConsulting / 한대성 (olaper@지메일.컴)

-------------------------------------------------------------