May 14, 2018 at 12:10 pm
Once a month on the first of the month I need to burst an SSRS report out to ~200 email addresses. This month was the first time I've attempted it. The first run processed 185 emails but had 86 failures. When I look at the log it appears that my data source (which an ODBC connection to a SAP HANA in memory DB) is simply failing. It's not available or times out, or something.
In order to re-try the failures, I pulled the log file into excel which shows me the success emails, and rebuild my data driven subscription filtering those email addresses out. I've done this a number of times now and I'm getting there, but obviously this is quite painful.
I was thinking of trying to use Caching to fix the issue, but I'm not sure how that is going to help me since I don't have a good way to know which of the 200ish paramaters each month successfully built a cache or not. Any advice would be appreciated. I don't know if there is someway to automatically retry a failure or at least log who successfully received an email so that I don't email the same person twice.
May 15, 2018 at 9:41 am
Is it possible to create a distro list on your email system with all the addresses who need this report and use it in your subscription? Fairly sure that this will result in your report only running once instead of 200 times
May 15, 2018 at 10:05 am
No, it's a data driven subscription because each recipient needs their own copy of the report (filtered to their ID). Due to security reasons, we can't allow them to get other copies.
Caching did help ultimately, but I think that's a poor fix. By using the Null Delivery provider I was able to schedule the same data driven data set to run every 5 minutes until the cache for every parameter combination that I cared about worked. It took about 4 runs before my remaining errors all ran successfully and built the cache. Then I was able to finally run the email subscription which leveraged that cache. http://blog.softartisans.com/2009/12/22/ssrs-improve-performance-by-automating-report-caching-using-null-delivery-provider/
It seems like there must be some way to fix the ODBC connection though, or to tell SSRS to not hammer it so hard when the subscription runs. It seems to be purely a throughput issue. All 186 emails eventually did run, it just took a lot of retries.
May 15, 2018 at 10:36 am
Bad_Karma - Tuesday, May 15, 2018 10:05 AMNo, it's a data driven subscription because each recipient needs their own copy of the report (filtered to their ID). Due to security reasons, we can't allow them to get other copies.Caching did help ultimately, but I think that's a poor fix. By using the Null Delivery provider I was able to schedule the same data driven data set to run every 5 minutes until the cache for every parameter combination that I cared about worked. It took about 4 runs before my remaining errors all ran successfully and built the cache. Then I was able to finally run the email subscription which leveraged that cache. http://blog.softartisans.com/2009/12/22/ssrs-improve-performance-by-automating-report-caching-using-null-delivery-provider/
It seems like there must be some way to fix the ODBC connection though, or to tell SSRS to not hammer it so hard when the subscription runs. It seems to be purely a throughput issue. All 186 emails eventually did run, it just took a lot of retries.
You might want to check the SAP site and check some of the properties for the connection string - especially timeout and reconnect setting. The site also has some information on tracing with that drive that might help in finding any issues with the driver. Start at the main section for the driver and then there are links to the other articles on properties, tracing, etc:
Connect to SAP HANA via ODBC
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply