스크립트 구성 요소를 이용한 원본, 변환 구현하기

스크립트구성요소.dtsx

스크립트 구성 요소를 이용한 원본, 변환 구현하기

 

한대성

MS SQL Server MVP

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

 

 

 

Question

아래의 데이터를  COL1기준으로 중복을 제거하고 나열하고 싶은데요. (순서는 상관없습니다.) COL1     COL2 ---------------     1       'AAA'     2       'BBB'     3       'CCC'     2       'ZZZ'     2       '1111'      2       'ZZZ'    <원하는 결과셋> COL1     COL2 ---------------     1       'AAA'     2       'BBB' 'ZZZ' '1111'     3       'CCC'

 

 

본 글에서는 다음과 같은 내용에 대해 다루겠습니다.

 

1.       스크립트 변환을 이용해서 원본 데이터 생성하기

2.       정렬을 이용한 데이터 중복 제거

3.       비동기(Asynchronous) 스크립트 변환 작업

 

 

 

 

 

1. 스크립트 변환을 이용한 원본 데이터 생성

 

데이터 처리 작업에서 원본이 되는 데이터 소스는 텍스트 파일, 데이터 테이블, 엑셀 파일 등 여러 형태가 될 수 있습니다. 어떤 경우에는 데이터 원본을 창조(!! ㅎㅎ)해야 하는 경우도 있겠지요. 테스트 데이터나 샘플 데이터 또는 데이터 처리에 필요한 임의의 규칙을 적용한 데이터를 생성해야 할 때 등입니다.

 

이런 경우에, 스크립트 구성요소의 원본 기능을 이용할 수 있습니다. 참고로, 제어 흐름의 스크립트 작업과는 다른 작업 개체입니다.

 

자, 이제 이런 기능을 구현해 봅시다.

 

우선, 제어 흐름 영역에 데이터 흐름 작업을 하나 추가한 후, 이를 더블 클릭해서 데이터 흐름 영역을 엽니다. 여기에 도구 상자의 데이터 흐름 변환 중 스크립트 구성요소를 선택하여 추가합니다. 추가할 경우, 이 스크립트 구성요소를 원본으로 사용할 지 또는 변환이나 대상으로 사용할 지를 선택하는 다음과 같은 창이 나타납니다.

 

 

원본(S)으로 선택하고 확인을 눌러 창을 닫은 후, 해당 작업 개체를 더블 클릭해서 스크립트 변환 편집기를 엽니다.

 

스크립트 변환 편집기는 다음과 같이 세 개의 탭으로 구성되어 있습니다. 탭의 각 기능에 대한 자세한 정보는 다음 링크의 글을 참고하시기 바랍니다.

 

 

입/출력(I) 부분의 출력 0을 확장한 후, COL1과 COL2라는 열을 추가합니다.

COL1은 부호 없는 4바이트 정수(DT_I4), COL2는 문자열(DT_STR) 10자리로 설정합니다.

 

이제 스크립트 탭에서 스크립트 디자인(S)를 클릭하여 VSA(Visual Studio for Applications)를 열고 다음과 같은 코드를 입력합니다. (VB.net은 제가 어떻게 설명할 내용이 아니기 때문에 설명은 생략합니다.^^)

 

 

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper   Public Class ScriptMain     Inherits UserComponent           Public Overrides Sub CreateNewOutputRows()                 With 출력0Buffer             .AddRow() : .COL1 = 1 : .COL2 = "'AAA'"             .AddRow() : .COL1 = 2 : .COL2 = "'BBB'"             .AddRow() : .COL1 = 3 : .COL2 = "'CCC'"             .AddRow() : .COL1 = 2 : .COL2 = "'ZZZ'"             .AddRow() : .COL1 = 2 : .COL2 = "'111'"             .AddRow() : .COL1 = 2 : .COL2 = "'ZZZ'"         End With       End Sub       End Class

 

자, 이제 멀티캐스트 변환이나 파생 열 변환과 같이 임의의 변환 작업을 연결한 후, 데이터 뷰어를 통해 제대로 데이터가 발생되는지 확인해 봅니다.

 

 

 

 

 

 

2. 정렬(Sort) 변환을 이용한 중복 제거하기

 

위의 예제에서 2, ‘ZZZ’ 데이터는 두 번 나옵니다.

만약 데이터 원본이 MSSQL 이라면 간단히

SELECT distinct …

구문을 이용해서 중복을 제거할 수도 있습니다.

 

하지만, 만약 데이터 원본이 텍스트 파일이나 위에서와 같이 쿼리를 적용할 수 없는 데이터 원본이라면 어떻게 해야 할까요? 읽어온 데이터를 임시 테이블에 넣고 다시 이를 읽어오는 방식으로 해야 할까요? 이러한 방식이 스테이징(Staging) 단계를 이용하는 형태입니다.

어떻게 보면, 이러한 스테이징 단계를 거치는 방법은 불필요한 디스크 I/O를 발생시키는 비효율적인 방식입니다.

 

이럴 경우, SSIS에서는 정렬 변환을 이용할 수 있습니다. 정렬 변환은 입력 데이터를 정해진 순서대로 정렬시키는 변환입니다. 하지만, SSIS의 정렬 변환은 단순 정렬뿐만 아니라 중복 제거라는 유용한 기능을 포함하고 있습니다. 정렬 변환을 이용한 데이터 중복 제거는 다음 글을 참고하시기 바랍니다.

 

여기서는 단순히 구현만 하도록 하겠습니다.

 

1의 예제에서 멀티캐스트 변환을 제거한 후, 대신 정렬 변환을 추가하고 스크립트 구성요소와 연결합니다.

 

본 예제에서는 정렬 기능은 크게 중요한 사항은 아닙니다. 전체 데이터 중에서 중복을 제거하는 것이기 때문에 사용 가능한 입력 열 부분에서 열을 모두 선택한 후, 아래에 있는 중복되는 정렬 값이 있는 행 제거(R)부분을 체크하고 확인합니다.

 

여기서도 1과 같이 멀티캐스트 같은 변환을 추가한 후, 데이터 뷰어를 통해서 처리되는 데이터를 확인해 보시기 바랍니다.

 

 

 

 

 

3. 비 동기(Asynchronous) 스크립트 변환 작업 구현하기

 

이제 위의 두 기능과는 조금 복잡한 기능을 구현해 봅시다. 우선 동기(Synchronous)와 비동기(Asynchronous)작업에 대해 아주 간단히 설명하고 넘어가겠습니다.

동기 작업은 입력되는 대로 바로 출력하는 변환 형태입니다. 파생 열 변환과 같이 일정 크기의 버퍼를 통해 전체 데이터 중 일부라도 로딩이 되어 변환 작업에 들어오면 이 데이터들만 바로 처리해서 출력하는 형태의 작업입니다. 이에 비해 비동기 작업은 정렬이나 집계 등과 같이 전체 데이터를 로딩한 후에 이를 대상으로 연산 작업을 한 후, 출력하는 형태입니다.

 

위의 예제를 이용해서 계속 진행하겠습니다.

 

정렬 변환 뒤에 스크립트 구성 요소 – 변환을 추가합니다.

 

 

스크립트 구성 요소가 변환으로 설정된 경우, 원본의 경우와는 달리 입력 열 탭이 하나 더 있습니다. 스크립트 변환에서 사용할 입력 열을 선택하는 단계이며, 본 예제에서는 COL1, COL2 모두 선택하도록 합니다.

 

입/출력 탭에서 아래와 같이 출력 0을 선택하고선, 오른쪽의 속성 부분에서 SynchronousInputID의 속성값을 없음으로 변경합니다.

 

그런 다음, 아래와 같이 두 열을 추가합니다. COL1이라는 부호 없는 4바이트 정수(DT_I4)와 COL3이라는 문자열(DT_STR) 50자리 열을 추가합니다. (필요한 경우, 조절해서 사용하세요.)

 

스크립트 탭에서 스크립트 디자인(S) 버튼을 클릭해서 VSA를 연 다음 아래와 같은 스크립트를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper   Public Class ScriptMain     Inherits UserComponent       Dim COL2() As String, maxSeq As Integer = 0       Public Overrides Sub 입력0_ProcessInput(ByVal Buffer As 입력0Buffer)           While Buffer.NextRow()             입력0_ProcessInputRow(Buffer)         End While           If Buffer.EndOfRowset Then               Dim i As Integer                 For i = 1 To maxSeq                 If COL2(i).ToString <> "" Then                     With 출력0Buffer                         .AddRow()                         .COL1 = i                         .COL3 = COL2(i).ToString                       End With                   End If             Next               출력0Buffer.SetEndOfRowset()         End If       End Sub         Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)           If COL2(Row.COL1) <> "" Then             COL2(Row.COL1) = COL2(Row.COL1) + " " + Row.COL2.ToString         Else             COL2(Row.COL1) = Row.COL2.ToString         End If           If Row.COL1 > maxSeq Then             maxSeq = Row.COL1         End If       End Sub         Public Sub New()         ReDim COL2(10000)     End Sub End Class

(스크립트 엉망이라고 뭐라고 하지 마십쇼~!!ㅎㅎ 더 좋은 방법 있으면 알려주십쇼~^^)

 

VSA를 닫고서 제대로 처리되는지 확인해 봅니다.

 

 

 

 

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