SSIS를 이용한 서버 성능 수집 프로세스 구현 - 메일 발송

5. 메일 발송

백도훈

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

 

이번 시간에는 지난 시간에 내보냈던 엑셀 파일을 관리자에게 메일로 보내보도록 하겠습니다.

사실 메일 보내는 방법은 한대성님이 작년 3월에 쓰신 강좌 작업 종료 및 실패 시 메일 통보 설정에 있는 내용입니다.

어떻게 글을 쓰다보니 이 부분만 남겨놓고 앞에서 딱 잘렸네요.

 

- DB

앞에서 PerfMonDB 라는 데이터베이스를 만들어서 테스트하고 있습니다. Member라는 테이블도 생성했었는데 데이터 입력부분은 주석처리했었습니다. 데이터가 입력되었는지 확인해보고 입력되지 않았다면 입력하도록 하겠습니다.

insert Member values ('[이름]', '[메일 주소]') insert Member values ('[이름]', '[메일 주소]')

 

[이름], [메일 주소]에 적당한 이름과 메일 주소를 넣도록 하겠습니다. 한 건만 입력해도 되지만 가능하면 두 건 이상의 데이터를 입력해서 모든 주소로 메일이 발송되는지 체크하도록 하겠습니다.

 

-SSIS
지난 시간엔 엑셀 파일 내보내기 까지 작성했었죠? 이번 시간엔 집계 데이터 내보내기(데이터 흐름 작업) 뒤에 이어서 만들어보도록 하겠습니다.

 

1.       먼저 변수를 추가할까요?

이름	범위	데이터 형식	값
MailList	패키지	Object	System.Object
MessageFrom	패키지	String	[보내는 메일 주소]
MessageTo	패키지	String
SMTPLogin	패키지	String	[SMTP Login ID]
SMTPPasswd	패키지	String	[SMTP Login Password]
SMTPServer	패키지	String	[SMTP 서버 주소]

 

2.       메일을 발송하기에 앞서 발송할 메일 주소를 Select 해오도록 하겠습니다. SQL 실행 작업을 추가하고 집계 데이터 내보내기와 연결하도록 합니다. 이름은 메일 리스트 받아오기로 변경 하겠습니다.



연결 관리자를 살펴보니 SourceDB 연결이 하나 있네요. 기억하실지 모르겠지만 이 연결은 OLE DB 연결이 아니고 ADO.NET 연결이었습니다. 이 연결을 재활용하기 위해서 메일 리스트 받아오기의 ConnectionType 속성을 ADO.NET으로 변경하겠습니다. 이제 SourceDB 연결을 사용할 수 있겠군요. Connection 속성을 SourceDB로 지정해주겠습니다. SQLStatement 속성에 다음의 쿼리를 입력합니다.

select mail from Member

 

쿼리의 결과를 단계 1에서 선언한 Obejct형 변수 MailList에 담도록 하겠습니다.
먼저, ResultSet 속성을 전체 결과 집합으로 변경하고 결과 집합 탭을 선택합니다. 추가 버튼을 클릭하고 결과 이름에는 0을, 변수 이름에는 사용자::MailList를 지정합니다.

 

3.       메일 리스트를 받아왔으니 받아온 변수 안의 모든 메일 주소로 메일을 발송해야겠지요? Foreach 루프 컨테이너를 사용하면 되겠네요. ^^;; Foreach 루프 컨테이너를 추가하고 메일 리스트 받아오기 작업과 연결합니다.

Foreach 루프 컨테이너의 편집기를 열고 컬렉션 페이지를 선택합니다.
앞에서 발송 메일 주소를 변수 MailList에 할당했었습니다. 그 변수를 사용하기 위해 Enumerator 속성으로 Foreach ADO 열거자를 선택합니다. 그러면 하단 열거자 구성에서 원본 변수를 선택할 수 있습니다. 당연히 사용자::MailList를 선택합니다.

변수 매핑 페이지를 엽니다. Foreach 루프를 돌면서 MailList 변수 안의 메일 주소를 모두 순환하게 됩니다. 한 사이클 내에서 하나의 메일 주소를 매핑할 변수를 여기에서 지정합니다. (무슨 말씀인지 아시지요? ^^;;;)
변수는 사용자::MessageTo를 지정하고 인덱스는 0이라고 입력합니다.

 

4.       자, 이제 실제로 메일을 보내는 작업을 추가해보겠습니다.
메일 보내기 작업을 사용하는 것도 가능하지만 그러기 위해서는 SSIS가 실행되는 컴퓨터의 도메인 안에 SMTP 서버가 있어야합니다. 또, 그렇게 되면 HTML 태그를 넣어서 메일 보내는 것이 불가능합니다.
그렇기 때문에 오늘은 스크립트 작업을 이용해서 발송하도록 하겠습니다.

이 방법은 글 처음에도 썼지만, 한대성님이 작년 3월에 쓰신 강좌 작업 종료 및 실패 시 메일 통보 설정에 소개된 내용을 이용했습니다.

스크립트 작업 편집기의 스크립트 페이지에서 ReadOnlyVariables 속성에 다음과 같이 입력합니다.
MessageFrom,MessageTo,SMTPServer,SMTPLogin,SMTPPasswd,xlsName

