데이터 원본에서 SQL 동적 쿼리 이용하기

데이터 원본에서 SQL 동적 쿼리 이용하기

 

한대성

MS SQL Server MVP

 

 

 

다음과 같은 동적 쿼리(Dynamic Query)를 데이터 흐름 작업의 원본으로 사용하고자 하는 경우를 살펴보겠습니다.

 

DECLARE @SQL VARCHAR(1000) SET @SQL = 'SELECT TOP 10 * INTO #AAA FROM Person.Address' SET @SQL = @SQL + ' SELECT * FROM #AAA' EXEC(@SQL)

 

 

미리 보기(Preview)를 하면 아래와 같이 결과가 잘 나옵니다.

 

하지만, 열 매핑(Columns) 탭에 들어가면 아무것도 나타나지 않습니다.

 

열 매핑이 이루어지지 않으면, 패키지를 구성할 수 없기 때문에 사실상 사용 불가능한 것이지요.

 

 

해결 방안을 찾기 전에 우선 왜 이런 현상이 나타나는 것일까요.

SSIS 는 결정적(Determinate) 구조를 가져야 합니다. (뭔 소리냐~)

패키지 개발 단계에서 전송되는 열의 정보나 크기, 형태 등 모든 구조가 만들어져야 하며, 패키지 실행 중에는 이를 변경할 수 없습니다.

 

그것과 이것이 무슨 관계일까요?

SSIS 패키지는 위와 같이 동적 쿼리를 사용하는 데이터 원본에서 열 정보를 얻어와야 패키지를 구성할 수 있습니다. 그런데, 위와 같은 동적 쿼리는 쿼리 실행 전에 열 정보를 알 수 없는 형태입니다.

(글로 설명할려니깐 혼란만 초래하는 것 같네요. 잘 안와닿으시면 통과~)

 

 

그럼, 어떻게 하면 될까요?

꽁수를 사용해서 이를 구현할 수 있습니다. (이전에 이 방법 알아내느라 한참 애먹었습니다. ㅡ,.ㅡ)

 

 

1. OLE DB 대신 ADO.NET 연결을 사용해야 합니다. 쿼리를 실행시켜서 결과를 받는 경우라면 별 차이 없습니다. 기존 작업 개체들은 OLE DB 연결을 사용하고 있었다면 이 데이터 흐름 작업만 ADO.NET을 쓰도록 설정해도 되겠지요?

 

2. 데이터 흐름에서 OLE DB 원본 대신 DataReader 원본을 사용합니다.

 

3. DataReader 원본에서 위에서 추가한 연결을 설정하고,

 

 

4. 구성요소 속성(Component Properties) 에서 쿼리를 입력합니다.

이 때~!!! 위의 쿼리를 그대로 넣지 마시고, 동적 쿼리 결과 형태와 동일한 열의 테이블이나 쿼리를 만들어 넣습니다. 우선 정적인 테이블 또는 결과 셋을 이용해서 열 정보가 어떤 것이다라는 것을 SSIS 패키지에다가 알려주고 이를 이용해서 구성해야 하기 때문입니다.

 

본 예제에서는 동일한 열 구조를 가지는

 

                  SELECT TOP 10 * FROM Person.Addrsss

 

쿼리를 사용하였습니다.

 

열 매핑 탭에서 열 정보를 확인한 다음, 확인을 눌러 편집기를 닫고, 다음 변환들을 구성합니다.

 

 

5. 이제 이 단계가 중요합니다. DataReader 원본을 선택한 후, 속성 창에서 ValidateExternalMetadata 속성을 True에서 False로 변경합니다.

 

열 구조를 정의해 놨으니깐, 따로 검사하지 말아라~라는 속성입니다.

 

 

6. 이제 제어 흐름 영역으로 이동한 다음, 만들어진 데이터 흐름 작업을 선택하고 속성 창에 있는 Expressions의 … 버튼을 클릭해서 식(Expression) 작성기를 엽니다.

 

아래와 같이 [DataReader 원본].[SqlCommand] 속성을 선택하고 옆에 있는 … 버튼을 클릭해서 식 작성기를 엽니다.

 

 

 

 

7. 식 작성기에서 다음과 같이 동적 쿼리를 입력합니다.

 

이 때, 쿼리 앞, 뒤로 큰 따옴표(“)를 쳐야 하며 역 슬래쉬(\)인 경우, 두 개(\\)로 바꿔줘야 합니다.

 

 

 

다 되었습니다. 테스트 해 봅시다. 위에서는 TOP 10 으로 만들었는데, 동적 쿼리에서는 TOP 30의 데이터를 가져오게 해놨습니다.

 

 

동적 쿼리를 변경해서 TOP 100으로 해서도 한 번 테스트.

 

 

 

 

단순하진 않지만, 그렇다고 아~주 복잡한 편도 아닙니다. (속성 하나와 식 사용하는 것 밖에는..)

 

데이터 원본으로 동적 쿼리를 사용해야 한다면 이와 같은 방법으로도 구현할 수 있다는 것을 참고하시기 바랍니다.

2009.12.06
다음 글에 설명된 대로 하면 간단히 구현 가능합니다.
http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=3647