원격에서 패키지 호출하기 – (3) SQL Agent Job을 이용한 호출

원격에서 패키지 호출하기 - (3) SQL Agent Job을 이용한 호출

한대성

MS SQL Server MVP

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

<강좌 구성>

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

3. SQL Agent Job을 이용한 호출

패키지를 외부에서 호출하기 위한 가장 간단하면서도 확실한 방법이 바로 SQL Agent Job을 이용하는 방식입니다. SQL Agent Job 호출 방식을 살펴보기 전에 왜 원격에서 패키지를 호출하는 것이 어려운지 살펴보겠습니다.

SQL Server 2000의 DTS 패키지와 마찬가지로 SQL Server 2005 SSIS 또한 이를 실행시키기 위해서는 dtexec.exe 또는 dtexecui.exe와 같은 실행 프로그램이 있어야 합니다. (참고 : SQL 2000 DTS 패키지는 dtsrun.exe)

즉, 서버가 아닌 다른 곳(사용자 또는 다른 서버)에서 서버에 있는 프로그램을 실행시킬 수 있어야 합니다.

dtexec.exe 및 dtexecui.exe는 웹 서비스와 같이 외부에서 직접 이 프로그램을 실행시킬 수는 없습니다. 어떻게든 로컬 서버(dtexec.exe 프로그램이 있는 서버)에서 이를 호출해야 합니다.

어떤 방식으로 가능할까요?

(프로그래밍을 깊이 몰라서 대충 아는 범위에서만 설명하겠습니다.)

아래 그림에서 “???” 표시한 부분과 같이 외부의 요청을 받아서 dtexec.exe를 호출할 수 있는 무언가가 있어야 합니다.

“???”의 기능을 할 수 있는 것은

a. SQL Server

b. 웹 서비스

c. 별도의 통신 프로그램

이 있습니다. (혹시 더 다른 방법 있나요?)

이번 글에서는 SQL Server 를 이용하는 방법을 중심으로 설명합니다.

이 그림이 이번 글 내용을 모두 포함합니다.

요약해서 설명하자면, 2강까지 만들었던 패키지를 SQL Agent 작업으로 등록합니다. 이 때 작업으로만 등록하며, 자동으로 수행될 일정은 등록하지 않습니다. 사용자는 외부에서 패키지를 실행하고자 하면, sp_start_job ‘작업 이름’ 형식으로 SQL Server에 명령을 보냅니다. 그러면, SQL Server에서는 SQL Agent 내의 작업을 수행하는 식입니다.

별 내용 아닌데 서두가 너무 길었네요. 얼른 만들어봅시다.

2강까지 만들었던 패키지를 준비하고선, SQL Server Management Studio 를 엽니다. 항목 중 가장 아래에 있는 SQL Agent 를 확장합니다. (시작되지 않았다면 시작하도록 합니다.)

새 작업(N)을 선택한 후, 다음과 같이 임의의 이름으로 작업 이름을 정합니다.

단계 탭에서 오른쪽 아래에 있는 새로 만들기(N) 버튼을 클릭해서 작업 단계를 추가합니다.

단계 이름을 적절히 지정하고, 유형(T) 부분을 SQL Server Integration Services 패키지로 변경한 후, 아래 부분에서 패키지 원본(O)을 파일 시스템으로 변경한 후, 패키지(K) 부분에서 패키지의 경로를 지정해 줍니다.

참고로, 만약 패키지를 SQL Server에 저장한 경우에는 파일 시스템 대신 SQL Server 로 선택한 후 유사한 방식으로 패키지를 지정해주면 됩니다.

참고 : 혹시 SQL Server 2005만 설치하고 서비스 팩은 설치하시지 않으셨다면 서비스 팩 2 이상 꼭 설치하세요~!

이제 확인을 눌러 창을 닫고, 다시 확인을 눌러 작업 등록을 마칩니다.

자, 이제 쿼리 창을 열어서 다음과 같이 명령을 실행해 봅시다.

EXEC msdb.dbo.sp_start_job @job_name=N'exSSISPackage'

즉, 위와 같은 형태의 쿼리를 호출하면 패키지가 실행됩니다.

비교적 간단한 방식입니다. 하지만, 이와 같은 방식에는 다음과 같은 단점이 있습니다.

a. 패키지를 호출할 때 입력 값을 넣기가 어렵습니다. 그러나, 불가능하진 않습니다.

b. 패키지 처리 결과 또는 수행 상태(성공 or 실패)를 전달받을 수 없습니다.

c. 반드시 SQL Server가 있어야 하며, SQL Agent가 실행 중이어야 합니다.
그러나, 반드시 SQL Server 2005일 필요는 없다. SQL Server 2000에서도 이와 비슷하게 구현할 수는 있습니다.

d. 동시에 패키지를 실행시킬 수 없습니다. 즉, 작업 실행이 끝나야 다시 실행할 수 있습니다. 작업이 실행 중일 때 sp_start_job을 호출하더라도 다시 실행되거나 다른 개체로 만들어져 실행되지는 않습니다. 하지만, 작업을 여러 개 만들어 각각 달리 호출하면 동시에 여러 패키지를 실행할 수는 있습니다.

이렇게 적고 보니 많은 단점들이 있는 것 같지만, 그래도 유용한 방식입니다. 만약 입력 값 및 출력 값을 받아야 한다면, 작업 실행 전에 별도의 테이블에 입력 값을 넣어주고, 패키지에서 이 값을 읽어온 후 처리 후에는 결과를 다른 테이블에 저장하고, 다른 부분에서 이 값을 읽어오도록 설정할 수도 있을 것입니다.

추가로 더 설명 드리겠습니다.

a. 패키지를 호출할 때 입력 값을 넣기가 어렵습니다. 그러나, 불가능하진 않습니다.

만약 위의 예제에서 사용하는 패키지와 같이 외부에서 패키지를 실행할 때 InputVal에 값을 넘겨주고 싶다면, 다음과 같이 설정합니다.

작업의 단계 부분 설정하는 곳에서 패키지를 선택한 다음, 아래와 같이 값 설정 탭을 설정합니다.

속성 경로 : \Package.Variables[사용자::InputVal].Properties[Value]

값 : 2 (임의의 값)

그런 후, 다음과 같이 작업을 스크립트로 생성합니다.

CREATE 스크립트 및 DROP 스크립트를 참고해서 다음과 같이 입력된 값을 이용해서 패키지 실행 명령을 만들고, 새로운 작업을 생성한 다음, 이 작업을 실행하고, 삭제시키는 저장 프로시저를 만듭니다.

USE [msdb] GO CREATE PROC dbo.USP_SSISPackageInputVal @InputVal INT AS BEGIN TRANSACTION DECLARE @ReturnCode INT DECLARE @cmdStr NVARCHAR(1000) SET @cmdStr = N'/FILE "D:\Data\My Documents\Visual Studio 2005\Projects\원격 호출 예제\원격 호출 예제\ProcessPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package.Variables[사용자::InputVal].Properties[Value]";' + CAST(@InputVal AS NVARCHAR) + ' /REPORTING E' --작업 삭제 IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'exSSISPackage') EXEC msdb.dbo.sp_delete_job @job_name=N'exSSISPackage', @delete_unused_schedule=1 SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'exSSISPackage', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'설명이 없습니다.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'DSHAN\Administrator', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ProcessPackage 실행', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'SSIS', @command=@cmdStr, @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: EXEC msdb.dbo.sp_start_job @job_name=N'exSSISPackage' --작업 수행 후 삭제 IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'exSSISPackage') EXEC msdb.dbo.sp_delete_job @job_name=N'exSSISPackage', @delete_unused_schedule=1 GO

이와 같은 저장 프로시저를 만든 후, 다음과 같은 쿼리를 실행하면 됩니다.

EXEC msdb.dbo.USP_SSISPackageInputVal 6

실제 환경에 사용하기 위해서는 몇 가지 더 고려해야 할 사항들이 있겠지요. 권한 문제라든지, 현재 수행중인 작업에 대한 삭제 방지 처리 등. 이와 같은 방법도 있다라는 것을 참고하시기 바랍니다.