스크립트 디자인 버튼을 클릭해서 VSA를 엽니다. 그리고 다음의 소스를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports System.Net Imports System.Net.Mail Imports Microsoft.SqlServer.Dts.Runtime   Public Class ScriptMain     Public Sub Main()         Dim htmlMessageTo As String = Dts.Variables("MessageTo").Value.ToString         Dim htmlMessageFrom As String = Dts.Variables("MessageFrom").Value.ToString         Dim htmlMessageSubject As String = "성능 카운터 보고 [" & Date.Today.ToString.Substring(0, 10) & "]"         Dim smtpServer As String = Dts.Variables("SMTPServer").Value.ToString         Dim smtpLogin As String = Dts.Variables("SMTPLogin").Value.ToString         Dim smtpPasswd As String = Dts.Variables("SMTPPasswd").Value.ToString         Dim htmlMessageBody As String           htmlMessageBody = "<H1>성능 카운터 보고</H1><BR>" & _             " <li> 발송 날짜: " & DateTime.Today.Now.ToString & _             " <li> 보고대상 날짜: " & Date.Today.AddDays(-1).Date.ToString.Substring(0, 10)           SendMailMessage( _             htmlMessageTo, htmlMessageFrom, _             htmlMessageSubject, htmlMessageBody, _             True, smtpServer, smtpLogin, smtpPasswd)         Dts.TaskResult = Dts.Results.Success     End Sub       Private Sub SendMailMessage( _         ByVal SendTo As String, ByVal From As String, _         ByVal Subject As String, ByVal Body As String, _         ByVal IsBodyHtml As Boolean, ByVal Server As String, _         ByVal Login As String, ByVal Passwd As String)           Dim htmlMessage As New MailMessage         Dim mySmtpClient As SmtpClient         Dim fromAddress As MailAddress         Dim toAddress As MailAddress         Dim fromAddrStr As String()         Dim toAddrStr As String()           fromAddrStr = From.Split(CChar(" "))         toAddrStr = SendTo.Split(CChar(" "))           If fromAddrStr.GetUpperBound(0) > 0 Then             fromAddress = New MailAddress(fromAddrStr(1), fromAddrStr(0))         Else             fromAddress = New MailAddress(fromAddrStr(0))         End If           If toAddrStr.GetUpperBound(0) > 0 Then             toAddress = New MailAddress(toAddrStr(1), toAddrStr(0))         Else             toAddress = New MailAddress(toAddrStr(0))         End If           With htmlMessage             .From = fromAddress             .To.Insert(0, toAddress)             .Subject = Subject             .Body = Body             .IsBodyHtml = IsBodyHtml             .Attachments.Add(New Mail.Attachment(Dts.Variables("xlsName").Value.ToString))         End With           mySmtpClient = New SmtpClient(Server)         mySmtpClient.EnableSsl = False ' SSL 연결을 사용할 경우 환경에 따라 변경하세요.         mySmtpClient.Port = 25 ' SMTP 포트입니다. 환경에 따라 변경하세요.         mySmtpClient.Credentials = New NetworkCredential(Login, Passwd)         Try             mySmtpClient.Send(htmlMessage)         Catch ex As Exception             MsgBox(ex.Message, MsgBoxStyle.OkOnly, "작업 실패")         End Try           System.Threading.Thread.Sleep(100)     End Sub End Class

 

17줄의 htmlMessageBody가 메일에 쓰여질 내용입니다. 미적 감각을 동원해서 꾸며보는 것도 좋을 것 같습니다.
66, 67줄에서 SSL 연결과 SMTP 포트를 지정해줄 수 있습니다. SMTP 환경에 따라 변경해주셔야합니다.
(테스트해보니 Gmail로는 잘 보내지지 않는 것 같습니다. 그래서 저는 사내 SMTP를 이용해서 테스트해봤습니다.)

 

5.       자, 패키지 작성이 완료되었습니다. 테스트를 한 번 해보겠습니다.


사용하는 아웃룩을 캡쳐했는데요. 입력한 두 개의 메일 주소로 모두 메일이 수신되었습니다.
엑셀 파일을 열어볼까요?


거의 놀고 있는 컴퓨터를 대상으로 성능 카운터를 뽑았더니 매우 심심한 데이터가 나왔습니다. -_-a
남은 것은 이 패키지도 작업으로 등록해서 하루에 한 번씩 실행되도록 하는 것입니다.

이번 이야기는 여기서 마치도록 하겠습니다. 사실 좀 더 손을 봐야 깔끔한 패키지가 될 것 입니다.
예를 들자면, 재부팅이 되면 네트워크 연결이 끊어질 수도 있습니다. 그러면 로그 파일을 옮겨올 수 없겠지요. 그 때의 처리라든지 DB 서버가 다운됐을 때의 처리 등을 추가하면 더욱 견고한 패키지가 될 것 입니다.

다음 번엔 어이 없는 주제로 글을 써보고 싶습니다. 어떤 주제가 있을런지… ㅎㅎ
긴~ 글 읽어주셔서 감사합니다. m( __)m 꾸벅~