데이터 흐름 원본에서 임시 테이블 사용하기

데이터 흐름 원본에서 임시 테이블 사용하기

 

Microsoft Premier Field Engineer

한대성

 

 

좀 까다로운 주제입니다.

예를 들어, 다음과 같은 쿼리를 생각해 봅시다.

CREATE TABLE #SeqNo (         SeqNo int )   INSERT #SeqNo VALUES(1) INSERT #SeqNo VALUES(2)   SELECT * FROM #SeqNo DROP TABLE #SeqNo   /* SeqNo ------ 1 2 */

 

쿼리 분석기에서 실행시키면 위와 같이 정상적으로 실행됩니다. 그런데 이러한 형태의 쿼리를 데이터 흐름 작업에서 OLE DB 원본 또는 DataReader Source 등에서 사용하려고 하면 다음과 같은 문제가 발생합니다.

 

이와 같은 형태의 에러가 발생하는 이유는 다음과 같습니다.

SSIS에서 데이터 흐름 작업 내의 모든 개체는 출력될 열의 정보가 패키지를 만들 때부터(=디자인 단계에서) 정해져야 합니다. 쉽게 말해 출력되는 열의 정보가 명확히 존재해야 한다는 것이지요.

 

그럼 위와 같은 쿼리는 뭐가 문제일까요?

SSIS 패키지에서 SQL 쿼리가 원본으로 설정된 경우에는 쿼리를 실행시켜서 나오는 결과를 이용하여 열 정보를 얻어오는 것이 아니라, 다음과 같이 sp_prepare 라는 시스템 프로시저로 열 정보만 읽어오게 됩니다.

declare @p1 int set @p1=NULL exec sp_prepare @p1 output,NULL,N'CREATE TABLE #SeqNo (         SeqNo int )   INSERT #SeqNo VALUES(1) INSERT #SeqNo VALUES(2)   SELECT * FROM #SeqNo DROP TABLE #SeqNo',1 select @p1

 

그런데, sp_prepare문은 쿼리나 테이블일 경우에는 아래와 같이 열 정보를 읽어올 수 있지만, 임시 테이블 또는 Sub 쿼리 형태의 경우에는 열 정보를 읽어오지 못합니다.

 

열 정보를 읽어올 수 없기 때문에 데이터 흐름 작업에서 출력할 열을 설정할 수 없게 되고 이로 인해 데이터 흐름 작업 내에서 임시 테이블을 못 쓰는 형태가 되어버립니다.

 

그럼 어떻게 해야 하나?

 

1.     임시 테이블 대신 일반 테이블 사용하기  (#SeqNo 테이블 대신 tmpSeqNo 테이블로 사용)

임시 테이블을 사용하는 이유는 여러 가지가 있습니다.

첫 번째 굳이 삭제를 해 주지 않아도 세션이 종료되면 삭제가 되며, 동시에 수행하더라도 중복이 발생하지 않게 됩니다. 하지만, 이러한 장점을 포기할 수 있는 상황이라면 일반 테이블 형태로 구성합니다. 단순히 임시 테이블을 일반 테이블로만 바꾸는 것이 아니라,

테이블 생성 및 데이터 적재는 SQL 실행 작업에서 먼저 수행하고,

데이터 흐름에서는 이 테이블에서 데이터를 읽도록 구성하며,

또 다른 SQL 실행 작업에서 삭제 작업을 수행하는 것이지요.

단, 이 때 주의할 점은 데이터 흐름 작업 구성 전에 DB에 테이블을 만들어 놓은 상태에서 설정해야 한다는 것이며, 데이터 흐름 작업의 속성 중 DelayValidation 속성을 True로 변경해야 합니다. 이에 대해서는 다음 글을 참고하세요.

 

 

2.     난 그래도 꼭 임시 테이블을 사용하고 싶다고~! (XML 소스를 이용하여 테이블 이름 변경)

동시에 패키지가 수행되기 때문에 반드시 하나의 패키지가 수행될 때 다른 패키지가 영향을 받지 않도록 설정해야 한다면 임시 테이블을 사용할 수 밖에 없겠지요. 물론 tmpSeqNo__121212, tmpSeqNo_212313 와 같이 테이블 명 뒤에 난수를 발생시켜 중복이 안 생기도록 할 수는 있습니다만, 귀찮죠.

다음과 같이 임시 테이블을 사용하도록 할 수 있습니다. 1번 방법과 같이 우선 일반 테이블을 사용하도록 패키지를 구성하세요. 그런 다음, 연결 관리자에서 패키지에서 쓰는 OLE DB 연결을 선택한 후, 속성에서 RetainSameConnection 속성을 True로 변경합니다.

그러고선, 솔루션 탐색기에서 패키지 파일을 선택한 후, 코드 보기를 해서 XML 파일로 패키지를 엽니다.

찾아 바꾸기를 이용해서 tmpSeqNo 를 #tmpSeqNo로 변경합니다.

 

패키지를 저장합니다.

패키지 내의 작업 개체들을 열어보면 임시 테이블로 쿼리가 변경된 것을 확인하실 수 있으며, 실행 시켜도 정상적으로 실행됩니다.

물론, 이 경우에는 마지막 단계인 테이블 삭제는 없어도 되겠지요.

 

 

3.     조금 더 파 보자. 갈 때까지 가보자. (스크립트 변환 – 원본을 이용하여 구현)

또 다른 방법이 있습니다.

데이터 흐름 작업 내에 스크립트 변환 – 원본을 추가한 후, 다음과 같이 Input and Output 탭에서 출력될 열 이름과 유형을 설정하여 추가합니다. 본 예에서는 SeqNo라는 열을 DT_I4로 설정하였습니다.

이 때 한글 버전일 경우에는 Output 0 대신 출력 0 으로 나타날 것입니다.

세 번째 탭인 Connection Manager에서는 다음과 같이 설정합니다.

 

이제 두 번째 Script 탭으로 이동한 후, 스크립트 편집기를 열고선 다음과 같이 스크립트를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper   Public Class ScriptMain     Inherits UserComponent           Public Overrides Sub CreateNewOutputRows() Dim s As String         s = "CREATE TABLE #SeqNo" & vbCrLf & _             "(" & vbCrLf & _             "        SeqNo Int" & vbCrLf & _             ")" & vbCrLf & _             " INSERT #SeqNo VALUES(1) " & vbCrLf & _             " INSERT #SeqNo VALUES(2) " & vbCrLf & _             "SELECT * FROM #SeqNo" & vbCrLf & _             "DROP TABLE #SeqNo"           Dim conn As New OleDb.OleDbConnection         Dim cmd As New OleDb.OleDbCommand         Dim dr As OleDb.OleDbDataReader             conn.ConnectionString = Me.Connections.Connection.ConnectionString         conn.Open()         cmd.Connection = conn         cmd.CommandText = s         dr = cmd.ExecuteReader           While dr.Read()             Output0Buffer.AddRow()             Output0Buffer.SeqNo = dr.GetInt32(0)         End While           dr.Close()         conn.Close()         End Sub   End Class

 

노란 색 부분이 달라질 수 있는 부분입니다. 특히 마지막 노란색 부분 Output0Buffer 부분은 위에서 말하 것과 같이 한글 버전인 경우, 출력0Buffer (공백 없음)로 설정하셔야 합니다.

 

패키지를 실행시켜 봅시다.

 

 

더 좋은 방법이 있을까요? 고민해보고 있으면 더 추가하겠습니다.