August 27, 2008 at 12:36 pm
I have few SSIS packages where I am using a Execute SQL task in the Event Handlers to send Emails to Distribution Lists. In the expression window I have selected "sqlStatementSOurce" as propert and I have this Expression---
"EXEC msdb.dbo.sp_send_dbmail
@profile_name = '"+ @[User::DBProfileName] +"' , " +
"@recipients = 'gyanendra_khadka@bcbstx.com; jerry_xia@bcbstx.com'" + ", " +
"@subject = " + "'" + @[System::PackageName] + " @task--"+@[System::SourceName] +", FAILED!!!"+ "'" +"," +
"@body = " + "'" + @[System::ErrorDescription] + " ' " + "," +
"@body_format =" + "'" + "TEXT" + "'" + "," +
"@importance =" + "'" +"HIGH" + "'"
Here As you can see I am exec a dbmail and using different variables for the parameters and value.
I have a user variable called DBprofilename which holds the value of profile name. I did this becoz i am hoping that once we move to diff environments, I will have diff profilers , SO i am thinking of having a Config file for this variable. So that I can change at one place. ( ALSO if anyone know that this profileer is independent of SQL server, meaning it will work if i go to another Environment) Is theer any way to avoid this variable and make it more dynamic without hard coding it.
The real issue here is @ recipients, I don't want to hard code it . I want to select it from a table where we have all those distribution lists.--
CREATE TABLE [dbo].[tblEmailRecipients](
[EmailAddr] [varchar](100) NOT NULL,
[Name] [varchar](50) NULL,
[MailType] [varchar](50) NULL
this is a table structure. I have also the code to select it in the way it excepts----
DECLARE @Names varchar ( max )
SET @Names = ''
SELECT @Names = emailAddr + '; ' + @Names
FROM #A
SELECT @Names = ltrim(RTRIM ( @Names ))
SELECT @names = left(@names ,len(@names) -1 )
SELECT @names
Thsi piece of code selects all email adress in the format like 'abc@tx.com;xyz@tx.com; and so on...
But how can put this value in my expression so that it picks email address from this table
Thanks to all for taking the time to read and think
August 27, 2008 at 1:40 pm
You can use Execute SQL task to set the recepient emails into a user variable (Note: varchar(max) will not map to string type, you can use varchar(4000) instead)
OR you can use the foreach loop to send emails individually to all recipients.
Just curious why are you not using SendMail task?
August 27, 2008 at 2:34 pm
The very first reason, I don't have SMTp server name, nor they will provide one fo rme here. You don't know how micro things are here in case of security.
Second , i have used send mail task several times before and one time it send like 801 emails for the same PK voiltaion while inserting to table.
It was like devil..................
And also, I don't want more coplexcity in the package. There is nothing I want to do when pacakge fails , just send email but then i want to select it from tbl.
I can make a proc and inside the proc i can exec the dbmail and call that proc through Execute sql task, but i also want to avoid this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply