레코드 셋 결과 집합을 스크립트 작업에서 읽어오기

레코드_셋_결과_집합을_스크립트_작업에서_.dtsx

레코드 셋 결과 집합을 스크립트 작업에서 읽어오기

 

한대성

MS SQL Server MVP

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

 

 

SQL에서 임시 테이블이 자주 사용되는 것과 마찬가지로 SSIS에서는 결과 집합을 메모리 변수에 저장하는 방식이 자주 사용됩니다. 예를 들어, 메일 발송 대상을 읽어온 후 차례대로 발송하거나 결과 집합을 파일이나 메시지로 출력할 경우에도 이용할 수 있습니다.

 

지금까지 소개한 방식은

a.       SQL 실행 작업에서 데이터를 읽어온 다음, Object형 변수에 저장

b.       Foreach 루프 컨테이너에서 Foreach ADO 열거자를 선택한 다음, Object형 변수를 선택

c.       변수 매핑 탭에서 각 열에 해당하는 변수를 매핑

d.       Foreach 루프 컨테이너 내에 스크립트 작업 또는 SQL 실행 작업을 추가한 다음, 사용할 변수를 지정하고 이 변수 값들을 이용하여 처리하는 스크립트 작성

또는 데이터 흐름 작업에서 스크립트 원본으로 데이터를 읽어와서 처리하는 방식 입니다.

 

이러한 방식에는 다음과 같은 어려움이 있습니다.

1.       출력되는 열의 수가 고정 – 어떤 열이 출력될 것인지가 미리 정해져야 합니다. 만약, 데이터베이스 내의 모든 테이블 또는 임의의 여러 테이블들을 파일로 출력하고자 할 경우에는 불가능합니다.

2.       작업 설정의 번거로움 – 각 열에 대한 정의 작업이 필요합니다.

 

이러한 점은 데이터를 Object형 변수에 입력하는 부분 보다는 출력하는 부분의 특성 때문입니다.

 

 

이러한 방식에 대한 새로운 처리 방법을 소개합니다.

 

다음 예제는 AdventureWorks DB에 있는 테이블 중 임의의 한 개의 테이블을 읽어온 다음, 이 테이블의 데이터를 파일로 출력하는 예제입니다.

 

1.       SQL 실행 작업을 하나 추가한 후, 다음과 같이 AdventureWorks에서 임의의 테이블 명을 읽어오는 쿼리를 지정하고, 결과 값을 TableName이라는 문자형 변수에 저장하도록 설정합니다.

 (이와 관련된 자세한 설정 방법은 이전 예제를 참고하시기 바랍니다.)

--임의의 테이블 이름 읽어오기 WITH TableListName(RowNum, TableName) AS (         SELECT                ROW_NUMBER() OVER (ORDER BY B.Name) as RowNum,                a.name + '.' + b.name  AS TableName         FROM sys.schemas A join sys.tables B                 on a.schema_id = b.schema_id         WHERE B.type = 'U' )       SELECT TOP 1 * FROM TableListName WHERE RowNum = CAST(RAND()*(SELECT MAX(RowNum)-1 as MaxRowNum                                 FROM TableListName) AS INT)+1

 

 

2.       TableName 변수를 이용해서 데이터를 읽어오는 작업을 설정합니다. SQL 실행 작업에서 식 탭에서 SqlStatementSource에 대해 다음과 같은 식을 설정합니다.

“SELECT * FROM “ + @[사용자::TableName]

 

 

 

ResultSet을 전체 결과 집합으로 설정하고, 결과 집합 탭에서 결과를 지정할 변수를 지정합니다. 본 예제에서는 Email_CurrentDataset 이라는 Object형 변수를 사용합니다.

 

3.       스크립트 작업을 추가한 후, ReadOnlyVariables 속성에 위에서 설정한 Email_CurrentDataSet 변수와 TableName 변수를 지정하고 VSA를 엽니다.

 

4.       VSA의 상단 메뉴에서 프로젝트(P) -> 참조 추가(R)를 선택한 후, System.Xml.dll 을 추가합니다.

 

5.       다음과 같은 스크립트를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO   Public Class ScriptMain       Public Sub Main()         Dim str As String         Dim colidx As Integer         Dim tableName As String         Dim oLead As New Data.OleDb.OleDbDataAdapter         Dim dt As New Data.DataTable         Dim dr As System.Data.DataRow         Dim WriteToFile As StreamWriter           tableName = Dts.Variables("TableName").Value.ToString         WriteToFile = File.CreateText("D:\" & tableName & ".txt")           Try             oLead.Fill(dt, Dts.Variables("Email_CurrentDataset").Value)             For Each dr In dt.Rows                 str = ""                 For colidx = 0 To dt.Columns.Count - 1                     If colidx < dt.Columns.Count - 1 Then                         str &= dr.Item(colidx).ToString & vbTab                     Else                         str &= dr.Item(colidx).ToString & vbCrLf                     End If                 Next                 WriteToFile.Write(str)             Next               WriteToFile.Close()             Dts.TaskResult = Dts.Results.Success           Catch ex As Exception             Dts.TaskResult = Dts.Results.Failure         End Try     End Sub End Class

AdventureWorks에서 임의의 테이블을 선택한 다음 D:\ 에 Tab 구분자로 설정된 데이터 파일로 출력합니다.

 

6.       패키지를 여러 번 실행시킨 후, 실행된 결과를 확인합니다.

 

 

 

Ex) HumanResources.Shift.txt

 

Ex) Production.TransactionHistory.Achive.txt

 

 

 

본 방식은 필자가 개인적으로 오랫동안 구현하고자 했던 내용입니다.

비록 스크립트 작업을 이용한 처리 방식이지만 『SSIS에서 동적 결과 처리를 할 수 있다』라는 점에서 매우 유용한 예제라 생각됩니다. 이러한 방식을 다음과 같은 형태의 작업에도 이용할 수 있습니다.

 

◦         테이블 결과를 메일의 내용으로 출력해야 할 경우 – 파일에 쓰는 대신 문자열 변수에 저장하도록 스크립트를 변경한 다음, 메일 보내기 작업에서 내용 부분에 이 변수를 지정합니다.   ◦         여러 테이블을 파일로 출력 - 파일 이름을 랜덤으로 획득하는 부분 대신, Foreach 루프 컨테이너를 사용해서 여러 테이블 또는 전체 테이블에 대해 파일 백업이나 엑셀 파일로 출력하는 기능을 구현할 수 있습니다.