실습 강좌 9 - 작업 종료 및 실패 시 메일 통보 설정

WebLogLoading_9강.dtsx

제 9강 작업 종료 및 실패 시 메일 통보 설정

 

한대성 (admin@sqlleader.com)

에이디컨설팅 / 책임 컨설턴트

SQLLeader.com / 운영자

 

 

이번 강좌에서는 패키지가 정상적으로 종료되거나 실패할 경우, 관리자나 담당자에게 메일을 발송하는 기능을 구현해보도록 하겠습니다.

 

메일 발송 기능은 SSIS에서 기본적으로 제공해주는 기능으로 하나의 강좌로 다룰 만큼의 분량이 되는 기능은 아닙니다. SMTP 연결 관리자를 추가하고, 이미 8강에서 다루었던 이벤트 처리기를 이용해서 발송할 메일을 설정해 주면 되는 것입니다.

 

하지만, 이렇게 별도의 강좌로 분리한 이유는 다음과 같습니다.

a.      SSIS에서 기본적으로 제공해주는 SMTP 연결은 인증 없음 또는 Windows 인증에 대해서만 설정 가능합니다. 예를 들어, GMail이나 로그인을 필요로 하는 SMTP 서버를 이용하고자 할 경우에는 설정할 수가 없습니다. 이에 대한 설정 방법을 다룰 것입니다.

b.      SSIS의 메일 보내기 기능은 텍스트 형태로만 발송됩니다. 기존의 DTS에 비해 MAPI가 아닌 SMTP 연결을 이용한다는 장점은 있지만, 텍스트 형태의 메일만 발송할 수 있다는 제약 사항이 있습니다. 본 강좌에서는 스크립트 작업을 이용한 HTML 메일 발송을 구현합니다.

 

 

8강까지 구성한 패키지를 엽니다.

 

제어 흐름에서 두 개의 변수를 추가합니다. ReadCnt 와 WriteCnt라는 이름의 Int32형 변수입니다. 이 변수는 파일로부터 읽어온 행 수와 테이블에 저장한 행 수를 보관하는 용도로 이용할 것입니다.

 

 

이제 WebLog 데이터 로딩 작업을 더블 클릭해서 데이터 흐름 영역을 연 다음, WebLogFile 원본과 조건부 분할, 멀티캐스트와 WebLogTable 대상 사이에 행 개수 변환을 각각 추가합니다.

 

 

윗 쪽에 있는 행 개수 변환을 더블 클릭해서 편집기를 연 다음, VariableName 속성에 추가한 “사용자::ReadCnt” 변수를 지정하고, 아래 쪽에 있는 행 개수 변환에는 “사용자::WriteCnt” 변수를 지정합니다.

 

 

 

이제, 데이터가 처리될 때 입력되는 행 수는 ReadCnt에 저장이 되고, 테이블에 저장되는 행 수는 WriteCnt 변수에 저장될 것입니다.

 

제어 흐름을 선택한 후, 다음과 같은 변수를 추가합니다.

이름	범위	데이터 형식	값
MessageFrom	WebLogLoading	String	admin@sqlleader.com
MessageTo	WebLogLoading	String	dshan@adconsulting.co.kr
SMTPServer	WebLogLoading	String	smtp.gmail.com
SMTPLogin	WebLogLoading	String	admin@sqlleader.com
SMTPPasswd	WebLogLoading	String	********

 

값 부분은 테스트 환경에 맞게 변경해서 지정하시기 바랍니다.

 

 

이벤트 처리기의 OnPostExecute 이벤트를 선택한 후, 스크립트 작업을 추가한 후, 기존에 추가한 로그 기록 작업과 연결시키고 작업의 이름을 메일 발송으로 변경합니다.

로그 기록 작업과 메일 발송 작업을 연결하는 연결선을 더블 클릭하여 선행 제약 조건 편집기를 연 다음, 평가 작업을 식으로 변경하고 식(X) 부분에 다음과 같은 조건 식을 입력합니다.

 

             @[System::PackageID] == @[System::SourceID]

 

 

