0

I've got an SSIS package which includes a "Send Mail Task" event, but it's not sending any mail. I didn't set this package up, and my experience of SSIS extend purely to ad-hoc jobs in SQL Server and I'm having trouble working out why it's not sending mail.

No error is logged: the package is set to report to the windows event log, and the events are there but appear to have completed successfully.

The task uses a variety of variables, extracted by running select commands against the database, which under "properties" appear in the format

@[User::EmailRecipients]

I've stopped the package at a breakpoint just before the mail sends, and all these seem to be present and correct.

I've asked our network administrator to verify that the mail server has no record of attempts to mail out using the address in the "from" field, and he says not, including failed attempts at sending.

I'm at a loss as to what else I can do to try and work out why this is failing. Can anyone offer me some pointers?

EDIT - Variables

Several fields in the package property have been given "placeholder" values, such as "FromLine" being set to "[email protected]". However, under the "Expressions" field, there are the following settings

setting name      variable     

CCLine            @[User::EmailOperators]
FromLine          @[User::EmailSender]
ToLine            @[User::EmailRecipients]

These have the expected values. The MessageSource is also set under this Expressions settings, and looks something like this (I've edited the text for privacy purposes.

"ghjgjgj - " +  (DT_WSTR, 20) @[User::TotalCount] + " "
+ "\n dfgdfgfdgdfg - "  +  (DT_WSTR, 20) (@[User::TotalDistinctCount]) 
+ "\n dsfsfsdfsf  - " +  (DT_WSTR, 20)@[User::TotalPotCount] + "\n fgdgdg - " +  (DT_WSTR, 20)@[User::TotalDuplicateCount] 
+ "\n\n sdfgdgdg: \n " + @[User::CustomerText]
+ "\n\n dfgdfgdg:\n\n" + @[User::UserText] + "\n"

In addition the connection has a nonsense IP address set for SMTP Server. But again, under the expressions property, there's a value which says

ConnectionString     "SmtpServer=" + @[User::SMTPServer]  + ";UseWindowsAuthentication=False;EnableSsl=False;"

The variable SMTPServer also has the expected value when the step executes.

Improve this question
5
  • Matt, could you provide a bit more information? Perhaps we could see all the variables and the values provided to them. - Ash
    – The Dumb Radish
    Commented Jun 9, 2014 at 14:35
  • @AshShah - thanks for looking in. I've added some more details.
    – Bob Tway
    Commented Jun 9, 2014 at 15:00
  • Thanks Matt. To my eye all of that seems fine. Gievn that the MessageSource seems to be the most complex part, could you change that to something straightforward, like "Test Message" to see if that works?
    – The Dumb Radish
    Commented Jun 9, 2014 at 15:25
  • What do you mean by "DTS Send Mail Task". Is this package upgraded from ancient DTS?
    – John Saunders
    Commented Jun 9, 2014 at 15:38
  • Tried removing the expression and replacing with "an email" .. still nothing sent, although the package reports successful completion.
    – Bob Tway
    Commented Jun 9, 2014 at 15:50

1 Answer 1

Reset to default
1

Look at the account that's trying to send the email. Most probably, it does not have sufficient permissions to do so. I have dealt with this before and it ended up being that the account we were using didn't have rights. So, take a look there. Another way of testing could be using Execute SQL Task and firing the sp_send_dbmail

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.