Library references

• System
• System.Data
• Microsoft.SqlServer.ScriptTask
• Microsoft.SqlServer.ManagedDTS
• CozyRoc.SSISPlus

ScriptMain

Option Explicit On ' Microsoft SQL Server Integration Services Script Task ' ' Copyright (c) 2007 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.ComponentModel Imports System.Net.Mail Imports Microsoft.SqlServer.Dts.Runtime Imports CozyRoc.SqlServer.SSIS.Attributes <SortProperties(New String() {"SmtpConnection", "Username", "Password", "FromSender", "ToRecipient", "Subject", "Message"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() Dim result As Integer Dim smtpClient As SmtpClient Dim auth As System.Net.NetworkCredential Dim message As MailMessage Dim fireAgain As Boolean Try ' Setup SMTP client. smtpClient = GetSmtpClient_() ' Setup credentials. auth = New System.Net.NetworkCredential(Me.Username, Me.Password) smtpClient.UseDefaultCredentials = False smtpClient.Credentials = auth ' Send message. message = New MailMessage(Me.FromSender, Me.ToRecipient, Me.Subject, Me.Message) Call smtpClient.Send(message) Call Dts.Events.FireInformation( _ 0, _ String.Empty, _ String.Format("Successfully send email message to {0}", Me.ToRecipient), _ String.Empty, _ 0, _ fireAgain) result = Dts.Results.Success Catch ex As Exception result = Dts.Results.Failure Call Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0) End Try Dts.TaskResult = result End Sub ' Main ' Validation routine for parameters. Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean result = False If Me.FromSender = "" Then errMessage = "Please specify FromSender." ElseIf Me.ToRecipient = "" Then errMessage = "Please specify ToRecipient." ElseIf Me.Subject = "" Then errMessage = "Please specify Subject." ElseIf Me.Message = "" Then errMessage = "Please specify Message." Else result = True End If Validate = result End Function ' Validate <Connection("SmtpConnectionType")> _ <Category("Connection")> _ <Description("Specifies the name of the SMTP connection manager.")> _ Public Property SmtpConnection() As String Get SmtpConnection = m_smtpConnection End Get Set(ByVal value As String) m_smtpConnection = value End Set End Property ' SmtpConnection <Category("Connection")> _ <Description("Specifies the username to use to authenticate with the SMTP server.")> _ Public Property Username() As String Get Username = m_userName End Get Set(ByVal value As String) m_userName = value End Set End Property ' Username <PasswordPropertyText(True)> _ <Category("Connection")> _ <Description("Specifies the password to use to authenticate with the SMTP server.")> _ Public Property Password() As String Get Password = m_password End Get Set(ByVal value As String) m_password = value End Set End Property ' Password <Category("Mail")> _ <Description("Specifies the e-mail address of the message sender.")> _ Public Property FromSender() As String Get FromSender = m_fromSender End Get Set(ByVal value As String) m_fromSender = value End Set End Property ' FromSender <Category("Mail")> _ <Description("Specifies the recipients of the e-mail message. Separate the recipients with semicolons.")> _ Public Property ToRecipient() As String Get ToRecipient = m_toRecipient End Get Set(ByVal value As String) m_toRecipient = value End Set End Property ' ToRecipient <Category("Mail")> _ <Description("Specifies the subject line for the message.")> _ Public Property Subject() As String Get Subject = m_subject End Get Set(ByVal value As String) m_subject = value End Set End Property ' Subject <TextDialog("Message content")> _ <Category("Mail")> _ <Description("Specifies the contents of the message.")> _ Public Property Message() As String Get Message = m_message End Get Set(ByVal value As String) m_message = value End Set End Property ' Message Private ReadOnly Property SmtpConnectionType() As String Get SmtpConnectionType = "SMTP" End Get End Property ' SmtpConnectionType ' Setup SMTP client from specified SMTP connection. Private Function GetSmtpClient_() As SmtpClient Dim result As SmtpClient Dim connection As ConnectionManager Dim connectionString As String connection = Dts.Connections(Me.SmtpConnection) connectionString = connection.ConnectionString result = New SmtpClient(GetConnectionParameter_(connectionString, "SmtpServer")) result.EnableSsl = Convert.ToBoolean(GetConnectionParameter_(connectionString, "EnableSsl")) GetSmtpClient_ = result End Function ' GetSmtpClient_ ' Extracts parameter from specified connection string. Private Function GetConnectionParameter_( _ ByVal connectionString As String, _ ByVal parameter As String) As String Dim result As String Dim startOf As Integer Dim endOf As Integer parameter += "=" startOf = connectionString.IndexOf(parameter) If startOf <> -1 Then startOf += parameter.Length endOf = connectionString.IndexOf(";", startOf) result = connectionString.Substring(startOf, endOf - startOf) End If GetConnectionParameter_ = result End Function ' GetConnectionParameter_ Private m_smtpConnection As String Private m_userName As String Private m_password As String Private m_fromSender As String Private m_toRecipient As String Private m_subject As String Private m_message As String End Class ' ScriptMain