조회 변환을 이용한 데이터 처리

20070201_ExcelDataMapping.dtsx

InputData.xls

다음과 같은 질문 메일을 받았습니다.

 

Question : 접수 프로세스에 접목시키려는 부분입니다. 어떤 지원부분에 있어.. 원서를 접수 받고.. 그 접수자의 별도 제출서류가 있을 경우 그 제출서류에 대한 도착여부를 데이터로 저장하고 싶습니다.   1. 엑셀파일로 다수의 수험번호, 도착상태ID, 간략 메모 값을 받습니다. A. (상태 ID값: 1=불필요, 2=미도착, 3=도착, 4=서류미비) B. 간략메모 : 어떤 서류가 미비 되었는지 간략 메모를 넣을 수 있습니다.(일단 여기선 배재) 2. A라는 테이블에 해당 ROW의 수험번호를 조회하여 IDX값을 받아옵니다. 3. 받아온 IDX값과 도착상태ID값을 B라는 테이블에 저장합니다. (여기서.. 중요한 점은 기존에 해당 IDX값이 저장되어있다면 Update, 없으면 Insert를 하고 싶습니다.)   For루프 컨테이너를 사용해서 해볼까 하는데.. 어떻게 진행해야 할까요?   원본 데이타는 엑셀이고 비교테이블은 A테이블 대상테이블은 B테이블입니다.   기존 DTS 2000으로 작업을 진행했었는데 엑셀 데이타 1000건을 넘겼을 경우 잦은 오류가 발생 하더라구요. 해서 이번 좋은 기회에 바꿔보려고 합니다.

 

요런(^^) 조건을 만족하는 패키지를 만들어봅시다.

 

자, 조건을 좀 더 나누어서 세 가지로 진행하겠습니다.

1. 위의 요구 사항을 조회 변환을 이용하여 구현하기

2. 입력되는 엑셀 파일이 여러 개 일 때 Foreach 루프 컨테이너 씌우기

 

이렇게 두 가지 형태로 만들어보겠습니다.

 

 

우선 다음과 같은 준비 작업을 하겠습니다.

 

조회 테이블 A

USE TEMPDB GO   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TABLEA' AND XTYPE = 'U') DROP TABLE TABLEA GO   --코드 테이블 CREATE TABLE TABLEA (              IDX INT IDENTITY,              IDNO CHAR(8) NOT NULL ) GO   --임의 코드값 발생 SET NOCOUNT ON DECLARE @I AS INT SET @I = 20000   WHILE @I>=1 BEGIN INSERT TABLEA(IDNO) VALUES ('A' + RIGHT('00000000' + CAST(@I AS VARCHAR), 7)) SET @I = @I - 1 END SET NOCOUNT OFF GO   --매핑 안되는 경우에 대한 처리를 위해 일부 데이터를 지웁니다. DELETE FROM TABLEA WHERE IDNO IN ('A0000324', 'A0000225', 'A0000765','A0000758','A0000104','A0000554') GO   SELECT TOP 10 * FROM TABLEA /* IDX IDNO -------------------------- 1            A0020000 2            A0019999 3            A0019998 4            A0019997 5            A0019996 6            A0019995 7            A0019994 8            A0019993 9            A0019992 10          A0019991 */

 

 

결과 저장 테이블 B : 매핑 되는 건이 입력 or 수정 됩니다.

USE TEMPDB GO   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TABLEB' AND XTYPE = 'U') DROP TABLE TABLEB GO   --매핑되는 데이터 결과 테이블 CREATE TABLE TABLEB (              순번 INT IDENTITY,              수험번호 CHAR(8),              도착상태 TINYINT,              IDX INT,              간략메모 TEXT ) GO

 

 

결과 저장 테이블 C : 입력 데이터 중에서 테이블 A와 매핑이 안 되는 건이 저장됩니다.

USE TEMPDB GO   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TABLEC' AND XTYPE = 'U') DROP TABLE TABLEC GO   --매핑 안 되는 데이터 결과 테이블 CREATE TABLE TABLEC (              순번 INT IDENTITY,              수험번호 CHAR(8),              도착상태 TINYINT,              간략메모 TEXT ) GO

 

 

입력 엑셀 데이터 (10,000개) : D:\InputData.xls