OnPostExecute 이벤트 중에서 작업 개체의 완료가 아닌 패키지의 완료일 때에만 메일 발송 작업이 실행되도록 설정한 것입니다.

 

메일 발송 스크립트 작업을 더블 클릭하여 작업 편집기를 연 다음, 스크립트 탭의 ReadOnlyVariables 부분에 다음과 같은 속성을 입력합니다.

 

MessageFrom,MessageTo,SMTPServer,SMTPLogin,SMTPPasswd,ReadCnt,WriteCnt

속성을 입력할 때 [,] 사이에 공백을 넣지 않도록 주의하시기 바랍니다.

 

 

이제, 아래 부분에 있는 스크립트 디자인(S) 버튼을 클릭하여 VSA(Visual Studio for Application)을 연 후, 다음과 같은 스크립트를 입력합니다.

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 = "SSIS 예제 작업 완료 통보 메일[" & Date.Today.ToString & "]"         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> 입력 행수: " & Dts.Variables("ReadCnt").Value.ToString & "<BR>" & _             " <li> 출력 행수: " & Dts.Variables("WriteCnt").Value.ToString & _            " <BR><BR>" & _             " <font style='font:9pt Arial;font-weight:bold;'>SQLLeader.com</font>"           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           End With           mySmtpClient = New SmtpClient(Server)         mySmtpClient.EnableSsl = True  ' SSL 연결을 사용할 경우 환경에 따라 변경하세요.         mySmtpClient.Port = 25 ' SMTP 포트 / 환경에 따라 변경하세요.         mySmtpClient.Credentials = New NetworkCredential(Login, Passwd)         mySmtpClient.Send(htmlMessage)       End Sub   End Class

 

패키지를 실행해서 메일이 정상적으로 발송되는지 확인합니다.

 

 

 

메일 발송 스크립트 작업을 복사한 후, 이벤트 처리기의 OnError 이벤트 영역에 붙여 넣습니다.

 

 

메일 발송 작업을 더블 클릭해서 편집기를 연 다음, 스크립트 탭의 ReadOnlyVariables 부분 중 ReadCnt,WriteCnt 변수 대신 ErrorCode,ErrorDescription을 추가합니다.

 

MessageFrom,MessageTo,SMTPServer,SMTPLogin,SMTPPasswd,ErrorCode,ErrorDescription

 

 

스크립트 디자인(S)를 클릭하여 VSA를 연 다음, 다음과 같이 Sub Main() 부분을 일부 변경하고 VSA를 닫습니다.

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 = "SSIS 예제 에러 발생 통보 메일[" & Date.Today.ToString & "]"         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> 에러코드: " & Dts.Variables("ErrorCode").Value.ToString & "<BR>" & _             " <li> 에러사유: " & Dts.Variables("ErrorDescription").Value.ToString & _            "<BR><BR>" & _             " <font style='font:9pt Arial;font-weight:bold;'>SQLLeader.com</font>"           SendMailMessage( _             htmlMessageTo, htmlMessageFrom, _             htmlMessageSubject, htmlMessageBody, _             True, smtpServer, smtpLogin, smtpPasswd)           Dts.TaskResult = Dts.Results.Success       End Sub

 

이제, 제어 흐름에 임의로 에러를 발생시키는 작업을 추가한 후, 패키지를 실행해 봅니다. 본 예제에서는 임의 에러 발생이라는 SQL 실행 작업을 추가한 후, Connection을 지정하고 SELECT 1/0 이라는 쿼리를 이용하여 0으로 나누기로 인한 오류가 발생하도록 설정하였습니다.

 

 

 

 

 

 

 

위의 작업들을 적절히 잘 활용하여 작업 결과나 에러 통보를 효과적으로 알려주는 모니터링 프로세스를 쉽게 구현할 수 있습니다.