Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ssis Send Mail Task

Posted on 2010-09-21
11
2,546 Views
Last Modified: 2013-11-10
I have a package with 4 Execute sql tasks. I clicked eah one individually, then clicked event handlers and setup a Send Mail Task (for each one).using the OnError  event handler. To test it, I renamed one of the tables so it would get an error. It got the error, but did not send the email.    sendMail.docx
0
Comment
Question by:qbjgqbjg
  • 4
  • 4
  • 2
  • +1
11 Comments
 

Author Comment

by:qbjgqbjg
ID: 33728876
[Send Mail Task] Error: 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: No connection could be made because the target machine actively refused it 10.100.2.12:25".

This is the error.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33728998
the error is in SEND MAIL TASK not execute sql task.
I think ssis can not connect to smtp connection. could you re-check it ?
0
 

Author Comment

by:qbjgqbjg
ID: 33729106
I am checking with the person in IT that manages the servers. He gave me the Smtp connection.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 30

Expert Comment

by:Reza Rad
ID: 33731746
could you connect to smtp server with outlook ?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33733502
Dear qbjgqbjg:


the normal SSIS send mail task is useless, it doesnt allow you to specify the credintials of the sender and this way the user that is running the SSIS package should always has a valid email and authenticated and authorised to send, and when you are running the ssis package using a sql job, the agent has to be run by an authorised domain user.

so that is why you need to use a SSIS script task As mentioned by MohammedU and below is a simple code

    Public Sub Main()

        Using reader As New StreamReader(response.GetResponseStream())
            Dim myAddress = "emailaddress@yourdomain.com"

            Dim myEmail As New MailMessage(myAddress, myAddress)
            myEmail.Subject = "From SSIS using HTML"
            myEmail.IsBodyHtml = True   ' this value should be true
            myEmail.Body = reader.ReadToEnd()

            Dim smtp As New SmtpClient("smtp.yoursmtp.com", port) ' you need to mention the smtp port
            smtp.Credentials = New NetworkCredential(myAddress, "yourpassword")
            smtp.EnableSsl = True
            smtp.Send(myEmail)
        End Using

        Dts.TaskResult = ScriptResults.Success
    End Sub


check the following article for more details

http://www.mssqltips.com/tip.asp?tip=1753
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33733615
@AmmarR:
Send Mail Task is not useless,
you can set sender configuration there
also you can set To or CC or BCC in configuration there.
but this does not means that this is static configuration.
you can simply use expression and there are ToLine, CCLine , BCCLine properties there which allows you to send mail to any address dynamically.

Am I understand your post correctly? If you mean something else explain it more.

0
 

Author Comment

by:qbjgqbjg
ID: 33734875
I have never used VB at all. I still am waiting for our server guy to get back to me. I think the problem is with the authentication. We use Windows authenication on a lot of things but there is a firewall that may be affecting it. I might have to learn enough VB to use the script.
0
 
LVL 3

Expert Comment

by:novaspoonman
ID: 33735124
See if your mail admin will allow relaying from that server without SMTP authentication, if you want to avoid code. It's not that most secure solution, but it is an alternative.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33736383
you can simply check the connection with outlook,
try to connect to server with outlook and send a dummy mail with credential provided
0
 
LVL 15

Accepted Solution

by:
AmmarR earned 500 total points
ID: 33736652
sorry @reza_rad: and guys, i didnt mean to say its useless in that sense, i meant its very limited and doenst give you all the necessary options to setup email client.

by the way qbjgqbjg:, if you SQL Server Integration Services is running under a valid domain account, which has access to send email, then you can simply run the send email task without all these configurations and problems.

check image below, see my account here is running under Network system, yours should be under a valid domain account

 
sql-server-ssis.jpg
0
 

Author Closing Comment

by:qbjgqbjg
ID: 33737311
Thanks for all of the suggestions.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question