SSIS를 이용한 서버 성능 수집 프로세스 구현 - 엑셀 내보내기

Template.xls

4. 집계 데이터를 엑셀 파일로 내보내기

백도훈

에이디컨설팅 선임 컨설턴트

 

지난 시간까지 데이터 수집 패키지를 만들었습니다. 이번 시간에는 하루에 한 번 실행되면서 집계를 하고 이 집계 정보를 엑셀 파일로 내보내고 메일로 보내는 패키지를 만들어보겠습니다.

수집된 성능 카운터 정보를 사용할 방법은 다양하게 있을 것입니다. 여기서는 간단하게 시간 별 성능 카운터의 최대 수치와 평균 수치를 집계로 내보겠습니다.

 

패키지를 만들기 전에 한 번 생각해볼 것이 있습니다. 실무에서 이 패키지를 사용한다고 가정했을 때 생성되는 엑셀 파일의 양식이 전혀 없다면 어떨까요? 소박하게나마 조금 양식이 있는 엑셀 파일로 출력하고 싶습니다. 예를 들어서 이런 엑셀 형식보다

이런 엑셀 파일을 만들고 싶다는 것이죠.

 

두 엑셀 파일의 차이를 보시고 별 감흥이 없으시다면 작전 실패입니다만 (^^;;;)

혹시 별 감흥이 없는 분들도 지금부터는 두 엑셀 파일의 차이를 보고 아래의 형식으로 엑셀 파일을 출력하고 싶다고 열망하기로 하겠습니다. -_ㅡa

 

자, 모든 분께서 서식있는 엑셀 파일로 출력하기를 간절히 바라고 계시는 것 같습니다;;; 어떻게 구현하면 될까요?

 

먼저, 스크립트 작업에서 엑셀 객체를 사용하여 만드는 방법이 있습니다. 구글과 MSDN을 넘나들며 검색과 Copy & Paste, 그리고 심장을 관통하는 디버그 미학을 체험하는 길이라고 감히 표현하겠습니다;;; 생성될 엑셀 파일의 디자인을 직접 보면서 하는 작업이 아닌데다가 어플리케이션 개발 툴이 아닌 SSIS 패키지 제작 툴이기 때문에 엑셀의 배경색을 한 번만 바꾸려해도 VSA 편집과 패키지 실행, 엑셀 파일 사이를 들락날락 하며 확인해야하는 번거로운 작업이 될 것이라는게 마구 느껴집니다.

 

그렇다면 어떤 다른 방법이 있을까요? 벌써 감을 잡으신 분들도 계신 것 같은 분위기 입니다. 뜸을 이렇게 들여놓고 막상 설명하면 맥이 다 빠지실 것 같은데;;;

 

다른 방법은, 미리 서식을 지정한 엑셀 파일을 템플릿으로 사용하는 것 입니다. 이 템플릿 파일을 어딘가에 두고 패키지에서는 이 템플릿 파일을 복사해서 데이터만 넣어주는 시나리오입니다. 좀 허무한가요? ^^;;; 이 쪽이 정신 건강에 좋은 생명 연장의 길인 것 같습니다.

 

템플릿으로 사용할 엑셀 파일을 만드는 것까지 다 설명하기에는 이야기가 너무 길어질 것 같습니다. 각자 미적 감각도 다르고 하니 여기서는 템플릿으로 사용될 엑셀 파일을 첨부하기만 하겠습니다. 게시물 상단에 링크되어 있는 엑셀 첨부 파일을 다운 로드해주세요~

 

템플릿 파일은 어디에 둘까요? 음… 이전 시간에 로그 파일을 옮겨오는 로컬 컴퓨터의 폴더가 있었네요. 그 폴더에 Excel이라는 하위 폴더를 만들어서 템플릿 파일도 놓고 패키지를 통해 생성될 엑셀 파일도 모아두기로 할까요? 저의 경우에는 D:\LogFiles 폴더 및에 Excel이라는 이름의 폴더를 생성해서 Template.xls 파일을 복사해두었습니다.

 

위에서 설명한 것을 토대로 대략적인 구성을 해볼까요?

날짜를 이용해서 엑셀 파일 명을 생성하고 à 템플릿 파일을 앞에서 생성한 파일 명으로 복사하고 à 쿼리를 통해서 집계 데이터를 입력하고 à 메일을 보낸다.

 

패키지 준비 사항에 대한 설명이 무지 길었네요. 이제 패키지 작성을 해보겠습니다.
여러 번 말씀 드리지만 지난 시간까지는 데이터를 입력하는 패키지였고 이번 시간에 만들 패키지는 데이터를 출력하는 패키지 입니다.

