Skip to content

조회 수 18360 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄 첨부
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄 첨부

SSIS에서 데이터 흐름 작업을 사용할 때 여러 제약 사항들 때문에 사용하기가 까다로운 경우가 있었을 것입니다. 다음과 같은 형태의 저장 프로시저를 예로 들어보겠습니다.

CREATE PROC TestProc

AS

        CREATE TABLE #RESULT

        (

               SEQ INT,

               COL1 INT,

               COL2 INT

        )

 

        INSERT #RESULT VALUES(1,2,3)

        INSERT #RESULT VALUES(4,5,6)

 

        SELECT * FROM #RESULT

GO

 

EXEC dbo.TestProc

/*

SEQ         COL1        COL2

----------- ----------- -----------

1           2           3

4           5           6

*/


지극 히(?) 정상적인 프로시저처럼 보입니다. 이 러한 형태로 프로시저를 만들어서 SSIS의 데이터 원본에서 막 쓸려고 하면,

1.jpg

이와 같은 에러 메시지가 나타나면서 열 정보를 설정할 수가 없습니다. 어떻게든 열 정보가 나와야 매핑을 설정할 수 있을 것인데 열 정보가 나타나지 않으니 더 이상 쓸 수가 없습니다.

 

1. SET FMTONLY OFF 구문을 추가

우선, 다음과 같이 저장 프로시저에 SET FMTONLY OFF 구문을 추가하고 실행해 봅시다.

ALTER PROC TestProc

AS

 

SET FMTONLY OFF

 

        CREATE TABLE #RESULT

        (

               SEQ INT,

               COL1 INT,

               COL2 INT

        )

 

        INSERT #RESULT VALUES(1,2,3)

        INSERT #RESULT VALUES(4,5,6)

 

        SELECT * FROM #RESULT

GO

2.jpg

열 정보가 잘 나타납니다. ~!

이렇게 처리되는 이유를 간략히 설명하자면 다음과 같습니다.

A.     SSIS에서 열 정보를 읽어올 때에는 자동으로 SET FMTONLY ON 옵션을 켠 상태에서 저장 프로시저의 열 정보를 읽어오게 됩니다. SET FMTONLY ON 옵션은 클라이언트의 메타 정보만 반환합니다. 쿼리를 실제로 실행하지 않고 응답 형식을 테스트하는 데 사용할 수 있습니다.”로 도움말에 설명되어 있습니다.

B.     문제는 SET FMTONLY ON 옵션으로는 임시 테이블이 생성되지 않는다는 점입니다. SET FMTONLY ON 옵션을 켠 상태에서 프로시저를 실행하니깐 임시 테이블이 안 만들어지고, 임시테이블이 없으니깐 열 정보를 몰라서 열 매핑이 실패하는 것입니다.

C.     프로시저 내에 강제로 SET FMTONLY OFF 명령을 넣어버렸습니다. 이렇게 하면 SSIS에서 열 정보를 읽어갈 때 SET FMTONLY ON 명령을 켜서 들어왔더라도 OFF 시켜서 임시 테이블을 만들고 이에 대한 정보를 읽어갈 수 있게 됩니다.

복잡하면 통과.. 하지만, 여기서 끝은 아닙니다. 이제 패키지를 대충 구성해서 실행시켜 봅니다.

3.jpg

4.jpg

[OLE DB Source [1]]
오류: A rowset based on the SQL command was not returned by the OLE DB provider.

이해하기 쉽지 않은 에러 메시지가 출력됩니다. 미리보기 해도 잘 나오고 열 정보도 잘 나오는데 뭐가 문제란 말인가.

 

2. SET NOCOUNT ON 구문을 추가

이번에도, 다음과 같이 저장 프로시저에 SET NOCOUNT ON 구문을 추가하고 실행해 봅시다.

ALTER PROC TestProc

AS

 

SET FMTONLY OFF

SET NOCOUNT ON

 

        CREATE TABLE #RESULT

        (                SEQ INT,

               COL1 INT,

               COL2 INT

        )

 

        INSERT #RESULT VALUES(1,2,3)

        INSERT #RESULT VALUES(4,5,6)

 

        SELECT * FROM #RESULT

