(─━┘_└━─)/ 머리만1톤(Head1Ton)

SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated 2007.05.14)

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

현상

로컬 PC 또는 서버에서는 패키지를 실행하면 제대로 수행이 되는데, 패키지 파일(*.dtsx)을 서버에 저장한 후 SQL Agent에 등록하여  실행하거나 패키지가 만들어진 PC나 서버가 아닌 다른 사용자의 환경에서 패키지를 실행할 경우 에러가 발생할 수 있습니다.

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

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

(Service Pack2 이전의 에러 메시지)

(Service Pack2 이후의 에러 메시지)

원인

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

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

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

수동으로 실행할 때에는 패키지가 암호화된 개인 키와 실행 환경의 개인키가 동일하기 때문에 정상적으로 실행됩니다. 하지만, 서버로 파일을 복사한 후, 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로 지정한 후, 저장한 패키지를 선택하면 됩니다.

[방법 3]  SQL Agent의 작업 중, 데이터 원본 탭에서 DB 연결에 대한 Password를 지정합니다.

패키지를 실행하는 작업 단계 중, 데이터 원본 탭을 클릭하면 패키지에서 이용하는 데이터 원본이 표시됩니다. 이 때, SQL 인증을 사용하는 DB 연결을 선택한 후, 연결 문자열 부분에 Password=... 구문을 추가합니다.

예) Data Source=dshan\ss2005;User ID=sa;Initial Catalog=tempdb;Provider=SQLOLEDB.1;Auto Translate=False;Password=dbpassword

[방법 4]  DB 정보를 포함하는 구성 파일을 만든 후, 이를 구성 탭에서 지정합니다.

패키지 내의 모든 개체에 대한 속성은 구성 이라는 기능으로 지정할 수 있습니다. 또한 구성 정보는 파일로 저장할 수도 있습니다.

(구성에 관한 자세한 내용은 다음 링크의 강좌를 참고하시기 바랍니다.

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=454)

패키지에서 사용하는 DB 연결 정보를 별도의 구성 파일로 만든 후에 이 구성 파일을 지정하는 식으로 문제를 해결할 수 있습니다. 예를 들어 다음과 같은 구성 파일을 생성합니다.

예) D:\dbconnectionstring.dtsconfig 라는 이름으로 구성 파일 생성

이 구성 파일은 “.\ss2005.tempdb” 라는 연결에 대해 ConnectionString 정보를 가지고 있는 구성 파일입니다. 이 구성 파일을 SQL Agent의 작업 단계 설정 중 구성 부분에서 추가합니다.

이 방식은 프로젝트 단위의 여러 패키지가 존재하며, 각 패키지에서 같은 DB 정보를 이용할 때 일괄적으로 관리할 수 있는 장점이 있습니다. 또한, 직접 데이터 원본 부분에서 password를 지정해주는 대신 별도의 파일에 기록함으로써 DB Password의 노출을 방지할 수 있습니다. (파일에 대한 접근 권한은 SQL Agent 실행 계정 및 Administrator에게만 부여하도록 Windows 환경에서 설정하면 됩니다.)

[방법 5] 패키지의 ProtectionLevel을 DontSaveSensitive로 변경한 후, DB 연결 정보의 값은 식을 이용하여 지정하는 방식으로 저장합니다. 여기서 ProtectionLevel을 DontSaveSensitive로 설정한 것은 다른 사용자가 패키지를 열었을 때에도 보안 경고를 발생시키지 않도록 하기 위함입니다.

우선 패키지의 보안 수준을 DontSaveSensitive로 변경합니다.

연결 관리자에서 SQL 인증을 이용하는 연결을 선택한 후, 속성의 Expression 부분에서 ... 버튼을 클릭하여 해당 연결의 속성에 대한 식을 지정합니다.

속성 식 편집기에서 ConnectionString 속성을 선택하고 해당 연결에 대한 연결 문자열을 직접 지정합니다. 이 때, 연결 문자열에 Password를 포함해서 지정해야 합니다.

[참고] 직접 식으로 지정하는 대신, 연결 문자열을 포함하는 String형 변수를 지정하고, 속성 식에서 이 변수를 사용하도록 설정할 수도 있습니다.

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.

Q) SSIS가 뭡니까?

SSIS는 SQL Server 2005 Integration Services의 약자로써, 간단하게는 데이터 가져오기/내보내기와 같은 데이터 이관 작업에서부터 이기종 데이터 통합 및 처리, 데이터베이스 관리 작업 등을 수행할 수 있는 서비스 입니다.

Q) SQL 2000의 DTS와 비슷하나요?

SSIS는 SQL 2000 DTS의 업그레이드 버전입니다. DTS(데이터 변환 서비스)가 SQL 2005에서는 SSIS(데이터 통합 서비스)로 이름이 변경되었으며, 많은 점들이 향상되었습니다.

Q) SSIS 패키지가 무엇인가요?

SQL Server의 저장 프로시저와 같이 사용자가 원하는 작업들을 정의해 놓은 실행 개체가 패키지 입니다. 하나의 패키지 내에는 테이블 생성, 데이터 전송, 메일 보내기 등과 같은 여러 작업들이 포함됩니다.

Q) SSIS를 사용하기 위해서는 SQL Server 2005가 설치되어야 하나요?

