Rick Caminiti dotCOM

Here’s a quick and dirty way to send email via MSSQL.  For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option.  Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

 

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell.  Fun stuff.

 

 

 

 

declare @developerperson varchar(80)

declare @message_body varchar(1000)

 

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ‘’s’

 

 

EXECUTE dbo.sp_SQLSMTPMail

      @vcTo = ‘You@you.com’,

      @vcCC= ‘Them@them.com’,

      @vcSubject = ‘Super Cool SQL Email’,

      @vcBody = @message_body,

      @vcFrom = ‘Me@me.com’,

      @vcSMTPServer = ‘SMTPName’,

      @vcSenderName = ‘Some One’,

      @vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

  @vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ’select col1, col2, col3, col4 from database.schema.table’

 

 

 

 

 

 

 

 

Here’s the actual Proc definition: Read more…

The other day I was trying to figure out how to send an email via Script Task in SSIS. For some reason, the Send Email Task wasn’t working for me (still can’t figure out why). Anyway, here’s what I ended up with.

Works like a charm…


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain
    Public Sub Main()
      Dim myHtmlMessage As MailMessage
      Dim mySmtpClient As SmtpClient

      myHtmlMessage = New MailMessage(“email@somewhere.com”, “email@somewhereelse.com”, “Subject”, “Body”)
      mySmtpClient = New SmtpClient(“SMTP”)
      mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
      mySmtpClient.Send(myHtmlMessage)
      Dts.TaskResult = Dts.Results.Success
    End Sub
End Class