Sending Mail In SSIS Using Script Task- Simpler and Flexible Approach

  • Great article Mainish, thanks.

    As was clearly stated in the "Purpose/Scope" section of the article, the intention is to show how you can use a script task to send email. It did not state that it is the best approach in all cases.

    I love this site and reading the forums, but I am often surprised that so many people have a need to display their arrogance and "always be right." While some are courteous, thoughtful, and considerate, some are not.

    Clearly, your mileage may vary! Of course you need to judge what mechanism is best for your needs - an article can't do that for you. But IF in your assessment you decide you want to use a script task, then this article shows you the way.

    For the record, I can see the advantages of using expressions, BUT there is also an advantage to coding the whole thing in a script insofar as it isn't so GUI-intensive when you define it. Depends what your values are. Some people like tomato juice, some people don't, but neither defines you as a better person.

    My wish list for SSIS is to be able to script a whole package, in the way it was possible to make a .BAS file out of a DTS package and then edit it to your heart's content. Also similar to programming in .NET - you can drag text boxes onto a form, or you can code the placement of text boxes onto your form. That's what makes programming in a full .NET environment superior to "programming" in a drag'n' drop GUI.

    My 2 cents.

    - Paul

  • BCC,

    Hopefully these screenshots shold give you a start.

    You'll need to add expressions to the Send Mail task which will reference existing variables.

    You can test your expressions for syntax errors by clicking the 'Evaluate Expression' button; if you have values assigned to your variables you will see them in the evaluated expression.

    Something I find handy is to use BIDS Helper (download from which will place a coloured triangle in the top lefthand corner of any task that uses expressions. That way you can see at a glance whether expressions are being used in a package. It does the same thing (albeit a different colour) for Connection Managers.

  • Paul Paiva (5/7/2009)


    As was clearly stated in the "Purpose/Scope" section of the article, the intention is to show how you can use a script task to send email. It did not state that it is the best approach in all cases.

    ... I am often surprised that so many people have a need to display their arrogance and "always be right." While some are courteous, thoughtful, and considerate, some are not...


    I am not sure whether you post was aimed at me, but I feel compelled to defend myself! The title of the article was 'Sending Mail In SSIS Using Script Task- Simpler and Flexible Approach' and I personally I disagree that it is simpler than using the Send Mail task with expressions. I am certainly not saying that no one should ever use a Script Task as Manish suggests, merely that it wouldn't be my choice and judging by other comments posted it would not be the choice of others either.

    If you come from a programming background (which I don't) then you might consider it easier to use a script because you're more comfortable with that.

    There are many ways to skin a cat and it is up to the individual which one they choose; my initial post reflects my personal experience/preference and what I've learned from courses, other SSIS forums and from people more experienced than myself.


    Lempster 🙂

  • But i think we can do this easily using the expressions rather than going for an extra script task

  • Lempster,

    Thank you for your help. Now I have another question regarding to my project. basically I need to generate a report and export it to the spreadsheet and then send an email with the attachment to some people daily. I was asked to put the date (yyyy-mm-dd) into the xls filename. Do you have any idea how to do that (build a dynamic filename)?



    DBA or SQL Programmer? Who Knows. :unsure:

  • BCC

    I can think of a couple of ways off the top of my head, but there are probably others.

    1. Using variables and expressions.

    2. Using a script task(!)

    The choice depends to some extent on whether you are just producing one file and therefore just need to append or prepend the date to a single filename, or, you need to loop through multiple files and so have a different filename each time, in which case a Script Task combined with a For Each loop would probably be the way to go.

    For a single file, create two variables - one to store the filename and one to store the current date which you can get from executing a simple Execute SQL task, returning getdate() and doing a CAST or CONVERT to get the result in the format you want, e.g. yyyy-mm-dd.

    You can then use an expression on your filename variable to append or prepend the contents of the date variable.

    To illustrate the idea, the first attached screenshot shows a variable in one of my packages (in fact I have this variable in all my packages) whose value is hardcoded, i.e. no set using an expression.

    The second screenshot shows a second variable whose value is set dynamically using an expression, based on the value of the first variable.

    If you go down the Script Task route and need to process multiple files then something like this script will give you a list of files which you can then pass to a For Each loop. Within the For Each loop you could append or prepend the date to the filename of each individual file:

    Option Strict Off

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports System.Collections

    Imports System.IO

    Public Class ScriptMain

    Private listForEnumerator As ArrayList

    ' This script builds an array of *.xls files that is passed to an Object type

    ' variable for later use in a ForEach loop.

    Public Sub Main()

    Dim varSourceDirectory As String

    Dim varLocalFileList() As String

    Dim varLocalFile As String 'to store full path and name of each file

    Dim varFirstLevelSubFolder As String 'to store name of each sub-folder

    Dim varProviderCount As Int16

    Dim varArrPath As Array

    Dim varProviderName As String

    Dim vd As VariableDispenser = Dts.VariableDispenser

    Dim vars As Variables

    vd.LockForRead("vSourceDirectory") 'passed in from Package

    vd.LockForRead("vExecuteFor") 'contains value of /SET option in job step used to execute package

    'for a single Provider

    vd.LockForWrite("vFileList") 'will hold the array contents




    listForEnumerator = New ArrayList

    varSourceDirectory = vars("vSourceDirectory").Value.ToString

    'If sub folders exist one level below the SourceDirectory, look in each

    'sub folder for .xls files and add them to the array.

    If Directory.GetDirectories(varSourceDirectory).Length > 0 Then

    varProviderCount = 0

    For Each varFirstLevelSubFolder In Directory.GetDirectories(varSourceDirectory)

    varLocalFileList = Directory.GetFiles(varFirstLevelSubFolder, "*.xls")

    varArrPath = Split(varFirstLevelSubFolder, "\")

    varProviderName = varArrPath(UBound(varArrPath))

    'MsgBox("Provider: " & varProviderName, MsgBoxStyle.Information)

    For Each varLocalFile In varLocalFileList


    'MsgBox("File name: " & System.IO.Path.GetFileName(varLocalFile), MsgBoxStyle.Information)


    varProviderCount += 1

    If varProviderName = vars("vExecuteFor").Value.ToString Then

    Exit For

    End If


    End If


    'MsgBox("Number of Providers: " & varProviderCount.ToString, MsgBoxStyle.Information)

    vars("vFileList").Value = listForEnumerator 'Pass the contents of the array to an object variable for use later in the package

    vars("Cnt_ProviderCount").Value = varProviderCount


    Dts.TaskResult = Dts.Results.Success

    Catch ex As Exception

    Dts.Events.FireError(0, "", ex.Message & ControlChars.CrLf & ex.StackTrace, String.Empty, 0)

    Dts.TaskResult = Dts.Results.Failure

    End Try

    vd = Nothing

    End Sub

    End Class

    There are many good examples of using Script Tasks by Jaime Thomson at



  • I had similar flexibility problem with the Send Mail task, but I used a different approach to overcome it. I used a SQL query and redirected the result to a Object variable. With a Foreach Loop Container, I mapped the variable to the Send Mail task’s Expressions. This way I can send emails to multiple addresses with the same message or send different messages to different addresses. Basically, I can dynamically change any part (attachment, priority, smtp connection, etc.) of the Send Mail task.

    Let me know if you interested.


  • Hi Lempster,

    No, I was not "aiming" my comments at you! I was just defending the author - I felt that he was NOT saying "always use script for send mail" but rather, "if you for whatever reason choose to send mail using a script, then this is your 'how to guide'".

    As you mentioned, we each have our backgrounds, so it is nice that there are many avenues to get a given task done.

    - Paul

  • I use sandor_g's approach from a series of tables for equating individuals to particular types of emails in SSIS. Great flexibility when sending to multiple receivers. I do choose to set up my email body's in HTML format, especially when I have data included from a SQL query or other dynamic content from whatever is generating the email, report, error or just informational. Using HTML you are also not limited by the normal body content size, 1 Mb I think I recall. Plus the possibility of incorporating links for further information on specific troubleshooting. I am not the premier SSIS programmer but from extensive email reporting in DTS I naturally adapted the same process when moving to SSIS.

  • I agree with some of the comments made regarding this method. I do appreciate the opportunity to review it being that I am new SSIS. One of the advantage of doing it this way is that you can save the code to file and compare prior and new changes. I am using expression to change the parameters for the Send Mail Task.

  • Hi

    Thanks for the post... A little more explanation or comment on how to create the config file might have been helpful


  • I can see the images correctly, but I'm trying with the script and I have an error:

    name dts is not declared

    I don't know what I have to do in tis case. I'm using the Script Task with Microsoft Visual Basic 2008

    Any idea?



Viewing 12 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply