스크립트 변환을 이용한 열 생성하기

스크립트 변환을 이용한 열 생성하기

 

한대성

MS SQL Server MVP

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

 

 

다음과 같은 일자 및 시간 열에 대해 아래 조건에 따른 파생 열을 생성하는 방법을 살펴보겠습니다.

 

CREATE TABLE DATETABLE (         date char(8),         time char(6) ) GO   INSERT DATETABLE VALUES ('20070703','162541') INSERT DATETABLE VALUES ('20070505','221305') INSERT DATETABLE VALUES ('20080221','101511') INSERT DATETABLE VALUES ('20110411','031120') INSERT DATETABLE VALUES ('20030109','120144') INSERT DATETABLE VALUES ('20060527','160010') GO   SELECT * FROM DATETABLE GO /* date     time -------- ------ 20070703 162541 20070505 221305 20080221 101511 20110411 031120 20030109 120144 20060527 160010 */

 

파생 열 생성 규칙

예) 20070703 162541  à 7G031

A. 7은 년도

B. 알파벳은 월 (A,B,C,D,E,F,G,H,I,J,K,L,M)

C. 다음 두 자리는 일

D. 마지막 자리는 주/야 – 08시 ~ 21시 이전 = 1, 그 외 = 2

 

 

1. 쿼리로 구현하기

 

입력 데이터가 SQL 테이블인 경우에는 쿼리를 이용하는 것이 가장 편리한 방법입니다.

제어 흐름 영역에 데이터 흐름 작업을 하나 추가하고 OLE DB 원본을 추가합니다.

OLE DB 원본에서 해당 테이블이 있는 DB 연결을 설정한 후, 데이터 액세스 모드(A)를 SQL 명령으로 변경하고 SQL 명령 텍스트(S)에 다음과 같은 쿼리를 입력합니다.

 

 

SELECT         CAST(CAST(SUBSTRING(date, 3,2) AS INT) AS VARCHAR)         + CHAR(64 + CAST(SUBSTRING(date, 5,2) AS INT))         + RIGHT(date,2)         + CASE WHEN TIME>='080000' AND TIME<'210000' THEN '1' ELSE '2' END         AS LOTNUM FROM DATETABLE

 

데이터 대상 또는 변환 작업을 설정하고 처리되는 결과를 확인합니다.

 

 

 

2. 스크립트 변환으로 구현하기

 

만약, 입력 데이터가 텍스트 파일과 같이 직접 쿼리를 이용하지 못할 경우에는 SSIS에서 이러한 식을 구현해야 합니다. 파생 열 변환과 SSIS의 식을 이용해서 이를 구현해도 되지만, 본 예제의 조건에서 월에 대한 변환 부분에서 상당히 긴 식을 이용해야 하기 때문에 작성이나 관리에 어려움이 있습니다.

대신, 스크립트 구성요소 - 변환을 이용해서 간단히 구현할 수 있습니다.

 

OLE DB 원본에서 데이터 액세스 모드(A)를 테이블 또는 뷰로 선택하고 해당 테이블을 선택합니다.

 

도구 상자에서 스크립트 구성 요소를 선택하여 추가한 후, 변환으로 설정하고, OLE DB 원본과 연결합니다.

스크립트 변환 편집기의 입력 열 탭에서 사용 가능한 입력 열 부분에 두 열을 모두 선택합니다.

 

입/출력 탭에서 [출력 0] à [출력 열] 부분에 LOT라는 열을 추가하고 DataType를 문자열[DT_STR], Length를 6으로 설정합니다.

 

스크립트 탭에서 스크립트 디자인(S)를 클릭해서 VSA(Visual Studio for Applications)을 연 다음, 다음과 같은 스크립트를 입력합니다.

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 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)         Dim tmpStr As String         Dim inpDate As String = Row.date.ToString           tmpStr = CInt(inpDate.Substring(2, 2)).ToString         tmpStr = tmpStr + Chr(64 + CInt(inpDate.Substring(4, 2)))         tmpStr = tmpStr + inpDate.Substring(6, 2)           If Row.time.ToString >= "080000" And Row.time.ToString < "210000" Then             tmpStr = tmpStr + "1"         Else             tmpStr = tmpStr + "2"         End If         Row.LOT = tmpStr     End Sub   End Class

 

처리되는 결과를 확인합니다.