텍스트 데이터를 이용한 값 업데이트 하기

source.txt

Question

이름 옆에 Y 라고 되어있는 사람의 no을 찾아 우리 테이블에도 update Y해야 되는 거거든요. 파일은 25000(명)줄정도 되고요. 테이블 row수는 150(명)정도로 훨씬 적습니다. 이걸 하루에 한번 배치로 돌리는데요.

우선 설명할 모든 테스트는 tempdb에서 작업하도록 하겠습니다.

------------------------------------------------------------------

--Query: 대상 테이블생성 및 가상데이터 입력

------------------------------------------------------------------

 

use tempdb

 

create table TB_Target (

idx int,

name varchar(10),

CheckYN char(1) )

go

 

insert TB_Target values (2, '이문세', 'N')

insert TB_Target values (3, '홍길동', 'N')

insert TB_Target values (5, '이로로', 'N')

 

select * from TB_Target

 

<결과>

 

대상 테이블과 데이터가 준비되었으니 SSIS에서 작업할 내용을 설명하겠습니다.

텍스트 파일의 데이터를 테이블에 반영하는 방법에는 여러 가지 솔루션이 있을 것 입니다.

여러 가지 솔루션 중에서 3가지 정도의 방안을 설명하겠습니다.

1. 임시 테이블을 이용한 처리
2. 임시 테이블을 사용하지 않고 OLE DB 명령을 이용한 직접 처리


첫 번째 방법...
첫 번째 방안에서는 가장 일반적으로 생각할 수 있는 방법으로 해결해보겠습니다.

 

큰 흐름은,

업데이트할 텍스트의 내용을 임시 테이블에 가져온다.

임시 테이블에 있는 값을 조회하여 대상테이블의 값을 변경한다.

입니다.

 

1.     ‘도구 상자 à 제어 흐름 항목 à SQL 실행 작업’을 하나 생성합니다.

2.     이후에 어떤 작업을 하는 것인지 확실히 알 수 있도록 이름을 바꿔줍니다.

‘임시 테이블 생성’이라고 변경하겠습니다.

 

<결과>

3.     임시 테이블 생성 항목을 더블 클릭하여 연결할 대상 DB와 쿼리를 지정하겠습니다.

먼저, Connection 속성을 클릭하여 <새 연결…>을 선택합니다.

 

OLE DB 연결 관리자 구성 창이 뜨면 새로 만들기를 클릭합니다.

 

연결 관리자 창이 뜨면 연결할 데이터 베이스의 정보를 입력합니다.

앞에서 환경을 가정했던 대로 연결할 데이터베이스는 tempdb로 설정합니다.

 

확인을 하고 나오면 SQL 실행 작업 편집기의 Connection 속성에 연결이 생성된 것을 확인할 수가 있습니다.

다음은 SQLStatement 속성을 클릭하여 아래의 쿼리를 입력합니다.

if not exists (select 1 from sys.sysobjects where name = 'Temp_Target') create table Temp_Target (idx int)

 

 

확인을 클릭하여 제어 흐름 창으로 나옵니다.

그리고, ‘도구 상자 à 제어 흐름 항목 à 데이터 흐름 작업’을 생성하고 앞에서 작업한 ‘임시 테이블 생성’과 연결합니다. 데이터 흐름 작업의 이름은 ‘파일 읽기’라고 수정하겠습니다.

 

그리고 데이터 흐름 작업의 DelayValidation 속성을 True로 설정합니다. 이 속성에 대한 자세한 내용은 다른 강의를 참고하시면 될 것 같습니다. 앞에서 실행되는 작업에서 임시 테이블을 생성했고 그 임시 테이블에 텍스트 데이터를 입력할 것입니다. 그런데 패키지 실행 전 Validation 검사를 하면 그 임시 테이블이 없기 때문에 실행도 하기 전에 에러가 발생하기 때문에 패키지 실행이 되지 않습니다. 그것을 피하기 위해 DelayValidation 속성을 True로 설정하겠습니다.

 