새로운 패키지를 만듭니다. 저의 작명 센스는 삼국 시대까지 거슬러 올라가는 수준이라 패키지 이름을 DBtoExcel이라고 지었습니다;;;

 

1.      생성할 엑셀 파일 명을 저장할 변수를 추가하겠습니다. 패키지 범위의 String 형인 xlsName이라는 변수를 추가하고 기본 값으로 Template.xls 파일의 경로를 입력합니다. 저의 경우에는 D:\LogFiles\Excel\Template.xls 입니다.
엑셀 파일이 저장될 경로를 저장할 변수도 추가하겠습니다. 패캐지 범위의 String 형인 TargetDir라는 변수를 추가하고 기본 값으로 엑셀을 저장할 폴더를 입력합니다. 저의 경우에는 D:\LogFiles\Excel\ 입니다.

 

2.      스크립트 작업을 추가하고 엑셀 파일 명 생성이라고 이름을 변경하겠습니다.
편집기를 열고 스크립트 페이지의 ReadOnlyVariables 속성에 TargetDir이라고 입력합니다.
또, ReadWriteVariables 속성에 xlsName이라고 입력합니다.

스크립트 디자인 버튼을 클릭해서 VSA(Visual Studio for Applications)를 열고 다음 소스를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime   Public Class ScriptMain     Public Sub Main()         Dts.Variables("xlsName").Value = Dts.Variables("TargetDir").Value.ToString + "PerfData_" + DateTime.Today.AddDays(-1).ToString.Substring(0, 10).Replace("-", "") + ".xls"         Dts.TaskResult = Dts.Results.Success     End Sub   End Class

어제 날짜의 성능 카운터를 집계하는 것이기 때문에 파일 명도 어제 날짜를 기준으로 생성했습니다.
관점에 따라서 오늘 날짜를 기준으로 파일 명을 생성할 수도 있을 것입니다.

 

3.      이제 템플릿 파일을 앞에서 생성한 엑셀 파일 명으로 복사하겠습니다.
복사하기 위해서 연결 관리자에 두 개의 연결을 추가해야 합니다.

연결 관리자에 새 파일 연결을 추가합니다. 사용 유형은 기존 파일로 지정하고 template.xls 파일을 선택합니다.
파일 연결 이름을 TemplateFile이라고 변경하겠습니다.
진짜 설정은 식에 변수를 대입해서 할 것입니다. 지금은 두 개의 파일 연결 틀이 필요한 것이므로 약간 편법을 사용해서 TemplateFile을 복사해서 붙여 넣겠습니다. TemplateFile 1 이라고 복사 됐을텐데 이름을 TargetFile이라고 변경하겠습니다.

TemplateFile의 속성 창을 엽니다. Expressions 속성 중에서 ConnectionString을 추가하겠습니다.
값으로 @[사용자::TargetDir] + "Template.xls" 이라고 입력합니다.

TargetFile의 Expressions 속성에도 ConnectionString을 추가합니다.
값으로 @[사용자::xlsName] 라고 입력합니다.

4.      파일 시스템 작업을 추가하고 엑셀 파일 명 생성과 연결합니다. 이름은 템플릿 복사라고 하겠습니다.
SourceConnection 속성으로 TemplateFile 연결을 지정하고
DestinationConnection 속성으로 TargetFile 연결을 지정합니다.
OverwriteDestination 속성은 True로 지정해서 파일을 복사할 때 같은 파일이 있다면 덮어쓰도록 설정하겠습니다.

패키지 작성이 잘 되고 있는지 확인도 해보고, 이후의 작업에서 지정할 엑셀 파일 대상으로 쓸 겸해서 실행해보겠습니다.



패키지가 정상적으로 실행되었고 대상 폴더에 어제 날짜를 기준으로 한 PerfData_20080731.xls 이라는 엑셀 파일이 복사되었습니다.

 

5.      데이터 흐름 작업을 추가합니다. 이름을 집계 데이터 내보내기라고 변경하겠습니다.
데이터 흐름 작업을 더블 클릭해서 데이터 흐름 디자인 모드로 전환합니다.
이 데이터 흐름 작업에서 흐름을 디자인해서 집계 데이터를 엑셀 파일에 입력하도록 하겠습니다.

 

6.      데이터를 이동하려고만 하면 계속 나오는 대목이라 지겨우실지 모르겠습니다. T^T
데이터를 원본에서 대상으로 이동하기 위해서는 연결 관리자에서 연결을 생성해줘서 각각의 통로를 만들어줘야합니다.
혼란 방지를 위해 미리 연결을 추가하고 가겠습니다.

