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

  • Paul Paiva

    SSCrazy

    Points: 2237

    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

    http://paulpaivasql.blogspot.com/

  • Lempster

    SSCoach

    Points: 15632

    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 http://bidshelper.codeplex.com) 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.

  • Lempster

    SSCoach

    Points: 15632

    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...

    Paul,

    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.

    Regards

    Lempster 🙂

  • Ajit Goswami-482889

    SSC Veteran

    Points: 230

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

  • BCC-493036

    Mr or Mrs. 500

    Points: 525

    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)?

    Thanks,

    BCC

    --------------------------------------------------------------
    DBA or SQL Programmer? Who Knows. :unsure:

  • Lempster

    SSCoach

    Points: 15632

    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

    vd.LockForWrite("Cnt_ProviderCount")

    vd.GetVariables(vars)

    Try

    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

    listForEnumerator.Add(varLocalFile)

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

    Next

    varProviderCount += 1

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

    Exit For

    End If

    Next

    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

    vars.Unlock()

    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 http://blogs.conchango.com

    HTH

    Lempster

  • sandor_g

    SSC Veteran

    Points: 230

    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.

    Sandor

  • Paul Paiva

    SSCrazy

    Points: 2237

    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

    http://paulpaivasql.blogspot.com/

  • Jim McCoy-259251

    Mr or Mrs. 500

    Points: 569

    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.

  • Fernando Sanchez

    SSC Rookie

    Points: 37

    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.

  • vani_r14

    SSCrazy

    Points: 2843

    Hi

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

    cheers

  • nancy.r.rosales.pang

    Newbie

    Points: 5

    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?

    Thanks,

    Nancy

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

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