I’ve come across this issue where an dbmail task failed inside ssis
After troubleshooting, it turns out the credentails inside the connection manager was not in the msdb db and not a member of databasemailuserRole
Detailed info below:
Link: http://msdn.microsoft.com/en-us/library/ms188719(v=sql.105).aspx
This topic describes how to troubleshoot an error message stating that the user attempting to send Database Mail does not have permission to execute sp_send_dbmail.
The error text is:
EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
,@membername = '<user or role name>';
GO