이번에는 ADO.NET 연결을 이용해보겠습니다.
연결 관리자에서 새 ADO.NET 연결을 추가합니다. 목록에 성능 카운터 데이터가 있는 데이터베이스가 있다면 선택하시고 없으시다면 새로 만들기 버튼을 클릭해서 데이터베이스 접속 정보를 입력합니다.



그리고 연결의 추가를 완료했으면 연결의 이름을 SourceDB라고 변경하겠습니다.

이제 엑셀 파일에 대한 연결을 추가해야겠지요?
연결 관리자에서 새 연결을 추가합니다. 그럼 다음과 같은 창이 열리는데요 여기서 나오는 목록은 컴퓨터에 설치된 프로그램과 드라이버에 따라서 조금씩 다를 것입니다. 유형이 없다거나 버전이 다르다거나 하겠지요?




이 목록에서 EXCEL을 선택하고 추가 버튼을 클릭합니다.
그러면 Excel 연결 관리자 창이 나옵니다.
파일 경로는 Template.xls 파일의 경로를 입력해주겠습니다. 실제로 템플릿 파일에 입력할 것은 아니지만 패키지를 작성하는 동안에는 틀이 필요하니 일단 그렇게 설정하겠습니다. 실제 파일은 식을 이용해서 설정할 것입니다.
첫 행은 열 이름으로 라는 체크 박스의 체크는 해제해주세요.

연결이 추가되었으면 TargetExcel이라고 변경하겠습니다.
TargetExcel 연결의 식을 추가하겠습니다.
Expressions 속성에서 ExcelFilePath 속성을 추가하고 값으로 @[사용자::xlsName]라고 입력하겠습니다.

 

7.      데이터 흐름 원본에 있는 DataReader 원본을 추가합니다.
편집기를 열어서 연결 관리자 탭의 연결 관리자로 SourceDB를 지정합니다.
구성 요소 속성 탭의 SqlCommand 속성으로 아래의 쿼리를 입력합니다.

declare @FromDate varchar(10), @ToDate varchar(10), @sql varchar(1000) select @FromDate = convert(varchar(10), dateadd(day, -1, getdate()), 121),              @ToDate = convert(varchar(10), getdate(), 121),              @sql = 'select B.AvgProcessorTime, B.MaxProcessorTime, B.AvgUserConnections, B.MaxUserConnections, B.AvgBatchRequests, B.MaxBatchRequests, B.AvgSqlCompilations, B.MaxSqlCompilations, B.AvgProcessorQueueLength, B.MaxProcessorQueueLength from (select num from number) AS A left outer join (select datepart(hour, PerfTime) AS PerfTime, Avg(ProcessorTime) AS AvgProcessorTime, Max(ProcessorTime) AS MaxProcessorTime, Avg(UserConnections) AS AvgUserConnections, Max(UserConnections) AS MaxUserConnections, Avg(BatchRequests) AS AvgBatchRequests, Max(BatchRequests) AS MaxBatchRequests, Avg(SqlCompilations) AS AvgSqlCompilations, Max(SqlCompilations) AS MaxSqlCompilations, Avg(ProcessorQueueLength) AS AvgProcessorQueueLength, Max(ProcessorQueueLength) AS MaxProcessorQueueLength from perfdata where perftime >= ''' + @FromDate + ''' and perftime < ''' + @ToDate + ''' group by datepart(hour, PerfTime)) AS B on A.num = B.PerfTime order by num'   exec (@sql)

쿼리 분석기에서 실행해보면 어떤 용도의 쿼리인지 한 번에 알 수 있습니다만 각 카운터 별로 어제 날짜의 시간대 별 최대 값과 평균 값을 조회하는 쿼리입니다.

자, 확인 버튼을 눌러서 나옵니다. 흐름의 이름을 집계 쿼리라고 변경해줄까요?

 

8.      데이터 흐름 대상에 있는 Excel 대상을 추가하고 집계 쿼리와 연결해줍니다.
편집기를 열면 연결 관리자에서 Excel 연결이 TargetExcel 하나밖에 없기 때문에 자동으로 매핑되어 있습니다.
Excel 시트의 이름을 PerfData로 지정해줍니다.

매핑 페이지에서 입력 열과 대상 열을 드래그 & 드롭으로 순서대로 매핑 시켜줍니다.

9.      엑셀 내보내기 까지의 패키지는 완성이 되었습니다.
패키지를 한 번 실행해보겠습니다.



엑셀 파일을 확인해보겠습니다.

항상 궁금한건 이 글을 보시는 분들도 정상적으로 결과가 출력됐을까? 입니다. ㅎ 정상적으로 실행이 됐나요? ^^ㅋ

다음 시간에는 메일 발송하는 작업에 대해서 이야기하고 이번 주제를 마치도록 하겠습니다.