SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Paul Paiva
Paul Paiva
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 265
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
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 Visits: 1657
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.
Attachments
Send Mail Task Editor.JPG (72 views, 44.00 KB)
Expression Builder.JPG (72 views, 81.00 KB)
Variable list.JPG (62 views, 61.00 KB)
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 Visits: 1657
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
Ajit Goswami-482889
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 107
But i think we can do this easily using the expressions rather than going for an extra script task
BCC-493036
BCC-493036
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 289
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
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 Visits: 1657
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
Attachments
hardcoded variable.JPG (23 views, 40.00 KB)
variable with expression.JPG (20 views, 53.00 KB)
sandor_g
sandor_g
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 66
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
Paul Paiva
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 265
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
Jim McCoy-259251
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 261
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
Fernando Sanchez
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 21
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search