수험번호	도착상태	간략메모
A0006853	2	메모메모A0006853
A0006910	1	메모메모A0006910
A0000386	2	메모메모A0000386
A0001959	4	메모메모A0001959
A0002282	3	메모메모A0002282
A0003198	4	메모메모A0003198
A0009000	4	메모메모A0009000
A0005821	4	메모메모A0005821
A0000036	2	메모메모A0000036
A0004206	1	메모메모A0004206
A0004965	4	메모메모A0004965
A0007500	1	메모메모A0007500
A0004815	4	메모메모A0004815
A0005155	4	메모메모A0005155
A0009572	4	메모메모A0009572
A0008573	1	메모메모A0008573
A0006463	4	메모메모A0006463
A0000186	2	메모메모A0000186
A0004464	1	메모메모A0004464
A0009642	2	메모메모A0009642
A0007817	4	메모메모A0007817
A0002225	1	메모메모A0002225
A0000083	1	메모메모A0000083
A0002475	1	메모메모A0002475
A0003398	3	메모메모A0003398
A0000585	2	메모메모A0000585
A0001938	3	메모메모A0001938

 

 

 

자. 그럼 우선 작업 유형부터 생각해 봅시다.

1) 엑셀 데이터를 읽어서 수험 번호에 해당하는 IDX값을 TABLEA로부터 검사하기

 : 조회 변환을 이용해도 되고, 조인 변환을 이용해도 됩니다. 장단점이 있겠지만, 저는 여기서 조회 변환을 쓰겠습니다. 조인 변환은 다들 잘 아시겠지요? (쿼리 잘 하시잖슴까~^^)

2) 요구 사항에는 없었는데, 혹시나 TABLEA에 매핑 안 되는 엑셀 데이터가 있겠지요? 매핑 안 되는 데이터는 별도의 테이블 C(TABLEC)에 저장합니다.

3) 매핑 되는 데이터 이더라도 TABLEB에 이미 들어가 있는 경우에는 UPDATE, 그렇지 않은 경우에는 INSERT 합니다. 이것은? 이것도 조회 변환을 이용합니다.

 

오케..데이터 읽고 조회 변환하고 하는 거 보니깐, 이건 데이터 흐름 작업이네. 라고 생각되시지요?

 

제어 흐름 영역에다가 데이터 흐름 작업 하나 추가합니다. 이왕 추가하는 김에 연결 관리자에서 tempdb를 지정하는 OLE DB 연결도 추가하고, 엑셀 파일에 대한 엑셀 연결도 추가합니다. 혹시나 연결 추가 방법 잘 모르시면 앞의 기본 강좌들을 참고하시기 바랍니다..

 

 

데이터 흐름 작업을 클릭하면 데이터 흐름 영역으로 넘어가죠? 자, 그럼 우선 엑셀 원본을 추가하고선, 엑셀 연결을 지정합니다.

 

참고로, 엑셀 파일의 문자열 데이터는 모두 UNICODE입니다. 따라서 nvarchar 형태로 매핑이 되어야 합니다. 하지만, 어라~! 결과가 저장하는 테이블은 varchar로 만들었네요!… 쩝..

여기서 SSIS가 조금씩 싫어질 수도.^^

SQL 2000 DTS에서는 그냥 변형 시켰습니다. 아니, 시켜줬습니다. Implicit Conversion(=암시적 or 암묵적(@.@) 변환). nvarchar라도 DTS 맘대로 그냥 varchar형으로 알아서 변경해서 넣어버렸지요. 요런 특징이 편한 분들도 계셨을 건데, 제 경우에는 요것 땜시로 몇 번 크게 고생해서 싫어졌습니다.

SSIS는? 절대 그런 것 없습니다. 특히나 유니코드와 비유니코드 간에는 자동으로 안 바꿉니다. 그래서 파생 열 변환이나 데이터 변환과 같은 변환을 이용해서 직접 변경해줘야 합니다. 여기서는 파생 열 변환을 이용하겠습니다. 파생 열 변환을 추가한 후, 엑셀 데이터 원본과 연결합니다. 그러고선, 파생 열 변환에 다음과 같이 입력합니다.

 

 

 

 

중간 부분만 좀 더 확대해서 보면~

 

새 열로 추가하면서도 파생 열 이름은 똑같이 [수험번호], [도착상태], [간략메모]와 같이 원본 입력 열 이름과 같네요.. (이렇게도 되더라고요 ㅎㅎ.. 그냥 해 봤습니다..)

 

자, 이제 조회 변환을 추가하여 파생 열 변환과 연결합니다.

그런 후, 조회 변환의 속성은 다음과 같이 지정합니다.

여기서 직접 테이블을 선택해도 되지만, 가급적이면 필요한 열만 포함하는 쿼리를 이용하도록 습관화(!) 합시다.

열 탭에서 다음과 같이 IDX열을 체크합니다. 이것은 조회 변환을 한 후에 IDX 열을 포함시키라는 것입니다.

고급 탭은 나중에 테스트 해보세요..요리 조리..

 

여기서 한 번 더 설명하겠습니다. 뭘? 조회 변환의 특징~

