SQL Server : DTS_E_PRIMEOUTPUTFAILED (데이터 가져오기 중 오류)

※ 예전에 작업했던 내용들을 기억/기록하기 위해 남기는 포스팅입니다. 

 

SQL Server에서 데이터 가져오기 ( Excel -> SQL Server ) 중 오류가 발생하였습니다. 

오류 내용은 아래와 같습니다. 

- 실행 중 (Error)
Messages
* 오류 0xc020901c: 데이터 흐름 태스크 1: 원본 - Sheet1$.출력[Excel 원본 출력]의 원본 - Sheet1$.출력[Excel 원본 출력].열[컬럼명]에 오류가 있습니다. 반환된 열 상태는 "텍스트가 잘렸거나 하나 이상의 문자가 대상 코드 페이지와 맞지 않습니다."입니다.
(SQL Server 가져오기 및 내보내기 마법사)

* 오류 0xc020902a: 데이터 흐름 태스크 1: 잘림이 발생했기 때문에 "원본 - Sheet1$.출력[Excel 원본 출력].열[컬럼명]"이(가) 실패했으며 "원본 - Sheet1$.출력[Excel 원본 출력].열[컬럼명]"에서의 잘림 행 처리는 잘림 발생 시 실패하도록 지정되어 있습니다. 해당 구성 요소의 지정한 개체에서 잘림 오류가 발생했습니다.
(SQL Server 가져오기 및 내보내기 마법사)

* 오류 0xc0047038: 데이터 흐름 태스크 1: SSIS 오류 코드 DTS_E_PRIMEOUTPUTFAILED. 원본 - Sheet1$에서 PrimeOutput 메서드가 오류 코드 0xC020902A을(를) 반환했습니다. 파이프라인 엔진이 PrimeOutput()을 호출할 때 이 구성 요소가 오류 코드를 반환했습니다. 이 오류 코드의 의미는 구성 요소에 따라 다르지만 파이프라인의 실행을 중지할 수도 있는 오류입니다. 오류에 대한 자세한 정보와 함께 이 오류 메시지보다 먼저 게시된 오류 메시지가 있을 수도 있습니다.
(SQL Server 가져오기 및 내보내기 마법사)

 

원인 : 

 

데이터를 가져올(목적지) 테이블의 컬럼 데이터 타입은 varchar(max)로 되어있으나, 

데이터 원본 Excel 컬럼(열) 데이터 타입이 기본 varchar(255)로 잡혀서 생기는 문제  

 

 

검색 : 

 

The OLEDB provider for excel will attempt to automatically determine the DataTypes based off of the first 8 rows of data, this can be set with the HDR=Yes/No property in the connection string. Additionally, there are multiple types that it can apply to text columns. The memo type holds over 255 characters, so if none of the first 8 rows have that then it will incorrectly set the data type.

The way to change this is by changing a registry setting called TypeGuessRows, as described here: Microsoft Support

NOTE: The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. So if you have a very large file make sure the biggest rows are first.

- Excel 용 OLEDB provider는 데이터의 처음 8개 행을 기반으로 DataType을 자동으로 결정한다. 

- 원본 Excel 데이터의 처음 8개 행에 255자가 넘는 문자열이 없으면 자동으로 varchar(255)로 결정한다. 

- 위 인용문 URL (stack overflow) [새창] 

 

 

해결 방법 : 

 

1. TypeGuessRows라는 레지스트리 설정을 변경 

   - 기본 설정 값은 8, 유효한 값 범위는 0 ~ 16 

   - 값이 0일 경우 처음 16384개 행을 읽고 결정 

 

   ==> 레지스트리 설정을 변경하는게 싫고, 16384개 행을 읽게 하는 게 싫었습니다. 그래서 이 방법은 패스 

 

2. 255자가 넘는 가상 데이터를 넣고, 가져오기 후 삭제 

   - 첫 번째 행(8개 행 범위 내)에 255자가 넘는 가상 데이터를 넣고, 가져오기 후 가상 데이터는 삭제 

 

컬럼1 : int	컬럼2 : varchar(4000)	컬럼3 : varchar(10)
가상데이터	가상데이터. 문자열 255넘게 적기	가상데이터
실제 데이터	실제 데이터	실제 데이터
실제 데이터	실제 데이터	실제 데이터