조회 변환을 이용한 데이터 동기화 작업

20070118_조회를이용한데이터동기화.dtsx

데이터 관련 업무를 하다 보면 서로 다른 두 서버간의 데이터 동기화 작업을 해야 할 경우가 많이 있습니다.

 

여러 경우가 있겠지만 본 글에서는 다음과 같은 상황에서 SSIS의 조회 변환을 이용한 처리 방법을 설명하도록 하겠습니다.

 

 

자 위의 그림과 같이 Source Table에는 새로운 데이터가 발생이 되며, 다른 서버의 Target Table에는 새롭게 추가된 데이터만 넣어야 하는 경우입니다.

 

만약 같은 서버 내의 SQL 테이블인 경우에는 Left Join 또는 Not Exists 문을 이용하여 처리하면 되겠지만, 원격 서버인 경우에는? Linked Server를 이용하여 쿼리를 사용하기에는 문제가 많겠지요. 그리고, 만약 데이터가 SQL Table이 아니라면? 원본이 텍스트 파일이라면?

 

이러한 경우에 SSIS 패키지를 이용할 수 있습니다.

 

다음과 같이 두 테이블을 준비합니다. 두 테이블은 서로 다른 서버 또는 서로 다른 DB에 만들어도 됩니다. (테스트니깐~^^)

 

CREATE TABLE SOURCE (        SEQ INT IDENTITY,        VAL VARCHAR(10) DEFAULT('AAA'),        CONSTRAINT PK_SOURCE PRIMARY KEY(SEQ) ) GO

본 예제에서는 Source Table은 Localhost 인스턴스의 tempdb에 만들겠습니다.

 

다음과 같이 대상 테이블도 만듭니다.

CREATE TABLE TARGET (        SEQ INT ,        VAL VARCHAR(10),        CONSTRAINT PK_TARGET PRIMARY KEY(SEQ) ) GO

본 예제에서는 localhost\ss2005 라는 SQL 2005 인스턴스의 tempdb에 만들겠습니다.

 

자, 이제 SOURCE 테이블에 값을 좀 넣읍시다. 데이터를 넣을 때 여러 방법이 있겠지만, 만약 SQL Server 2005 Management Studios를 이용한다면 다음과 같은 방법으로 한 번 해 보시기 바랍니다.

 

GO 뒤에 숫자 붙이기.. 이것은 SQL 2005의 기능이 아니라 Management에 추가된 기능입니다. 즉, Management Studios를 이용할 경우, DB가 SQL 2000이더라도 수행됩니다.^^

어쨌든 Source 테이블에 3000개의 데이터를 입력했습니다.

 

자, 그럼 빈 SSIS 패키지를 하나 추가한 후, 데이터 흐름 작업을 추가합니다.

 

데이터 흐름 영역에서 OLE DB 원본을 갖다 놓고선, 속성에서 새로 만들기(N)을 이용하여 SOURCE 테이블에 대한 연결을 만듭니다. (잘 모르시면 앞의 예제들 참고하세요.)

 

열 탭 한 번 눌러서 열 확인해 주시고, 확인을 눌러 창을 닫습니다.

 

이제 도구상자에서 조회 변환을 끌어서 갖다 놓은 후, OLE DB 원본의 녹색선과 연결합니다.

조회 변환을 더블 클릭하여 나타나는 속성 창에서 새로 만들기(N)을 클릭하여 조회용 연결을 만듭니다. 어떤 조회? 여기서는 원본 테이블에서 읽은 데이터를 TARGET 테이블과 비교하는 과정이기 때문에 TARGET 테이블이 있는 DB가 조회 연결이 되겠지요.

 

그러고선, 조회용 결과를 테이블로 하지 말고, 테이블의 키 열만 SELECT 하는 쿼리로 지정합니다.

 

열 탭에서 연결 관계를 확인합니다.

고급은 그냥 구경만~^^ - 이 부분이 중요합니다. 나중에. 테이블의 크기에 따라 캐싱 사이즈를 조절하거나 행 단위 처리 등을 지정해야 할 수도 있습니다.

 

우선은 확인을 눌러 닫고선 도구상자에서 OLE DB 대상을 끌어서 데이터 흐름 영역에 추가합니다.

이 때, 조회 변환의 녹색선과 연결하지 말고, 적색선과 연결합니다.

왜? 조회가 되는 데이터, 즉 매핑이 되는 데이터는 이미 TARGET 테이블에 있는 데이터이기 때문에 여기서는 필요가 없고 조회가 안되는 데이터인 오류 데이터가 결국은 새로 추가된 데이터이기 때문입니다.

 

적색선을 OLE DB 대상에 연결하면 구성 창이 나타납니다.

오류 부분에 행 리디렉션으로 변경한 후 확인을 눌러 창을 닫습니다.

 

이제, OLE DB 대상을 더블 클릭한 후, 다음과 같이 지정합니다.

그런 다음 매핑 탭에서 SEQ와 VAL 열만 지정합니다. (자동으로 지정되어 있을 것입니다.^^)

확인을 눌러 실행시키면 다음과 같이 데이터 전송됩니다.

 

이 상태에서 또 실행 시키면?

전송되는 데이터가 없습니다. 즉, 새로운 데이터가 없다는 것입니다.

 

자, 쿼리 분석기에서 SOURCE 테이블에 신규 데이터를 추가하고 다시 돌려봅시다.

100,000개의 데이터를 추가했습니다.

위와 같이 103,000개의 원본 데이터 중 매핑이 안된 데이터(=신규로 발생한 데이터) 100,000개가 추가됩니다.

 

 

 

데이터가 엄청나게 많은 경우에는 조회 변환의 캐싱 옵션을 조절해 보시기 바랍니다. 캐싱 설정 부분은 다음 글을 참고하세요.

 

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intPage=2&intCategory=0&strSearchCategory=|s_name|s_subject|&strSearchWord=&intSeq=428

 

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intPage=2&intCategory=0&strSearchCategory=|s_name|s_subject|&strSearchWord=&intSeq=540