엑셀 파일과 테이블 데이터 동기화하기

엑셀데이터동기화.zip

엑셀 파일과 테이블 동기화 구현

 

한대성

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

 

Question : MS-SQL 2005가 설친된 서버의 특정 폴더의 특정 엑셀 파일을 읽어서 DB에 Import시키려고 합니다. 이걸 패키지로 만들어 작업 스켸줄을 걸어서 매일 1시간에 한번씩 읽어서 DB에 있는 값을 비교 한후 새로운 데이터만 Impot하려고 하는데...또는 기존 테이블의 데이터를 TRUNCATE(인덱스와 PK때문에 DROP은 안뎁니다..ㅠㅠ)시키고 계속 데이터가 쌓이는 엑셀 파일을 Import해도 괜찮습니다..(이왕이면 첫번째 방법을 원하긴 하지만..ㅠㅠ) 이걸 패키지로 만드는 패턴도 예제라도 알려주시면 정말 감사하겠습니다..ㅠㅠ

 

 

다음과 같은 질문이 들어와서 이와 관련되어 간단한 예제를 만들어 설명드리겠습니다.

기존의 조회 변환을 이용한 데이터 동기화 작업과 비슷하지만 엑셀 파일을 이용한다는 차이가 있긴 하지만 거의 비슷합니다..

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=682  

 

 

우선, 다음과 같은 InputExcel.xls이라는 예제 엑셀 파일이 있다고 하고,

 

   

이 데이터를 localhost의 tempdb의 TargetTable 테이블에 추가 시키는 경우로 예를 들겠습니다.

USE TEMPDB GO   --임시 테이블 생성 IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'TARGETTABLE')        DROP TABLE TARGETTABLE GO   CREATE TABLE TARGETTABLE (        SEQ INT NOT NULL,        VALUE1 VARCHAR(20) NULL DEFAULT('AAAAA'),        CONSTRAINT PK_TARGETTABLE PRIMARY KEY (SEQ) ) GO --임시 데이터 적재(1 ~ 1000까지 데이터 입력) DECLARE @I AS INT SET @I = 1 WHILE (@I<=1000) BEGIN        INSERT TARGETTABLE(SEQ) VALUES (@I)        SET @I = @I + 1 END GO --10개의 임의 데이터 삭제 DELETE FROM TARGETTABLE WHERE SEQ IN (118,141,224,252,269,332,340,342,349,359) GO

 

엑셀 파일(InputExcel.xls)에서 데이터를 읽어와서 TEMPDB의 TARGETTABLE 중, Seq값이 없는 건에 대해서만 추가 입력하도록 하는 예입니다.

 

결론부터 먼저 말씀 드리자면, 29개의 엑셀 데이터 중, 10개만 신규 데이터입니다. 이 건들만 TARGETTABLE에 들어가게 됩니다.

 

 

1. SSIS 패키지에서 데이터 흐름 작업을 추가한 후, Excel 원본을 선택하고 위의 예제 Excel 파일을 지정합니다.

 

2. 연결 관리자에서 OLE DB 연결을 추가한 후, 대상 DB(본 예제에서는 tempdb)를 가리키는 연결을 만듭니다.

 

3. 데이터 변환을 추가한 후, Excel 원본과 연결하고 Seq열을 배정밀도 부동 소수점 수[DT_R8]에서 부호 없는 4바이트 정수[DT_I4]로 변경하는 새로운 열 Seq1을 만듭니다. 또한 Value1을 선택한 후, 문자열[DT_STR] 20자리로 변경하는 새로운 열 Value2를 만듭니다. 이런 단계를 거치는 것은 엑셀 데이터가 숫자는 Real 형, 문자는 NVARCHAR(255)형으로만 들어오기 때문에 테이블에 맞게끔 변경하는 역할을 합니다.

             

 

3. 조회 변환을 추가한 후, 데이터 변환과 연결을 맺고, 다음과 같은 조회용 쿼리를 입력합니다.

SELECT Seq FROM TargetTable

 

4. 열 탭에서 보면 자동으로 입력 Seq열과 조회 Seq열이 매핑되어 있습니다. 이 매핑을 제거하고 Seq1과 조회 열의 Seq를 매핑 시킵니다.

               

5. 이제 OLE DB 대상을 추가한 후, 조회 변환의 적색 선과 연결시킵니다. 즉, 매핑이 안되어서 오류가 발생한 데이터를 OLE DB 대상이 가리킬 TargetTable로 저장하는 것입니다. 여기서, 매핑이 안되었다라는 것은 TargetTable의 Seq열과 매핑이 안되었다라는 것이며, 이 데이터들이 신규 데이터인 것입니다.

적색 선을 연결시키면 다음과 같이 오류 출력 구성 창이 나타납니다. 오류 부분에서 구성 요소 실패로 되어 있는 부분을 행 리디렉션으로 변경합니다.

            

 

6. OLE DB 대상에서 대상 테이블을 TargetTable로 지정하고, 매핑 탭에서 자동으로 연결되는 매핑선을 다 지워주고 Seq1을 SEQ로, Value2를 Value1으로 매핑시킵니다.

 

 

 

7. 패키지를 실행시켜서 처리되는 결과를 확인합니다.

            

 

29개의 데이터를 엑셀 파일에서 읽은 후, 테이블에 없는 10개의 데이터를 저장시켰습니다.

다시 한 번 패키지를 실행시키면 다음과 같습니다. 이미 모든 건들이 다 들어가 있기 때문에 추가되는 행이 없습니다.

            

 

 

 

첨부 패키지 및 파일을 참고하시기 바랍니다..

 

좋은 하루 되세요..