GO

 5.jpg


~~수 행됩니다.

SET NOCOUNT ON은 대충 어떤 기능을 하는지 아실 것입니다.

A.     SET NOCOUNT ON 옵션을 넣지 않은 상태에서 저장 프로시저를 실행 시키면 다음과 같이 처리 단계에서 실행된 결과를 반환합니다.
6.jpg

B.     SET NOCOUNT ON 옵션을 넣으면 다음과 같이 결과만 출력됩니다.

7.jpg

C.     SSIS의 데이터 흐름에서는 몇 개 행이 처리되었습니다.”라는 메시지도 결과값으로 인식해서 처리하게 됩니다. 이미 열 정보를 이용해서 세 개의 열이 출력될 것이다라고 설정해 놨는데 이상한(?) 문자열(1 rows(s) affected)이 먼저 나타나니깐 에러를 발생시킨 것입니다.

D.    SET NOCOUNT ON 옵션으로 저런 정보 메시지를 나타내지 않도록 한 것입니다. 자연히 미리 정한 열 정보 모양대로 출력되기 때문에 정상적으로 패키지가 실행됩니다.

 

 

SSIS Beta 버전부터 해왔지만 조금 전까지도 "SSIS에서는 이건 안 되는 기능이야.”라 고 단정하고 있었던 내용입니다. 조금 더 깊이 살펴봤다면 좀 더 일찍 알 수도 있었을 수도 있었는데. 프로젝트 할 때 이것이 안되서 다른 방식으로 고생 고생해서 처리했던 생각이 나네요~~^^


List of Articles
번호 제목 글쓴이 날짜 조회 수
39 [MS-SQL] MS-SQL 2000 이하에서 지원되던 오래된 쿼리 2008 이... ADMINPLAY 2014.09.28 4086
38 MicroSoft SQL Server 2008 1433 port 원격(외부)접속 허용하기 ADMINPLAY 2014.09.20 4138
37 SET LOCK_TIMEOUT ADMINPLAY 2012.01.16 13829
36 SQL Server 2008 R2 버전에서 지원하는 기능 ADMINPLAY 2011.03.30 15948
35 Fatal error: Call to undefined function: mssql_connect() ADMINPLAY 2010.06.03 22271
34 DBCC SHRINKFILE 트랜잭션 로그파일 축소 l2zeo 2010.05.29 21360
33 마소제공 mssql 관리툴 ADMINPLAY 2010.04.23 19547
32 SQL Server 데이터 파일 축소 l2zeo 2010.03.28 20419
31 [FAQ]join을 하다가 막혔어요.. l2zeo 2010.02.25 16415
30 [FAQ]mssql2005 디버깅 어떻게 해야 하나요? file l2zeo 2010.02.25 20687
29 [FAQ]하나의 서버에 두개의 database를 설치하려고 합니다. l2zeo 2010.02.25 19287
» 데이터 원본에 저장 프로시저 사용하기 file l2zeo 2010.02.25 18360
27 MSSQL 버전별 최대 용량 비교표 l2zeo 2010.02.24 18425
26 SQL Server 2008의 새로운 기능 - Parameter Embedding Optimi... l2zeo 2010.02.24 17008
25 SQL서버 성능카운터 활용을 위한 팁 file l2zeo 2010.02.24 19802
24 SQL Server 2005 백업 설정 방법 file l2zeo 2010.02.24 17672
23 SQL Agent 작업 실행 상태 확인하기 l2zeo 2010.02.23 17782
22 DB 서버에 특정 IP 접근 차단 방법 file l2zeo 2010.02.21 20949
21 [MSSQL 2000] 성능 모니터링 file l2zeo 2010.02.21 14741
20 [SQL Server] Build Number of Each Version of SQL Server - ... ADMINPLAY 2009.12.20 16623
Board Pagination Prev 1 2 Next
/ 2

Copyright ADMINPLAY corp. All rights reserved.

abcXYZ, 세종대왕,1234

abcXYZ, 세종대왕,1234