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. 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

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

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

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

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

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

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

[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

 

잘~~수 행됩니다.

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

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

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

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

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

 

 

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