SSRS 2008 - Large Number of e-mail Recipients - Data Driven Subscription?

Question

Hi all,

Is there a recommended method for dealing with large numbers of e-mail recipients for SSRS reports (i.e. >100)?

I have tried a data driven subscription to bring in the e-mail addresses from a data table, but this resulted in an error being logged of "The maximum number of concurrent connections has exceeded a limit, closing transaction channel" and the subscription failing for most of the members.

I have no real need for the 100 e-mails to be processed separately, a single e-mail with 100 recipients would work fine, but I would like to keep management of the mailing list members as simple as possible. If I just copied all the addresses into the "To" field it would be very difficult to manage people leaving and joining the group.

The only other solution I can think of would be to use a data driven subscription to create a semicolon delimited string from the database fields and use this as the "To" parameter. If I were to try this, is there a maximum number of characters that the e-mail fields can contain?

Answer 1

I ran into a very similar problem recently.  A configuration change was made on the email server to workaround the problem.  The admin of that server created a "new dedicated Receive Connector" that permitted the SSRS report server to send out more emails than the Exchange server's default settings (20 emails per IP).

Here's the behavior I was able to reproduce prior to that workaround:

• Attempt to send 100 emails
• 20 are sent on the first try, 80 fail
• 20 sent on second try, 60 fail
• 20 sent on third and final try, 20 fail
• The emails sent are random.  The query that populated my test data-driven subscription is ordered, but that has no impact on the order of the sent emails.

The number of retries attempted and the time between retries are set in the SSRS config file.  On each email attempt, SSRS tries to open a connection with the SMTP server and that server was only permitting 20 connections per IP address.

Disclaimer: I’m not an SSRS/SMTP setup or configuration expert so there may be additional options to consider.

---

website: jontav.com | Blog

Answer 2

Thank you for your help, Jon.

As it turned out, I found an alternative solution which seems to be working.

At the e-mail server end, I was having the problem described here, that a friendly Exchange server admin was able to sort out for me.

In terms of the huge number of connections, that I didn't really need since all the e-mails contained the same report, I discovered through trial and error that an SSRS data driven subscription will accept a semicolon delimited string from a query as the "to" field.

I was able to manage distribution by having my select query concatenate all the e-mail addresses I needed into one string, then fire off a single e-mail to all of them.

I'm posting this in case it helps anyone else who is having the same problem.

Answer 3

An Email Distribution List worked for me.

However, I am having some issues with one distribution list, not sure if it's because the name was to long. Anyone knows if there is a limit on the number of characters for an email address?