SSIS SEND MAIL TASK

Question

SSIS SEND MAIL TASK

sql_learner29

SSCrazy Eights

Points: 9107
More actions
July 22, 2008 at 2:50 pm

#123237

Hi all
Can anyone tell me how to use to/cc/subj/messg variables in SSIS package..
I mean I will provide columns and based on tht the to cc and subj should be populated...
Any help on this
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 12 posts - 1 through 11 (of 11 total)

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

Mukti SSCrazy Points: 2826 More actions · Answer 1

One way if your CC and subject is stored in DB is to create variables eg vEmailCC and vEmailSubject both of string type and then create a Execute sql task to assign values to these variables using resultset tab. Please note that i am assuming that these values are stored somewhere in Database. If not you just need to assign appropriate values to these variables.

Now in the Send Mail Task go to Expressions section select CCLine and put @[User::vEmailCC] as expression. Do the similar with subject variable too.

~Mukti

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

Thanks I Got this much but 1 more thing ..

If I use the Variable name in CC and ToLine

then what will I write in actual To Box and CC Box....As SSIS expects a proper email format string from me

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 3

I mean I tried adding Expression for ToLine and CCLine but Still It show me a warning sign that to format is not proper...

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 4

Now, I modify the Send Mail Task to include an Expression which updates the "ToLine" property with the email address in the "rsDetails" variable.

Moreover, I am not hard-coding the "To" property in the "Mail" tab of "Send Mail Task Editor" and leaving it blank. Now, it does not allow me to execute the package and gives me a "Package Validation Error" saying: No recipient is specified.

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Mukti SSCrazy Points: 2826 More actions · Answer 5

Try setting the DelayValidation = True on Send Mail task.

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 6

Thanks Mukti..It Works Fine Now 🙂

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 7

When I did this Send mail task with Single Row from table in result set in the EXECUTE SQL TASK ..it worked fine but now I want to send mail to multiple recipents that I am gettin g from such a query:

select email

from user1

where userid in(

select userid

from adminroles

where roleid = (select roleid from notification where seqno =100))

This will result in 2 rows(2 Emails)

What shud I change now so that my Sendmail will Run Fine..

How to handle Full result Set in one Variable

Thanks In Advance

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

shivashish.das SSC Enthusiast Points: 112 More actions · Answer 8

Hi SQL Learner,

If you are pulling multiple email ID's you'd be better off storing the result set in an object (use a for-each loop container) and pass that to a mail task. Just my two cents.

regards

Shiva

All good movies deserve a SQL.

peemd SSC Rookie Points: 43 More actions · Answer 9

It's validation error only. Just initialize your variable (in variables pane) with some dummy email address. This will pass the validation.

hussain27syed Ten Centuries Points: 1191 More actions · Answer 10

I am not sure if you can use a Full row set variable in sendmail task.

Use a for each loop with a script task in it to append all the retrieved rows to a single variable.

Use ; to seperate multiple id's

U can then use this variable in the send mail task.

Another alternative is to have the variable in which you can add all email id's seperated by ;

U can include this variable in the config file which would make adding and removing email id's easier.

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 11

select email

from user1

where userid in(

select userid

from adminroles

where roleid = (select roleid from notification where seqno =100))

i am not sure how ur email are stored in tbl. I am assuming that you have one row for each email address. what u can do as someone above suggested get all email address and then concatenaet all email address seperated by ";'. A single variable and a single execute task will do that. here is the piece of code that will do for u (u need to change it to reflect ur sql query)

DECLARE @Names varchar ( 4000 )

SET @Names = ''

SELECT @Names = emailAddrress + '; ' + @Names

FROM dbo.tblEmailRecipients

WHERE MailType = 'ErrorReport'

SELECT @Names = ltrim(RTRIM ( @Names ))

SELECT @names = left(@names ,len(@names) -1 )

SELECT @names AS Emails

In the execute sql task. u will have return type as single row and in result set page map the variable accorindly......then in ur tosend expreesion of send mail task put this variable which will hold all the email that it rturns back