데이터 흐름 작업(파일 읽기)을 더블 클릭해서 데이터 흐름 디자인 모드로 전환합니다.

‘도구 상자 à 데이터 흐름 원본 à 플랫 파일 원본’을 추가합니다. 이름은 ‘원본 파일’이라고 변경하겠습니다. 더블 클릭하여 ‘플랫 파일 원본 편집기’ 창을 띄워서 새로 만들기를 클릭합니다.

 

플랫 파일 연결 관리자 편집기 창이 뜨면 원본 파일을 선택해줍니다.

 

좌측의 열 메뉴를 선택합니다. 그러면 자동으로 열 매핑이 되고 미리 보기 창에서 매핑된 파일의 내용을 볼 수 있습니다.

 

그리고 고급 메뉴를 선택합니다. 일련 번호의 데이터 형식이 문자열로 설정되어 있는데 정수형으로 변경해줍니다. DataType 속성을 ‘부호 없는 4바이트 정수’로 설정해줍니다. 또 Name 속성을 ‘idx’로 변경해줍니다.

 

확인을 클릭하여 데이터 흐름 창으로 나오면 하단에 있는 연결 관리자에 지금까지 연결한 DB와 파일 연결에 대한 정보를 볼 수 있습니다.

DB연결 관리자는 ‘TargetDB’로 파일 연결 관리자는 ‘SourceFile’로 변경하겠습니다.

 

‘도구 상자 à 데이터 흐름 원본 à 조건부 분할’을 추가해서 앞에서 작업한 ‘원본 파일’과 연결해줍니다.

 

‘조건부 분할’을 더블 클릭해서 편집기를 열고 ‘열2’를 조건에 드래그하여 추가합니다.

조건 식을 [열 2] == “Y”로 입력하고 이름을 적절히 정해줍니다.

 

이제 텍스트 데이터를 임시 테이블에 입력하는 작업을 하겠습니다. 하지만 다음 작업을 하기 전에 임시 테이블을 한 번 생성해주겠습니다. 왜냐하면 입력할 대상 테이블(임시 테이블)을 지정해줘야 하는데 tempdb에 그 테이블이 없으면 테이블 목록에 보이지 않기 때문입니다.

 

다음의 쿼리를 DB에서 실행합니다.

 

use tempdb if not exists (select 1 from sys.sysobjects where name = 'Temp_Target')              create table Temp_Target (idx int)

 

다시 SSIS의 데이터 흐름 디자이너 모드로 돌아와서 ‘도구 상자 à 데이터 흐름 대상 à OLE DB 대상’을 추가하고 ‘조건부 분할’과 연결해줍니다.

입/출력 선택 창이 뜨는데 위에서 설정해준 업데이트 대상으로 설정해줍니다.

 

‘OLE DB 대상’을 더블 클릭하여 편집기 창을 띄웁니다.

OLE DB 연결 관리자는 앞에서 설정해둔 TargetDB로 지정하고 데이터 액세스 모드는 테이블 또는 뷰 – 빠른 로드로 지정합니다. 테이블 또는 뷰 이름에는 바로 위에서 임시로 생성한 Temp_Target테이블로 지정합니다.

 

좌측 메뉴에서 매핑을 클릭합니다. 앞에서 ‘데이터 변환’ 작업에서 idx라는 이름으로 컬럼을 생성해줬기 때문에 매핑 메뉴를 클릭하면 자동으로 열이 매핑됩니다.

 

확인을 클릭하여 데이터 흐름 디자이너로 나오면 다음과 같이 데이터 흐름 작업이 디자인 된 것을 확인할 수 있습니다.

 

상단의 제어 흐름 탭을 선택하여 제어 흐름 디자인 모드로 전환합니다.

제어 흐름 창에 ‘도구 상자 à 제어 흐름 항목 à SQL 실행 작업’을 추가합니다.

이름을 ‘업데이트 및 임시 테이블 삭제’로 변경합니다.

 

‘업데이트 및 임시 테이블 삭제’ 항목을 더블 클릭하고 Connection 속성은 TargetDB로 지정합니다. SQLStatement 속성은 아래의 쿼리를 입력합니다.

update dbo.TB_Target set CheckYN = 'Y' where idx in (select * from tempdb.dbo.Temp_Target with (nolock))   if exists (select 1 from sys.sysobjects where name = 'Temp_Target') drop table dbo.Temp_Target

 

 

확인을 클릭하여 편집기를 빠져나옵니다.

모든 작업의 작성이 완료되었습니다.

 

확인을 해보겠습니다.

위에서도 확인했지만 현재 대상 테이블에는 아래와 같은 데이터가 입력되어 있습니다.

 

패키지를 실행해봅니다. F5로 디버깅을 해줍니다. 정상적으로 작성이 됐다면 제어 흐름과 데이터 흐름에서 각각 아래와 같은 결과를 확인할 수 있습니다.

 

select * from TB_Target

쿼리를 실행하여 테이블의 데이터를 확인합니다.

 

 

 

두 번째 방법.

앞에서 설명한 첫 번째 방법에선 임시 테이블(Temp_Target)을 사용했었습니다. 두 번째 방법에선 이 임시 테이블을 사용하지 않는 방법을 생각해보겠습니다.

                 

앞에서 구현한 첫 번째 방법에서 조금 수정해보겠습니다.

 

<제어 흐름>

제어 흐름 디자인 창에서 과감히 ‘임시 테이블 생성’과 ‘업데이트 및 임시 테이블 삭제’를 삭제합니다.

제어 흐름 디자인 창에는 파일 읽기(데이터 흐름) Task 하나만 남았습니다.

 

<데이터 흐름>

데이터 흐름 디자인 창에서 마지막에 추가한 ‘OLE DB 대상’을 삭제합니다.

대신, ‘도구 상자 à 데이터 흐름 변환 à OLE DB 명령’을 추가하고 ‘조건부 분할’과 연결 시켜줍니다. 유형 1에서 했던 것 처럼 ‘입/출력 선택’ 창이 뜨면 출력을 ‘업데이트 대상’으로 설정해줍니다. ‘OLE DB 명령’을 더블 클릭해서 편집창을 띄웁니다. 연결 관리자를 TargetDB로 설정합니다.

 

구성 요소 속성 탭의 SqlCommand 속성에 쿼리를 입력합니다.

update TB_Target set checkYN = 'Y' where idx = ?

 

SqlCommand 속성에서 ? 매개변수를 주었기 때문에 열 매핑 탭에서 idx와 매핑된 것을 확인합니다.

 

데이터 흐름 디자인 창에는 아래와 같이 구성되었습니다.

 

완료되었습니다.

패키지를 실행하기 전에 앞에서 반영한 테이블의 값을 초기화 하겠습니다.

use tempdb   truncate table TB_Target   insert TB_Target values (2, '이문세', 'N') insert TB_Target values (3, '홍길동', 'N') insert TB_Target values (5, '이로로', 'N')   select * from TB_Target

 

F5로 디버깅을 해서 실행한 후 테이블을 확인해보면 첫 번째 방법과 동일한 결과를 볼 수 있습니다.

그럼 왜 간단한 이 방법을 먼저 설명하지 않고 조금 더 복잡하고 임시 테이블도 사용하는 방법을 먼저 설명했을까요?

데이터의 수에 따라서 부하와 속도에 차이가 있기 때문입니다. 두 번째 방법의 ‘OLE DB 명령’을 보시면 알겠지만 이 방법은 건 by 건으로 업데이트를 하는 커서 방식입니다. 데이터 수가 많을수록 부하도 커지고 속도는 느려집니다.