Send Email Using SSIS
In this Article, I have explained to send a mail using script task.  Go to File à New and Open a SSIS Package like below,


Drag and drop the scrip task and rename it


In script Task, right click and edit it
 Then we will get the below screen. Here Give a Script task name and description.


And then the Scrip Table Click the Design Script 


It will open a new window like below


Here we will do the code like our .Net Code. See the below example, it is used to send a mail and it is having the variable names that is defined to our variable window(see below fig) 

Option Strict Off

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net



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 vars As Variables
        Dim myHtmlMessage As MailMessage
        Dim mySmtpClient As SmtpClient
        Dim Message As String = "This is Test Mail"
        Try


            myHtmlMessage = New MailMessage()

            Dim FromAddress As String
            Dts.VariableDispenser.LockOneForWrite("from", vars)
            FromAddress = vars("from").Value.ToString().Trim()
            vars.Unlock()

            Dim ToAddress As String
            Dts.VariableDispenser.LockOneForWrite("to", vars)
            ToAddress = vars("to").Value.ToString().Trim()
            vars.Unlock()

            Dim CCAddress As String
            Dts.VariableDispenser.LockOneForWrite("cc", vars)
            CCAddress = vars("cc").Value.ToString().Trim()
            vars.Unlock()

            Dim SmtpClient As String
            Dts.VariableDispenser.LockOneForWrite("smtpServer", vars)
            SmtpClient = vars("smtpServer").Value.ToString().Trim()
            vars.Unlock()


            Dim SmtpPort As String
            Dts.VariableDispenser.LockOneForWrite("smtpPort", vars)
            SmtpPort = vars("smtpPort").Value.ToString().Trim()
            vars.Unlock()


            myHtmlMessage.From = New MailAddress(FromAddress)

            Dim tt As String()
            If (ToAddress.ToString().Trim().Length > 0) Then
                If (ToAddress.IndexOf(";") > 0) Then
                    tt = ToAddress.Split(";")
                    Dim c As String
                    If (tt.Length > 0) Then
                        For Each c In tt
                            myHtmlMessage.To.Add(c)
                        Next
                    End If
                Else
                    myHtmlMessage.To.Add(ToAddress.ToString().Trim())
                End If
            End If

            Dim cc As String()
            If (CCAddress.ToString().Trim().Length > 0) Then
                If (CCAddress.IndexOf(";") > 0) Then
                    cc = CCAddress.Split(";")
                    Dim c As String
                    If (cc.Length > 0) Then
                        For Each c In cc
                            myHtmlMessage.CC.Add(c)
                        Next
                    End If
                Else
                    myHtmlMessage.CC.Add(CCAddress)
                End If
            End If

            Dim MailSubject As String = " Email scheduling process"
            Dim mailBody As String = "Hi, </BR>" + Message + "</BR> Thanks, </BR> XXX"

            myHtmlMessage.Subject = MailSubject
            myHtmlMessage.IsBodyHtml = True
            myHtmlMessage.Body = mailBody
            mySmtpClient = New SmtpClient(SmtpClient, SmtpPort)


            'Dim myCredentials As System.Net.NetworkCredential
            'myCredentials = New System.Net.NetworkCredential("nsuresh", "xxxx")

            'mySmtpClient.Credentials = myCredentials
            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
            mySmtpClient.Send(myHtmlMessage)

            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
        End Try
    End Sub

End Class





Now save and execute we will get mail shortly.


05:15 | Category: | 0 comments

Comments (0)

Post a Comment