요약 리포트 생성 및 발송 패키지 구현 – (4) 결과 파일 발송

요약 리포트 생성 및 발송 패키지 구현 – (4) 결과 파일 발송

 

한대성

MS SQL Server MVP

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

 

 

이번 강좌에서는 생성된 엑셀 파일을 날짜 별로 만든 다음, 이를 관리자에서 메일로 발송하는 기능을 구현하겠습니다.

 

3강까지 진행한 강좌를 이용해서 계속 설명하겠습니다.

 

이제 일자 별로 데이터를 출력하는 것 까지는 되었습니다. 그럼 생성되는 엑셀 파일도 날짜 별로 만들고 싶습니다. 어떻게 하면 좋을까요?

가장 간단한 방법은, ExcelReport.xls 파일에다가 결과를 출력한 후, 이 파일을 ExcelReport_200800710.xls 형태로 파일 복사를 하는 것입니다. (괜히 복잡하게 엑셀 파일을 생성한다든지 등과 같은 복잡한 생각은 나~중에 하시고.)

 

파일을 복사하는 작업을 구현하기 위해 파일 시스템 작업을 추가하고, 이름을 일별 파일 생성으로 변경한 후, 패키지의 가장 마지막 부분에 연결합니다.

 

 

 

이제, 연결 관리자에서 새 파일 연결을 선택한 후, 아래와 같이 사용 유형을 기존 파일로 선택하고 ExcelReport.xls를 지정합니다.

 

이 연결의 이름을 원본 파일이라고 변경해 줍시다.

그런 다음, 동일한 방법으로 또 하나의 파일 연결을 추가합니다. 이번에는 사용 유형을 파일 만들기라고 설정하고, 파일 이름에 다음과 같이 임의의 이름을 설정합니다.

 

이 연결의 이름을 일별 파일이라고 변경해 줍시다.

이제, 일별 파일 연결을 선택한 후, 속성 창의 Expressions 오른쪽에 있는 ... 버튼을 클릭하여 속성 식 편집기를 엽니다.

 

속성을 ConnectionString으로 선택한 후, 식 부분의 ... 버튼을 클릭해서 다음과 같이 식을 입력합니다.

 

식 부분에 다음과 같이 입력합니다.

"D:\\ExcelReport_" +  REPLACE(@[사용자::ExecDate],"-","") + ".xls"

역 슬래쉬(\)가 두 개인 것 주의하세요~.

 

지금까지 설정한 것은 대상 파일, 즉 일별 파일의 이름을 자동으로 설정해 주는 작업입니다.

 

확인을 눌러 편집기를 닫은 후, 일별 파일 생성 작업을 연 후, 아래와 같이 설정합니다.

 

 

이제, 담당자에게 메일 발송하는 기능을 추가합시다. (간단히 하겠습니다.^^)

 

SMTP 연결을 이용해서 메일 작업을 구성해도 되지만, 별로 안 좋습니다. 왜?

우선, SSIS에서 지원하는 SMTP 연결은 윈도우 인증만 됩니다. (켁~) 예를 들어, 제가 사용하는 smtp 메일 서버인 mail.adconsulting.co.kr은 사용할 수 없습니다.

두 번째는 메일 보내기 작업이 텍스트 형태의 메일만 지원한다는 것입니다. 색깔도 좀 넣고 굵은 글씨도 좀 넣고 싶은데, 할 수가 없지요.

 

그래서, 저는 메일 보내기 작업을 할 때마다 스크립트 작업을 사용합니다.

 

제어 흐름 영역의 가장 마지막 단계에 스크립트 작업을 하나 추가하고 이름을 메일 보내기로 변경합니다.

작업 편집기를 연 후, ReadOnlyVariables 속성 부분에 ExecDate 변수를 입력합니다. 스크립트 내에서 SSIS의 ExecDate 변수를 쓰겠다라는 것입니다.

 

이제, 아래에 있는 스크립트 디자인(S) 버튼을 클릭해서 편집기를 연 후, 다음과 같은 스크립트를 입력합니다.

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 ExecDate As String = Dts.Variables("ExecDate").Value.ToString         Dim AttachFileName As String = Dts.Connections("일별 파일").ConnectionString         Dim htmlMessageTo As String = "xxxx@xxxx.xxx" ' 메일 수신자         Dim htmlMessageFrom As String = "xxxx@xxxxxxxxxx.co.kr" ' 메일 발송자         Dim htmlMessageSubject As String = "SSIS 예제 결과 발송[" & ExecDate & "]"           Dim smtpServer As String = "mail.adconsulting.co.kr" ' SMTP 서버         Dim smtpLogin As String = "xxxx@xxxxxxxxxx.co.kr"         Dim smtpPasswd As String = "xxxxxxxxx"           Dim htmlMessageBody As String           htmlMessageBody = "<H1>엑셀 결과 발송 메일</H1><BR>" & _             " <BR><BR>" & _             " <font style='font:9pt Arial;font-weight:bold;'>SQLLeader.com</font>"           SendMailMessage( _             htmlMessageTo, htmlMessageFrom, _             htmlMessageSubject, htmlMessageBody, _             True, smtpServer, smtpLogin, smtpPasswd, AttachFileName)           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, ByVal AttachFileName 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               If AttachFileName <> "" Then                 Dim fileattach As New Mail.Attachment(AttachFileName)                 .Attachments.Add(fileattach)             End If           End With           mySmtpClient = New SmtpClient(Server)         mySmtpClient.EnableSsl = False  ' SSL 연결을 사용할 경우 환경에 따라 변경하세요.         mySmtpClient.Port = 25 ' SMTP 포트 / 환경에 따라 변경하세요.         mySmtpClient.Credentials = New NetworkCredential(Login, Passwd)         mySmtpClient.Send(htmlMessage)       End Sub   End Class

 

수행해 봅시다.

 

 

다 되었습니다.

 

이제 이것을 SQL Agent에 등록하고 새벽 시간에 수행하도록 설정해 놓으면 매일 아침에 오면 이와 같은 결과 요약 메일이 와 있을 것입니다.

 

 

 

강좌를 마치며..   역시 이번 강좌도 미흡했습니다. 큭~ “시작은 미약하지만 끝은 창대하리라”가 아니라 “시작은 거창하지만 끝은 미흡하리라” 입니다.   2번 강좌를 쓴 게 5월 10일이고, 3번 강좌를 쓴게 오늘(7월 11일)이니깐 2달만에 다시 쓰게 되었네요. 중간에 지방 출장에다가 주말 근무, 개인적인 사정 등 정신없이 지나치다가 또 한동안 무기력 상태에 빠져 있었습니다. 그러다가 2번 강좌까지 읽은 조횟수가 500을 넘어선 것을 보고선, 혹시라도 다음 강좌를 기다리시는 분이 계시지 않을까 해서 이렇게 마음 동여매고 마무리 지어 봅니다.