February 6, 2008 at 10:37 am
Okay, I give up, I surrender.
I need to send an e-mail notification when a job fails, no big deal right? The problem is that I just can not get this to work. I've tried the 'Send Mail Task', I've tried a SQL task, things just do not work. I get a variety of messages depending on what I'm using, from, 'Database Mail XPs component is not enabled', it is; to the ubiquitous, 'Send Mail Failed', (oh, thanks a lot):sick:
Could someone please at least point me the right direction?
Tim
February 6, 2008 at 10:48 am
If your using an execute SQL task you would have to enable the database mail XP(s) via the surface area configuration tool.
What type of error are you getting from the Send Mail task in SSIS? Have validated your SMTP relay (via Telnet, http://support.microsoft.com/kb/153119).
Tommy
Follow @sqlscribeFebruary 6, 2008 at 10:52 am
I think you may need to set up database mail, in the following script that I use
1. search and replace ServerName with your servername,
2. add your mail server where commented --mailserver,
3. add your email adddress to test where prompted --your email address
You may also have to set up SQL agent properties Alert System to Enable mail profile.
--Reconfigure the server to allow email
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
reconfigure
go
/*Add Service Account Permission
As an extra security feature SQL Server 2005 uses a role with specific permissions in the MSDB database, not even system administrators can use the mail feature without being a member of this role. So you need to either add the specific account that runs SQL Server to this role, or add all administers:-
*/
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'ServerNameMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'ServerName@mail.com',
@display_name = 'ServerNameMailAccount',
@use_default_credentials = 0,
@mailserver_name = 'mail.server.net' --mailserver
/*
If you look at the propertities you may want to alert the anonymous login to SQL Server authentication.
Create Mail Profile
The next component of the configuration requires the creation of a Mail profile.
We are going to create "ServernamMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.
For help on options use sp_helptext sysmail_add_profile_sp
*/
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'ServerNameMailProfile',
@description = 'Profile used for database mail'
/*
Add Account Profile
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account usding the Database Mail profile you created in last step
For help on options use sp_helptext sysmail_add_profileaccount_sp
*/
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'ServerNameMailProfile',
@account_name = 'ServerNameMailAccount',
@sequence_number = 1
/*Set default Profile
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
For help on options use sysmail_add_principalprofile_sp
*/
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'ServerNameMailProfile',
@principal_name = 'public',
@is_default = 1 ;
/*To send a test email from SQL Server. Execute the statement below.*/
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='SQLAlerts@mail.com', --your email address
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;
February 6, 2008 at 11:02 am
:w00t: Outstanding...Thank you :blush:
Tim
July 28, 2008 at 10:19 am
This worked beautifully for us as well. Many thanks for the script and instructions! This saved me LOTS of time trying to figure out how to integrate mail in SSIS pkg.
MJ
~mj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply