원격에서 패키지 호출하기 – (1) 구현 환경 설명

원격에서 패키지 호출하기 - (1) 구현 환경 설명

한대성

MS SQL Server MVP

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

이번 강좌에서는 패키지가 있는 로컬 서버가 아닌 원격 서버에서 패키지를 호출하는 방법에 대해 설명하겠습니다. 본 강좌에서는 초보자들도 쉽게 따라서 할 수 있도록 하기 위해 좀 더 자세하게 패키지를 만드는 것부터 작업을 구성하는 것까지 최대한 상세하게 진행토록 하겠습니다.

<강좌 구성>

1. 구현 환경 설명 2. 테스트 용 패키지 만들기 3. SQL Agent Job을 이용한 호출 4. xp_cmdshell 명령을 이용한 호출 5. 웹 서비스를 이용한 호출

1. 구현 환경 설명

일반적으로 ETL 작업이라 하면 서버에서 패키지를 특정 시간에 실행되도록 하는 용도로만 생각할 수 있습니다. 또는 데이터를 이관하거나 처리해야 할 때 서버에서 실행하는 작업 개체 정도로만 생각하기 쉽습니다. 하지만 다음과 같은 상황도 생각해 볼 수 있겠지요.

A라는 서버에서 특정 작업을 수행하다가 B라는 서버에 있는 SSIS 패키지를 실행시켜야 하는 경우도 있습니다. 또는 CS 어플리케이션의 예로 들자면, 사용자가 [패키지 실행]이라는 버튼을 클릭하면 서버에 있는 패키지를 실행하는 경우도 이와 비슷한 경우입니다.

위의 환경과 같은 상황이 원격 환경에서 패키지를 실행시키는 환경입니다.

SSIS 패키지는 SQL Server에 포함되어 있는 서비스이긴 하지만 단순히 쿼리 문으로는 패키지를 실행시킬 수는 없는, 어플리케이션에 가까운 형태의 개체입니다.

dtsx 형태의 파일로 된 패키지나 msdb에 저장된 패키지이더라도 모두 dtexec.exe 또는 dtexecui.exe라는 프로그램을 이용해야만 패키지를 실행할 수 있습니다. 이 외에도 .net 어플리케이션에서 패키지 개체를 만들고 이를 호출하는 방식도 있습니다. 어쨌든 별도의 프로그램이 실행되어야 패키지가 처리되는 방식입니다.

단순히 패키지 실행만 하면 되는 경우도 있겠지만, 좀 더 생각해 봅시다. 첫 번째 그림과 같이 경우에 따라서는 입력 값을 패키지에 넘겨주고(①) 이를 이용해서 처리 작업을 수행한 다음(②) 결과를 반환하는 형태(③)로도 사용됩니다. 단순히 패키지를 실행시킨 후 정상적으로 처리되었는지를 체크하기 위해 리턴 값을 받는 경우도 비슷한 형태입니다.

다음 2강부터 5강까지는 이와 같은 각 상황을 구현하는 방법에 대해 설명토록 하겠습니다.

간단히 원격 패키지 호출 방식을 요약하자면 다음과 같이 정리할 수 있습니다.

a. 패키지가 있는 서버에 패키지를 실행시키는 SQL Agent 작업을 등록하고, 여기에는 일정을 설정하지 않습니다. 그런 다음 원격 서버 또는 로컬 서버에서 해당 패키지를 실행해야 할 경우,
EXEC msdb.dbo.sp_start_job <작업 명>
형태로 쿼리에서 작업을 실행하도록 합니다.
만약 원격 서버라면
EXEC linkedserver.msdb.dbo.sp_start_jon <작업 명>
와 같이 연결된 서버(Linked Server)로 등록한 후, 원격 서버의 저장 프로시저를 호출하는 식으로 구현하는 방법입니다.

b. 두 번째는 master.dbo.xp_cmdshell 저장 프로시저를 이용하는 방식입니다.
xp_cmdshell 확장 저장 프로시저는 외부의 프로그램을 실행시킬 수 있는 저장 프로시저입니다. 하지만 SQL 2005에서는 기본값으로 이 저장 프로시저가 비활성화 되어 있습니다. 따라서 이를 활성화 해 주는 작업을 먼저 해야겠지요. 그런 후 다음과 같은 형태로 패키지를 실행시키는 방식입니다.
EXEC master.dbo.xp_cmdshell ‘dtexec /F[패키지 명] ….’
원격 서버에서는 연결된 서버의 RPC 옵션을 True로 설정한 후
EXEC linkedserver.master.dbo.xp_cmdshell ‘dtexec …’
형태로 사용하면 됩니다.

c. 마지막 방법은 .net 환경에서 패키지 개체를 만들고 이를 웹 서비스로 구성하는 방식입니다. 사용자 어플리케이션 등에서 서버의 패키지를 호출할 때에는 웹 서비스에 패키지 관련 인자를 지정한 후 이를 호출하는 방식입니다.

필자는 처음에는 SQL 2005의 SQL CLR을 이용해서 이를 구현해 보고자 하였습니다. CLR 코드에서 패키지 개체를 저장 프로시저 또는 사용자 함수, 트리거 형태로 만들고선 이를 이용해 보고자 하였습니다.

결론적으로 말해서는, 이 방법은 불가능 합니다.

The reason is that SQL Server has very strict requirements on the type of code running inside (this is how it achieves the great reliability and why by default it only allows "safe" .NET code and only selected system assemblies).

<출처 : http://blogs.msdn.com/michen/archive/2007/08/24/don-t-run-ssis-package-using-sql-clr.aspx>

자 그럼 다음 강좌에서 위의 기능들을 단계별로 구현해 보겠습니다.