SSIS는 SQL Server 2005에 포함된 서비스이긴 하지만 SQL 엔진(SQL Server Database Services)이 반드시 설치되어야 할 필요는 없습니다. SQL Server 2005에는 SQL Server 엔진뿐만 아니라 Integration Services, Analysis Services, Reporting Services, Notification Services 등과 같은 여러 서비스들이 포함되어 있습니다.

SQL 2000 DTS는 반드시 SQL이 실행 중이어야만 사용 가능했지만, SSIS는 SQL이 실행 중일 필요는 없습니다. 따라서 단순히 패키지를 실행시키기 위해서는 Integration Services만 설치되어 있으면 됩니다. 패키지를 작성하려면 다음과 같이 고급(D)를 누르면 나타나는 기능 선택 창에서 Business Intelligence Development Studio가 설치되어야 합니다.

Q) SQL Server Database Services가 반드시 실행 중이어야 하나요?

SQL Server 2005에서는 Integration Services와 Database Services가 분리되었습니다. 즉, Database Services가 실행 중이지 않더라도 패키지를 실행하거나 관리할 수 있습니다.

Q) SSIS 패키지는 무엇으로 만드나요?

SQL 2000 DTS 패키지는 엔터프라이즈 관리자(Enterprise Manager)에서 만들었습니다. 즉, EM에서 패키지를 만들고 관리까지 모두 수행하였습니다. 하지만, SQL 2005 SSIS에서는 패키지를 만들기 위해서는 Visual Studio를 이용해야 합니다. 이러한 점은 SSIS 패키지뿐만 아니라 Analysis Services의 Cube 및 Dimension, Reporting Services의 보고서를 만들 때도 마찬가지입니다.

Q) 그러면 SQL Server 2005 외에도 추가로 Visual Studio를 사야 하나요?

별도로 구매할 필요는 없습니다. SQL Server 2005를 설치하는 과정에서 클라이언트 구성 요소 중 Business Intelligence Development Studio를 선택하게 되면 자동으로 Visual Studio가 설치됩니다. 이 때 설치되는 Visual Studio는 SQL 2005의 Integration Services 패키지, Analysis Services의 큐브 및 디멘젼, Reporting Services의 보고서를 만들 수 있는 기능만 포함되어 있습니다.

Q) 패키지를 만들기 위해서는 SQL 쿼리 외에 추가로 알아야 할 언어가 있습니까?

별도로 알아야 할 언어는 없습니다. 또한 SQL과 관련된 작업을 수행하지 않는다면 SQL 쿼리를 몰라도 됩니다. 기본적으로 제공되는 다양한 형태의 작업 개체 중 원하는 작업을 선택해서 끌어 놓은 후, 필요한 설정만 해주면 됩니다.

VB.NET을 잘 안다면 스크립트 작업을 이용해서 다양한 작업을 추가로 구현할 수 있기 때문에 편하긴 하지만 반드시 알아야 할 필요는 없습니다.

Q) MSSQL 데이터만 사용할 수 있나요?

MSSQL 뿐만 아니라 Oracle, Sybase, DB2 등과 같은 다양한 형태의 데이터를 직접 이용할 수도 있으며 텍스트 파일, XML 파일, 엑셀 파일 등과 같은 형태의 데이터도 모두 사용할 수 있으며, 이러한 형태로 저장도 가능합니다.

Q) SSIS 패키지는 어디에 저장됩니까?

SSIS 패키지는 기본적으로 파일 형태로 존재하며, 확장자는 dtsx 입니다.

이 파일은 XML 형태로 되어 있습니다. 사용자가 원하는 위치에 패키지 파일이 저장됩니다. 이렇게 만든 패키지 파일을 서버로 복사해서 수행하거나, 또는 SQL Server의 테이블로 바로 저장시킬 수 있습니다. (SQL Server 2005의 msdb.dbo.sysdtspackages90 테이블에 저장)

Q) SSIS 패키지는 어떻게 실행시키나요?

우선, 개발 툴인 Visual Studio에서 바로 실행시킬 수 있습니다. 단, 이 때에는 Integration Services가 실행 중일 필요는 없습니다.

또한 패키지 파일(.dtsx)를 더블 클릭하면 패키지를 실행시킬 수 있는 전용 유틸리티가 실행됩니다. (DTEXECUI.exe) 이 프로그램에서 연결 정보나 옵션 등을 설정하여 수행할 수 있습니다.

이 외에도 콘솔 모드(도스 창)에서 실행시킬 수 있는 dtexec.exe라는 유틸리티를 이용해서 패키지를 수행할 수 있습니다. 패키지를 예약해서 수행하기 위해서는 SQL Server 2005 에이전트에서 작업으로 등록해서 수행하게 됩니다. 작업으로 등록할 때 유형에서 SQL Server Integration Services 패키지를 선택한 후 실행시킬 패키지를 지정하면 됩니다. 만약 SQL Server 2005 Agent를 사용하지 않는다면, 윈도우 예약 작업에서 다음과 같은 형태의 명령으로 실행시킬 수도 있습니다.

예) dtexec /FILE "D:\SSIS\Sample.dtsx"  /CHECKPOINTING OFF  /REPORTING EWCDI