조회 변환은 입력 데이터가 조회 데이터와 매핑 되면 딱 한 놈만 매핑 합니다. 만약 조회 테이블에 여러 데이터가 있더라도 한 놈만 매핑 시킵니다. (테스트 했을 때에는 마지막 놈이었는데, 좀 불확실~^^)

그리고, 매핑 안되면 오류로 처리됩니다. 조인 변환과의 차이지요? 조인의 경우에는 매핑 안되면 땡(!)입니다. 하지만, 이 변환은 에러로 처리해줍니다. 고맙죠~^^ 그럼? 실패?

기본적으로는 작업 실패입니다. 하지만, 아래에 있는 오류 출력 구성(G)을 이용해서 오류에 대한 처리 방법을 변경할 수 있습니다. 오류를 무시하도록 할 수도 있고, 오류가 발생한 데이터를 다른 경로로 출력할 수도 있습니다.

여기서는 다른 경로로 출력하겠습니다. 우선 OLE DB 대상을 하나 추가한 후에 조회 변환의 적색 선과 연결시킵니다. 그러면 다음과 같은 창이 자동으로 뜹니다. 요 창이 바로 조회 작업의 오류에 대한 처리를 설정하는 창이며, 위의 그림에서 오류 출력 구성(G)을 눌렀을 때 나타나는 것과 같은 창입니다.

 

오류 부분을 그림과 같이 행 리디렉션으로 설정합니다. 그런 후 OLE DB 대상(그림에서 매핑 안 되는 데이터)을 더블 클릭해서 편집기를 띄운 후, 대상 테이블은 TABLEC로 지정합니다.

또한 매핑 탭에서 다음과 같이 열을 지정합니다. 굳이 여기서는 오류 정보를 넣을 필요 없기 때문에 세 열만 지정합니다.

사용 가능한 입력 열에 보면 조로코롬(@.@) 되어있지요? 즉, 앞에서 말한 것처럼 열 이름을 동일하게 가지고 가더라도 변환 이름이 앞에 붙어서 구분됩니다.

 

자, 이제는 매핑 된 데이터에 대한 처리를 합시다. 매핑이 된 데이터 이더라도 TABLEB에 이미 들어가 있으면 Update, 없으면 Insert를 해야 합니다. 요놈도 역시 조회 작업으로 해 봅시다. 또 하나의 조회 변환을 추가한 후, 기존의 조회 변환과 연결시킵니다.

SELECT 수험번호 FROM TABLEB (NOLOCK)

으로 쿼리를 입력하고선, 열 탭에서 다음과 같이 그냥 연결만 하고 열 추가를 하지 않습니다. (할 필요 없겠죠? 그냥 검사만 하는 것이기 땜시로..)

 

자, 만약에 매핑이 된다면? 이 데이터(입력 데이터)는 TABLEB에 이미 있는 데이터라는 것을 말하겠지요. 매핑이 안 된다면? TABLEB에 없는 데이터이지요.

따라서, 여기서도 위와 같이 오류 출력을 구성해서 오류가 난 데이터(=매핑이 안된 데이터)를 TABLEB에 입력하는 것을 구성합니다. 새로운 OLE DB 대상을 추가한 후, 조회 변환의 적색 선과 연결하고선, 위에서와 같이 오류 출력 구성을 행 리디렉션, 그리고 조회 변환의 편집기 창에서 열을 지정해줍니다.

 

 

 

자, 이제 하나 남았네요. 기존에 있는 데이터인 경우엔 업데이트 해라~!

요놈은? OLE DB 명령을 이용합니다.

일단 추가.. OLE DB 명령. 조회 변환의 녹색 선과 연결.

 

OLE DB 명령을 더블 클릭해서 나타나는 편집기에서 연결 관리자를 지정합니다.

 

구성 요소 속성 탭의 SqlCommand 부분에 다음과 같은 쿼리를 입력합니다.

 UPDATE TABLEB SET IDX = ? WHERE 수험번호= ?

 

열 매핑 탭에서는 다음과 같이 연결해 줍니다.

 

끝~!. 한 번 돌려봅시다.

 

매핑 안된 데이터 6개는 TABLEC에 저장됩니다.

 

TABLEB에는 기존 데이터가 없기 때문에 모두 새로 입력됩니다. 9,994개.

 

만약 다시 돌린다면? 다음 그림과 같겠지요?

 

마지막으로 성능을 위해서 인덱스를 잡아주는 쎈스~(테이블 만들 때 해줘야겠죠?)

CREATE INDEX IDX_TABLEA_1 ON TABLEA(IDNO) GO   CREATE INDEX IDX_TABLEB_1 ON TABLEB(수험번호) GO

 

 

다음 강좌는 동일한 환경에서 만약에 엑셀 파일이 여러 개일 때 Foreach 루프 컨테이너로 씌우는 것을 하겠습니다.