데이터 가져오기에서의 텍스트 파일 Null 처리 문제

데이터 가져오기/내보내기에서의 텍스트 파일 Null 처리 문제

 

 

한대성

MS SQL Server MVP

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

 

 

[질문]

SQL 2005 에서 DTS 수행시 엑셀 파일이면 상관이 없는데 텍스트나 CSV파일이면 날짜 형식(smalldatetime)에 null 값이 있으면 가져오기가 되지 않습니다.   이 부분에 대해 해결할 방법이 없을까요?

 

 

다음과 같은 데이터를 SQL 2005의 데이터 가져오기/내보내기를 이용해서 가져올 때 발생할 수 있는 문제입니다.

 

[데이터 원본]

1.     엑셀 파일

 

2.     텍스트 파일 or CSV 파일

 

 

[데이터 대상]

CREATE TABLE DateTarget (        InputDate smalldatetime NULL ) GO

 

 

즉, Null이 포함된 입력 데이터를 smalldatetime 형태의 Null이 허용되는 테이블로 넣는 작업을 수행할 때, Excel 파일은 문제없이 수행되나 텍스트 파일이나 CSV 파일일 경우에는 에러가 발생합니다. 

 

 

 

 

 

이런 에러가 발생하는 원인은 다음과 같은 점 때문입니다.

 

1.     Excel 파일을 읽어오는 Jet Engine Data Provider는 빈 값을 Null로 처리해서 가져옵니다. 따라서 입력 데이터 중에 Null이 포함되더라도 대상 테이블이 Null 허용이면 에러 없이 처리됩니다.

 

2.     텍스트 파일(또는 CSV 파일)은 기본값으로 빈 값을 Null로 간주하지 않습니다. 빈 값을 대상 테이블의 유형에 맞게 자동으로 변경합니다. 이 때 변환하는 값은 도구에 따라 차이가 있습니다.

예를 들어 다음과 같이 쿼리 분석기에서 수행할 경우, 빈 값은 ‘1900-01-01 00:00:00’의 값으로 변환합니다.

CREATE TABLE DateTarget (         InputDate smalldatetime NULL ) GO   INSERT DateTarget VALUES ('') GO   SELECT * FROM DateTarget /* InputDate ----------------------- 1900-01-01 00:00:00 */

 

SQL 2000 DTS에서는 기본값으로 빈 값을 Null로 간주합니다. 따라서, 텍스트 파일이 빈 값일 경우에는 Null로 처리됩니다.

 

하지만, 데이터 가져오기/내보내기를 수행하는 SSIS 엔진에서는 다른 값으로 처리합니다.

 

즉, SSIS에서는 빈 값을 ‘1899-12-30 오전 12:00:00’으로 변환하게 됩니다.

 

 

 

datetime과 smalldatetime의 데이터 범위를 잠시 살펴보겠습니다. 

 

 

에러가 발생하는 원인은 바로 여기에 있습니다. SSIS에서 빈 값을 ‘1899-12-30’으로 변형한 후에 smalldatetime 열에 입력을 하려고 하지만, smalldatetime의 데이터 범위 밖의 숫자이기 때문에 위와 같은 에러가 발생합니다.

 

 

이러한 문제를 해결하기 위해서는 다음과 같은 방법을 사용하시기 바랍니다.

 

방법 1. 텍스트 파일 또는 CSV 파일이면서 Null이 포함된 데이터인 경우, 쿼리 분석기에서 BULK INSERT 명령을 이용하시기 바랍니다.

BULK INSERT DateTarget FROM 'D:\Date.txt' WITH (FIRSTROW=2) –열 머리글이 포함된 경우 GO

 

 

방법 2. 대상 테이블의 열을 smalldatetime 대신 datetime으로 변경하고 작업 처리 후에 ‘1899-12-30’으로 입력된 값을 Null로 변경하는 작업을 수행합니다.

 

 

방법 3. 데이터 가져오기 작업을 ‘즉시 실행’하는 대신 SSIS 패키지로 저장한 다음, 이 패키지를 편집 모드로 열어서 다음과 같은 수정 작업을 수행합니다. 편집 모드로 열려면 패키지 파일을 선택한 다음, 마우스 오른쪽 버튼을 클릭해서 나타나는 메뉴 중, 편집을 선택하면 됩니다. 이 경우, 프로젝트 없이 패키지만 여는 것이기 때문에 패키지를 수정할 수는 있지만, 디버깅이나 실행할 수는 없습니다.

 

 

 

 

플랫 파일 원본 편집기 내에 있는 원본의 Null 값을 데이터 흐름의 Null 값으로 유지(R) 옵션을 체크하고 저장한 다음, 패키지를 실행합니다. 패키지 파일을 더블 클릭하면 자동으로 패키지 실행 유틸리티인 DTEXECUI가 실행됩니다.

 

 

 

 

데이터 가져오기/내보내기 기능에서 위와 같이 Null 값을 유지하는 옵션이 있다면 이와 같은 번거로운 작업을 수행하지 않아도 되지만, 유감스럽게도 아직까지는(SP2) 이 옵션이 포함되어 있지 않습니다.

 

 

참고하시기 바랍니다.