May 7, 2009 at 2:48 pm
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/
May 8, 2009 at 4:35 am
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.
May 8, 2009 at 7:03 am
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 🙂
May 10, 2009 at 2:03 am
But i think we can do this easily using the expressions rather than going for an extra script task
May 10, 2009 at 6:22 pm
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:
May 11, 2009 at 3:16 am
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
June 8, 2009 at 12:21 pm
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
June 8, 2009 at 10:21 pm
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/
July 31, 2009 at 12:09 pm
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.
October 8, 2009 at 1:19 pm
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.
December 14, 2010 at 3:34 pm
Hi
Thanks for the post... A little more explanation or comment on how to create the config file might have been helpful
cheers
November 10, 2011 at 8:34 pm
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 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply