스크립트 변환을 이용한 RowNumber 및 파생열 만들기

RowNumber와 파생열 구현하기

 

한대성

MS SQL Server MVP

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

 

 

 

Question

제가 하고자 하는 질문은 '|'구분자를 가지는 TEXT파일의 원본을 가지고 OLE DB에 넣고자합니다. 그냥 DB에 넣는게아니라 한 필드를 이용하여 새로운 필드를 추가하여 넣는것입니다. openrowset을 이용하여 쿼리문으로 넣는건 만들었는데 SSIS를 이용하자니 막히네요. 제가 openrowset에서 사용했던 쿼리는 아래와 같습니다. select left('AA980020000000' , 13-len(row_number() over(order by A_NO)))      + cast(row_number() over(order by A_NO)  as varchar(10)) AS 'KEY'   설명을 드리자면 A_NO필드를 기준으로 row_number를 생성합니다. 숫자형태임으로 형변환을 하고 KEY필드의 자리수는  14자리 고정임으로 'AA980020000000'에서 row_number된 자리수만큼을 빼고 row_number를 넣어주는 것입니다.   필드명만 바꿔서 select해보시면 어떤 형태를 원하는건지 아실수있으실것입니다.   위와같은 방법을 SSIS에 적용하고자하는데 [파생열 변환]을 이용했습니다. 근데 거기서는 over나 row_number등 함수에 대한 제약이 있는듯한다. 위와같은 방법을 실현할수 있는방법이 궁금합니다.

 

 

질문하신 사항으로 하기 위해서는 입력 데이터에 대해 Row_Number 기능이 구현되어야 합니다. Row_Number를 간단히 구현하는 방법으로는 스크립트 변환을 이용하면 됩니다.

 

1. 다음과 같은 텍스트 파일을 원본 데이터로 이용하겠습니다.

 

2. AA980020000000라는 값이 상황에 따라 바뀔 것도 같습니다. 이 값을 변수에 저장하도록 합시다. 만약 값이 변하더라도 변수의 값만 변경하면 되겠지요.

제어 흐름 영역의 빈 곳을 선택한 후, 마우스 오른쪽 클릭해서 나타나는 메뉴 중 변수(S)를 선택하고선 KeyCol이라는 String형 변수를 하나 추가하고, 기본 값으로 AA980020000000을 설정합니다.

 

 

3. 데이터 흐름 작업을 추가한 후, 해당 데이터 흐름 영역에서 플랫 파일 원본을 추가한 후, 위의 텍스트 파일에 대한 열을 설정합니다.

 

4. 스크립트 구성 요소(변환)을 추가한 후, 플랫 파일 원본과 연결합니다.

 

5. 스크립트 구성 요소를 더블 클릭해서 편집기를 연 다음, 입력 열 탭에서 사용 가능한 입력 열 부분을 체크합니다.

 

6. 입/출력 탭에서 출력 0 부분을 확장한 후, 출력 열을 선택하고선, 아래에 있는 열 추가(C)를 클릭해서 새로운 열을 하나 추가합니다. 새로 추가한 열 이름은 RowNum이라 하고, DataType을 부호 없는 4바이트 정수(DT_I4)로 설정합니다.

 

7. 스크립트 탭에서 스크립트 디자인(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 i As Integer       Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)         i = i + 1         Row.RowNum = i       End Sub       Public Sub New()         i = 0     End Sub End Class

 

자, 여기까지가 RowNumber를 추가하는 부분을 구현하였습니다. VB.net 스크립트라서 복잡하게 보이지만, 몇 번 하다 보면 금방 익숙해지실 것입니다.

 

 

8. 이제, 도구 상자에서 파생 열 변환을 추가한 후, 스크립트 구성 요소와 연결합니다.

 

9. 파생 열 변환 편집기에서 다음과 같은 파생 열을 추가합니다.

 

식 : SUBSTRING(@[사용자::KeyCol],1,14 - LEN((DT_STR,14,949)RowNum)) + ((DT_STR,14,949)RowNum)

 

10. 추가된 데이터를 확인해 보면 다음과 같습니다.

 

 

 

 

SSIS의 파생 열 변환을 이용해서 이와 같이 구현하시면 됩니다.

하지만~!! 복잡합니다.

 

이러한 방법은 어떨까요?

 

6단계부터 다음과 같이 해 봅시다.

6’. RowNum 열 뿐만 아니라 Key라는 열까지 추가합니다. KeyCol 열은 String형, 14자리로 설정합니다.

 

7’. 스크립트 탭에서 ReadOnlyVariables 부분에 KeyCol이라고 입력한 후, 아래 스크립트 디자인(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 i As Integer     Dim keyStr As String       Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)         i = i + 1         keyStr = Me.Variables.KeyCol.ToString             Row.RowNum = i         Row.Key = keyStr.Substring(0, keyStr.Length - i.ToString.Length) + i.ToString       End Sub       Public Sub New()         i = 0       End Sub End Class

 

 

 

 

스크립트 구성 요소가 어렵게 보일 수도 있지만, 복잡한 로직을 간단히 구현할 수 있는 유용한 기능입니다.