Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Sending Mail In SSIS Using Script Task- Simpler and Flexible Approach Expand / Collapse
Author
Message
Posted Thursday, May 7, 2009 2:48 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:22 PM
Points: 727, 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/
Post #712445
Posted Friday, May 8, 2009 4:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 2,062, Visits: 1,437
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.


  Post Attachments 
Send Mail Task Editor.JPG (66 views, 45.00 KB)
Expression Builder.JPG (67 views, 81.97 KB)
Variable list.JPG (57 views, 61.26 KB)
Post #712784
Posted Friday, May 8, 2009 7:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 2,062, Visits: 1,437
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
Post #712864
Posted Sunday, May 10, 2009 2:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 4:04 AM
Points: 4, Visits: 105
But i think we can do this easily using the expressions rather than going for an extra script task
Post #713666
Posted Sunday, May 10, 2009 6:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:49 PM
Points: 27, 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.
Post #713809
Posted Monday, May 11, 2009 3:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 2,062, Visits: 1,437
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



  Post Attachments 
hardcoded variable.JPG (16 views, 40.14 KB)
variable with expression.JPG (15 views, 53.62 KB)
Post #713936
Posted Monday, June 8, 2009 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 3:15 PM
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
Post #730886
Posted Monday, June 8, 2009 10:21 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:22 PM
Points: 727, 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/
Post #731108
Posted Friday, July 31, 2009 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:22 AM
Points: 21, Visits: 246
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.
Post #763304
Posted Thursday, October 8, 2009 1:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 6, 2010 1:57 PM
Points: 1, 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.
Post #800297
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse