마스터 패키지로 여러 패키지들의 연결 정보 관리하기

마스터 패키지로 여러 패키지들의 연결 정보 관리하기

 

Microsoft Premier Field Engineer

한대성

 

다음과 같은 형태의 단순한 자식 패키지를 하나 만듭니다.

a.     Servername 이라는 String 형 변수를 추가합니다.

b.     ChildDBConn이라는 OLE DB 연결을 설정합니다. 본 예제에서는localhost\ss2005 서버를 가리키는 연결을 만듭니다.

c.     이 외에 여러 형태의 연결을 만듭니다. (옵션 사항임.)

d.     SQL 실행 작업을 추가한 후, 다음과 같이 설정합니다.

 

e.     스크립트 작업을 추가한 후, SQL 실행 작업과 연결하고, 속성의 ReadOnlyVariables 부분에 Servername을 입력한 후, 스크립트 편집기를 열어 다음과 같은 간단한 스크립트를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime   Public Class ScriptMain               Public Sub Main()                                                 MsgBox(Dts.Variables("Servername").Value.ToString)                           Dts.TaskResult = Dts.Results.Success             End Sub   End Class

 

f.      완성된 자식 패키지 모습입니다.

 

g.     패키지를 실행하면 다음과 같이 기본으로 설정된 OLE DB의 서버 명이 출력됩니다.

 

 

다음과 같은 상황을 생각해 봅시다.

1.     위와 같은 형태의 패키지가 아주 많다.

2.     대부분 OLE DB 연결 명은 비슷하거나 동일하다.

3.     패키지를 실행 시킬 때, 다른 DB 서버 연결을 지정하고 싶다. 즉, 위의 예에서 ChildDBConn이라는 연결이 localhost\SS2005로 지정되어 있지만, localhost DB 서버에서 실행되도록 하고 싶다.

4.     SQL Agent나 패키지를 실행시킬 때 Connection 탭에서 지정해주면 되겠지만, 패키지들이 많아서 번거롭다.

5.     또한, DB 서버를 일괄적으로 바꿀 수 있다. 그러면 다시 모든 패키지의 연결 정보를 (Agent 작업이든, 패키지 실행 스크립트이든, SSIS 패키지 자체이든) 바꿔줘야 한다.

6.     자식 패키지들이 많아서 일일이 어떤 기능을 추가하는 것도 어렵다.

 

이런 경우가 가끔씩 있습니다. 제 개인적인 경험도 수백 개의 패키지의 연결 정보를 관리해야 할 때가 있었습니다.

 

몇 가지 운영 방법들이 있겠지만, 다음과 같은 조건을 가지면서 할 수 있는 방법을 생각해 봤습니다.

1.     자식 패키지(=기존 패키지)를 수정하지 않았으면 좋겠다.

2.     연결 정보가 변경되더라도 최소한의 작업으로 이를 반영할 수 있었으면 좋겠다.

 

제가 생각해 본 방법은 일명 『마스터 패키지』라는 패키지 실행을 담당하는 부모 패키지를 이용하는 것입니다.

직접 AAA.dtsx라는 패키지를 실행하는 대신, Master.dtsx라는 마스터 패키지에 AAA.dtsx라는 패키지 이름을 던져주면(외부에서 변수 값을 지정하는 방식으로) 마스터 패키지에서는 연결 정보를 바꿔치기 한 후 이 패키지를 실행하는 방법입니다.

 

 

다음과 같은 형태로 마스터 패키지를 만들어 봅시다.

a.     PackageNameStr이라는 String형 변수를 추가하고, 기본값으로 자식 패키지의 경로를 입력합니다.

b.     ChildDBConn 이라는 String형 변수를 추가하고 다음과 같이 OLE DB의 연결 문자열을 입력합니다. 이 때의 문자열은 자식 패키지의 ChildDBConn 연결의 연결 문자열과는 다르게 지정합니다. 본 예에서는 localhost 서버를 가리키는 DB 연결 문자열을 설정합니다.

c.     스크립트 작업을 하나 추가한 후, 편집기의 ReadOnlyVariables 속성에 PackageNameStr,ChildDBConn을 입력합니다.

 

d.     스크립트 편집기를 연 후, 다음과 같은 스크립트를 입력합니다.

Imports System Imports System.Data Imports Microsoft.SqlServer.Dts.Runtime   Public Class ScriptMain       Public Sub Main()           Dim pkg As String         pkg = Dts.Variables("PackageNameStr").Value.ToString           Dim app As Application = New Application()         Dim p As Package = app.LoadPackage(pkg, Nothing) ' 패키지 개체를 생성           Dim con As ConnectionManager           For Each con In p.Connections  ' 패키지 내의 연결들을 조사               If con.AcquireConnection(Dts.Transaction).GetType.ToString = "System.__ComObject" Then ' OLEDB 연결일 경우                 If con.Name = "ChildDBConn" Then  ' 연결 이름이 ChildDBConn일 경우, 변수값으로 대체                     con.ConnectionString = Dts.Variables("ChildDBConn").Value.ToString                 End If             End If           Next           p.Execute() ' 패키지 실행               Dim pkgError As DtsError           If (p.Errors.Count > 0) Then ' 자식 패키지에서 에러가 발생한 경우, 이 정보를 전파             p.Dispose()             For Each pkgError In p.Errors                 Dts.Events.FireError(pkgError.ErrorCode, pkgError.SubComponent, pkgError.Description, pkgError.HelpFile, pkgError.HelpContext)             Next               Dts.TaskResult = Dts.Results.Failure         Else             p.Dispose()             Dts.TaskResult = Dts.Results.Success         End If     End Sub   End Class

 

에러 전파 부분을 잠시 설명하겠습니다.

패키지 개체를 만들어 자식 패키지를 실행할 경우, 자식 패키지가 에러가 나더라도 이 정보가 전달이 안됩니다. 즉, 무조건 성공으로 보이지요. 따라서 이를 개선하고자 추가한 부분입니다.

자식 패키지를 실행한 후, Error Count가 0보다 크다면 에러 정보를 현재 패키지에 전달(FireError)하는 것입니다.

e.     패키지를 실행시켜 봅시다.

 

f.      SQL Agent에 패키지를 등록할 경우, 다음과 같이 설정합니다.

값 설정 탭에서

            \Package.Variables[사용자::PackageNameStr].Properties[Value] 라는 속성을 입력하고 값 부분에는

실행 시킬 패키지 명(경로 포함)을 입력함.
                  

 

 

본 예제에서는 단순히 마스터 패키지에 자식 패키지에서 사용할 연결 정보(ChildDBConn)를 변수에다가 저장하는 식으로 구현했지만, 연결 정보가 많거나 연결 정보들을 별도의 파일이나 테이블 등에 관리할 경우, Recordset을 이용하여 ADO 개체에 저장하고 스크립트 작업을 조금 변경해서 매핑 & 변경 되도록 설정하면 되겠지요.

(글로 적으려니까 무지하게 길어지고 무슨 말인지 잘 모르겠네요.@.@)