TEXT 형이 포함된 Excel 데이터 가져오기 문제 - Registry 수정

ImportExcel2.dtsx

stuanswer.xls

TEXT 형이 포함된 Excel 데이터 가져오기 문제 - Registry 수정

 

Microsoft Premier Field Engineer

한대성

 

 

Excel à SQL Table 로 처리하는 패키지로 설명드리겠습니다.

 

다음과 같이 원본 엑셀 파일(원본 - stuanswer.xls)에 있는 데이터를 저장할 테이블(stuanswer)를 만듭니다.

USE tempdb

GO

 

CREATE TABLE STUANSWER

(

       UserID VARCHAR(20),

       TestID INT,

       QID INT,

       Answer TEXT,

       FeedBack TEXT,

       MyScore INT,

       IsEval_Commit CHAR(1)

)

GO

 

데이터 가져오기/내보내기 기능을 이용하여 패키지를 만들어도 되고, BIDS에서 직접 패키지를 만들어도 됩니다. 본 예제에서는 데이터 가져오기/내보내기 기능을 이용하여 패키지를 만들겠습니다.

 

다른 일반 문자열들은 자동으로 변환이 되어지는데 텍스트 유형의 열은 자동으로 변환이 이루어지지 않습니다. (Answer 열)

그래서, 다음과 같이 데이터 변환에서 Answer 열에 대해 직접 변환을 설정해 준 후,

데이터 대상에서 이 열로 매핑을 변경해줘야 합니다.

 

 

그럼 되느냐.. 에러가 발생합니다.

 

[Source - stuanswer$ [1]] 오류: The "output column "Answer" (24)" failed because truncation occurred, and the truncation row disposition on "output column "Answer" (24)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

 

첫 단계인 엑셀 파일에서 SSIS의 데이터 원본으로 데이터를 가져오는 부분에서부터 에러가 나네요. 에러 메시지를 보아하니, 데이터가 짤리거나 코드가 안맞다라고 하는데, 엑셀이니깐 유니코드라 데이터 코드가 존재하지 않기 때문에 코드가 안맞다는 것은 아닐테고..데이터가 짤린다고 합니다.

Source-stuanswer$과 데이터 변환(Data Conversion 1) 사이의 녹색 라인을 더블 클릭해서 메타데이터를 확인해 봅시다.

Answer가 255자리로 들어오네요. 그럼 데이터 중에 255자리가 넘는것이 있다라는 것인데..

확인을 위해 다음과 같이 구성해 봅시다.

1.     멀티캐스트 변환을 추가하고, 데이터 원본에서 빨간색 라인을 연결시킵니다. 멀티캐스트 변환은 아무것도 안하는 Dummy 대상으로 사용할 놈입니다.

2.     빨간색 라인을 연결시키면 다음과 같이 창이 뜨는데 여기서 아래와 같이 구성합시다.

Answer 열에서 잘림이 발생했을 때 오류로 처리하지 말고 이 쪽 길(빨간 색)로 에러나는 데이터를 보내라는 것입니다.

 

3.     빨간 색 라인을 더블클릭해서 나타나는 속성 창 중, 데이터 뷰에 부분에서 추가를 눌러 표를 추가합니다.

 

 

 

그런 다음 패키지를 실행시켜 봅시다.

또 에러가 발생하긴 했지만, 첫 번째 단계는 넘어섰네요.

위에서 보는 것과 같이 세 개의 데이터가 255자리를 넘는 놈들입니다.

그럼 엑셀의 255자리가 넘는 데이터는 SQL로 못 가져온단 말인가요? 물론 아닙니다.

 

다음 KB의 내용에 원인이 설명되어 있습니다.

http://msdn.microsoft.com/ko-kr/library/ms141683.aspx

Ÿ   잘린 텍스트. Excel 열에 텍스트 데이터가 포함되어 있음이 확인되면 드라이버는 샘플링하는 값 중 가장 긴 값을 기준으로 데이터 형식(문자열 또는 메모)을 선택합니다. 샘플링하는 행에서 255자보다 긴 값이 검색되지 않으면 이 드라이버는 해당 열을 메모 열이 아닌 255자 문자열 열로 처리합니다. 따라서 255자보다 긴 값은 잘릴 수 있습니다. 메모 열에서 데이터를 가져올 때 데이터가 잘리지 않도록 하려면 샘플링된 행 중 하나 이상의 행에 있는 메모 열에 255자보다 긴 값을 포함시키거나 드라이버가 샘플링하는 행 수를 늘려 이러한 행을 포함하도록 합니다. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel 레지스트리 키에서 TypeGuessRows 값을 늘려 샘플링할 행 수를 늘릴 수 있습니다. 자세한 내용은 PRB: Jet 4.0LEDB 원본에서 데이터를 전송하면 버퍼 오버플로 오류가 발생하면서 실패한다를 참조하십시오.

 

패키지를 실행시킬 서버(or PC)의 레지스트리의 값을 기본값인 8에서 0으로 변경합시다. 참고로 0은 16384개의 행을 이용하여 유형을 정한다는 의미입니다.

 

그러고선 다시 데이터 가져오기/내보내기로 패키지를 만들어서 확인해 봅시다.

바꼈지요?

 

이제 패키지를 실행시켜봅시다.


 

 

참고 글

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

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