Post reply

SSIS - send mail task

  • September 15, 2008 at 2:49 am

    #126324

    How to send mail using SEND MAIL TASK?

    I m not able to set smtpconnection property? how to set the port number?

    any help on this is apprecited.

    Thanks in advance

  • September 15, 2008 at 8:04 am

    #871754

    Hi,

    I guess you might be knowing about Send Mail Task Editor (when you double click on send mail task).

    But before that, if you want to check whether your email, port number and the server are working well, then you can check that by going to SQL Server Management Studio.

    Connect to the database engine and the server that you are using. Then in the Object Explorer(on the left hand side), expand Management tab. Under Management, expand SQL Server Logs and you will find Database Email. In the Database Email, right click and select Configure Database Email. Then you will be getting a Database Mail Configuration Wizard, which you can use to configure and check your connection. You can even set port number here. Later on, if you wish to modify, then you can even do that by selecting Manage Database Mail accounts and Profiles in the Select Configuration Task of this wizard. It is user-friendly, so I guess you should not be having any issue.

    After you complete the work on this wizard, then again right click, Database email in the Object Explorer and click on Send Test Email. Over here, you can specify the To address and the Subject and try sending a test e-mail. If this is working fine, the you should not have any issue in SSIS Send Mail Task also. If there is an issue here, then you will need to resolve it first.

    Hope this reply helps you somewhat.

    Good Luck.

    Thanks,

    notes4we

  • September 16, 2008 at 4:14 am

    #872229

    Assuming you're doing this in SSIS, drag a Send Mail Task onto the Control Flow. Double click on it to open the editor and select Mail in the left pane. Under SmtpConnection, select New Connection. This will then allow you to add the smtp server.

  • September 17, 2008 at 4:19 am

    #872904

    Database mail i m using from so long and it is working fine.

    but when i try to use Send Mail Task of SSIS.

    It ends with following error on execution:

    "Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "Failure sending mail."."

    i think i have set the necessory properties properly but still it gives error.

  • September 17, 2008 at 6:27 am

    #872950

    Did you yerify your server IP address and that you have permissions on the server? Also make sure you have the from: field set in the task.

  • September 17, 2008 at 11:00 pm

    #873385

    Thanks all for the reply...

    while configuring the account on my sql server i have set the "server name" in SMTP properties as

    from there it works properly. and i have scheduled several jobs for sending mails.(sp_send_dbmail i hv used), which works very nicely.

    now i m trying for SSIS and in send mail task i have set properties as-

    SMTP connection => new connection => mail.mydomain.com

    From: aaaaa@mydomain.com

    To: aaaaa@mydomain.com

    Subject: Test Mail

    Message source: Test Mail

    SSIS i m trying on the same server.

    is there anything i m missing?

    if this works then i have lot of jobs to be scheduled using SSIS.

    thanks in advance

  • March 31, 2009 at 11:47 am

    #969317

    Most probably your antivirus program is blocking the email out port (25).

  • April 1, 2009 at 12:12 pm

    #970142

    are you getting any errors!! or simply mail is not sending??

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • December 17, 2009 at 7:43 am

    #1093501

    I know this is an old thread, but I'm having the same issue. Is there any way I can specify which email credentials for the SSIS Send Mail Task to use? If I use "Windows Authentication" and run the task from my machine, it works, but as soon as I try to automate the task, it won't work.

  • December 17, 2009 at 7:58 am

    #1093511

    Did you give the account that runs the job permissions on the smtp server?

  • December 17, 2009 at 8:34 am

    #1093534

    Yeah, that was my guess. I didn't want to mention it because I thought it might've been way off and I'd sound like a complete idiot. 😉 I will let you know in a bit after I try to add permission to that account.

    One other thing, on the Send Mail Task Editor - does the FROM field have to be a valid email address on that SMTP server? I mean, when I receive the email from the task, it shows my email address instead of a name in the FROM field. Is there any way around that? I dont' want it to show an email address, I want it to show something else.

  • December 17, 2009 at 8:58 am

    #1093554

    No it does not. I use "donotreply@xxx.com" and it sends fine.

  • August 25, 2010 at 7:15 am

    #1211613

    hey me too having the same problem .

    An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 216.166.12.44:25".

    this is my error msg . how can i proceed ??

    thanks

    G

  • August 25, 2010 at 9:17 am

    #1211711

    ryan213 (12/17/2009)

    I know this is an old thread, but I'm having the same issue. Is there any way I can specify which email credentials for the SSIS Send Mail Task to use? If I use "Windows Authentication" and run the task from my machine, it works, but as soon as I try to automate the task, it won't work.

    Ryan,

    This task does not support providing credentials. to do that you will have to take a different approach, using a Script Task.

    Here is some starter code for you:

    Dim _oSMTPClient As New SmtpClient("smtpmailhost.yourcompany.com")

    Dim _oMailMsg As New MailMessage()

    _oMailMsg.From = New MailAddress("someemailaddress@yourcompany.com")

    _oMailMsg.To.Add("someotheremailaddress@yourcompany.com")

    _oMailMsg.Subject = "The Subject"

    _oMailMsg.Body = "Message Body"

    _oSMTPClient.Send(_oMailMsg)

    Don't forget:

    Imports System.Net.Mail

    CEWII

  • September 14, 2010 at 8:49 pm

    #1221104

    Hello all,

    I created a package to extract data from the database, put the data in an Excel file, zip it, and then email the file. I ran into a roadblock when my stmp server required authentication on a secure port (not port 25).

    I found a work around to send email in an SSIS package if your smtp server requires authentication. You can also send an HTML email.

    You must have Database Mail already set up with the proper authentication. If you don’t, there are many articles that show you how to set up database mail. I will tell you that if you are using port 465 on your stmp server, for some reason, you need to specify port 587 in database mail.

    In your SSIS package, simply add an “Execute SQL Task” step to your package.

    In the SQLStatement entry box under the General Tab type:

    EXEC msdb.dbo.sp_send_dbmail

    @recipients='theemail@server.net',

    @profile_name = 'Your Database Mail Profile Name’,

    @subject = 'Your Subject',

    @body = 'your body,

    @file_attachments='U:\yourfilename.zip;

    Database mail only allows a 1MB attachment as a default but this can be changed in “View or change system parameters”, “Maximum File Size (Bytes)”.

    I hope this helps.

    Jim

Viewing 15 posts - 1 through 15 (of 17 total)

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