조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리

조회 변환 및 OLE DB 명령 변환을 이용한 데이터 처리

 

 

한대성

 

  

Question

구성하고 픈 작업은..... Table1  테이블에서 Code 값 한개 받아와서 그 값을 조건으로 해서 Table2에서 결과를 뽑아 Table3에 insert하는 패키지를 구성하고 싶은데요... Code 값을 받아와서 넘겨주는게 잘 안되네요.

간단히 다음과 같은 상황을 만들어 보죠.

TABLEA 에는 코드성 테이블이 있습니다. (예 : A, B, C, …)

TABLEB 에는 각 코드에 해당하는 데이터들이 있습니다.

TABLEA 에서 코드 값 하나를 읽어서 B에서 이 코드 값의 건수를 계산한 후, TABLEC에 저장하는 패키지를 만들어 봅시다.

 

 

USE TEMPDB GO DROP TABLE TABLEA DROP TABLE TABLEB DROP TABLE TABLEC GO   CREATE TABLE TABLEA (                   SEQ INT IDENTITY,                   COLID VARCHAR(10) ) GO   INSERT TABLEA(COLID) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' GO   CREATE TABLE TABLEB (                   COLID VARCHAR(10),                   RANDDATA INT DEFAULT(RAND()*100) ) GO   INSERT TABLEB(COLID) SELECT 'A' UNION ALL SELECT 'A' UNION ALL SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'B' UNION ALL SELECT 'C' GO   CREATE TABLE TABLEC (                   COLID VARCHAR(10),                   CNT INT ) GO

 

 

다음과 같이 SSIS 패키지를 구성합니다. 데이터 흐름에서 TABLEA의 값을 읽어오는 OLE DB 데이터 원본을 구성합니다.

 

 

TABLEA에서 읽어온 코드 값으로 TABLEB에서 건수를 읽어오는 작업을 구현하기 위해 조회를 이용하는 방법부터 먼저 설명하겠습니다.

조회(Lookup) 변환을 추가한 후, OLE DB 원본(TABLEA)과 연결하고 속성 부분에 다음과 같은 쿼리를 입력합니다.

 

열 탭에서 CNT 부분을 체크하고,

 

고급 탭에서 메모리 제한 사용을 선택한 후, SQL 문 수정을 체크하고 매개 변수(P) 버튼을 클릭해서 COLID가 매개 변수로 지정되도록 선택합니다. 이렇게 설정하는 이유는 미리 전 COLID 열에 대해 집계를 하는 것이 아니라, 입력되는 COLID에 대해서만 집계 연산을 수행토록 하기 위함입니다.

 

 

확인을 눌러 편집기를 닫은 다음, OLE DB 대상을 추가해서 연결하고, 다음과 같이 COLID,와 CNT 열을 매핑 시킵니다.

 

실행해 봅시다.

 

 

 

 

두 번째 방법입니다.

 

다음과 같이 입력 값에 대해 연산을 수행하는 저장 프로시저를 만듭시다.

CREATE PROC USP_CALCCNT                   @COLID VARCHAR(10) AS                   INSERT TABLEC(COLID, CNT)                   SELECT @COLID,                                    (SELECT COUNT(*) AS CNT FROM TABLEB WHERE COLID = @COLID) GO

 

 

OLE DB 명령 변환을 추가한 후, OLE DB 원본(TABLEA)과 연결합니다.

 

OLE DB 명령 편집기를 연 다음, 연결 관리자 탭에서 저장 프로시저가 있는 DB 연결을 설정하고, 구성 요소 속성 탭에서 다음과 같은 명령을 입력합니다.

 

열 매핑 탭에서 다음과 같이 설정한 후, 확인을 눌러 편집기를 닫습니다.

 

실행해 봅시다. TABLEC의 결과는 동일합니다.

 

첫 번째 방법(LOOKUP)을 사용했을 경우에는, 만약 TABLEB에 COLID가 없는 경우(예 COLID=D) 에러가 발생합니다. 이를 방지하기 위해 에러 처리 작업을 해 줘야 합니다. (참고 : LOOKUP이 안되서 발생하는 에러이며, SQL 2008에서는 에러가 발생하지 않도록 변경되었습니다.) 대신, TABLEA, TABLEB, TABLEC가 각기 서로 다른 DB에 있는 경우에는 유용하겠지요.

두 번째 방법(OLE DB 명령)을 사용했을 경우에는, 동일한 인스턴스 내에 있는 테이블들에 대해서는 성능이 괜찮지만, 서로 다른 인스턴스에 있는 테이블들인 경우에는 성능이 문제가 될 수 있습니다.

 

이와 같은 간단한 예를 참고해서 변형해 보시기 바랍니다.  @.@