A reader has created a Data driven subscription for a RS report with file share option, and got “Done: 1 processed of 1 total; 1 errors.” error.
Here are the three steps I’d suggest to troubleshoot the problem.
Data-driven subscription is a powerful RS tool for automating our report subscription tasks. There are, however, very little real life instructions to follow to make it work. The error message you’ve encountered is all the help we can get from Microsoft :).
There are three types of challenges when working with data-driven subscription
- Designing a SQL table that will hold your dynamic parameters, and
- passing the correct parameters so the subscription engine does not reject them, and
- With the file share option, it adds another layer of complexity to it, that is, use the correct service account to access the shared path.
- Since you have passed the creation step, I’d assume that you have overcome the first challenge.
- Based on my experience, challenge 2 and 3 can take some time to make it right.
I’d suggest that you troubleshoot 2 and 3 separately
To make sure you have passed the correct parameters that the subscription engine would not reject, use the send mail option instead first.
Once you pass challenge 2, move one to challenge 3. If you have no rights to create service account on the file server, this can be a real challenge because you will need to convince your system admin to work closely with you.
Beyond Relational is running a month long SQL Server BI quiz. Tomorrow, 6/11, my quiz will be open to accept answers from readers.
1st Prize – Apple iPad!
It is not a surprise that the quiz from me is about Reporting Services Subscription.
Question Title: Mass Mailing Your Report
Context: You have a SSRS report that takes 3 parameters
Parameter 1: Begin Date (you have set it to a default value which is the Beginning of the Reporting Month)
Parameter 2: End Date (you have set it to a default value which is the current date)
Parameter 3: Decision Type (it is a multi-valued parameter, you have set it to "Select All" from a default list that comes from a dataset query.
(Possible Decision Types are: Approval, Decline, Info Needed, First Level Management Review, Other)
Challenge: Business has asked you to send the report in an Email as an Excel attachment.
1. The Email needs to have event-driven behavior (as soon as the data is updated daily)
2. The report will only have data from the previous day (not month-to-date)
3. When the Decision Type is Approval, send the email to MgrApproval@Company.com
When the Decision Type is other than Approval, send the email to MgrOther@Company.com
Quiz: please implement a solution on Microsoft BI platform, with the following considerations
1. Do not modify the current SSRS report
2. The solution needs to be data-driven and can be automated
3. Constant manual intervention is not acceptable
Need a hint? Please check out my recent blogs on RS Subscription and research on how to implement a simple Data-driven Subscription.
You can submit answers to this question after 11 Jun 2011. The question will be open for 30 days.
I had several blog posts on how to manage your SSRS subscriptions. Here is a summary of the different ways I’ve been using Reporting Services out-of-box subscription features (no programming/expressions involved).
1. Simple, but mostly “static” way. You can find the New Subscription button from the Subscription tab on the Reporting Server.
- 2. Even with this simple/static feature, you can create powerful report delivery options, i.e. E-Mail or Windows File Share.
- 3. With the Windows File Share delivery option, you can send your reports to a shared location, and eventually to a SharePoint site.
- 4. Still simple, but dynamic (relatively speaking) way. This is done through Data-driven Subscription.
5. Through the data-driven subscription, you can achieve the “dynamic” features you’ve always wanted. AND through some simple steps without any scripting. The dynamic features you’ve always wanted include: dynamic report parameters, dynamic file names (when sending to a shared location), dynamic email distribution list, etc.
6. Through the data-driven subscription, you can also achieve the “mass mailing” feature (only if you have such desire) with setting up only one data-driven subscription.
7. I made it sound like data-driven subscription can solve all the problems in the world. The truth is it cannot. Without some scripting, one of the things it cannot do is handle the cascading parameters. Some experienced developers will suggest scripting with the SOAP web services exposed by RS to make it work. By far, you should know my preferred way, SQL. I have resolved the issue by paring up SQL procedures with data-driven subscription to achieve the ultimate flexibility.
8. With both the simple Subscription and Data-driven Subscription, you kick star the delivery by a fixed schedule, or make it event-driven.
In my previous post, SSRS #47 – Manage subscriptions dynamically, I’ve promised to blog about the Data Driven Subscription in the future. I will. I promise.
In my previous post, SSRS #43 – Problem with undocumented SQL procedure AddEvent when kicking off a Timed Subscription, I mentioned that the report link from the Subscription is not a valid link. Our architecture team informed me that using undocumented procedure AddEvent probably is not the best way to kick start a Reporting Services Subscription. The recommendation is to use the exposed web services provided by the reporting Services within my SSIS package to kick start the Subscription.
Well so far I couldn’t get it to work because I couldn’t even get the ReportingService class to work in either a Script Task or the Web Service Task. There are many articles talking about how to get the ReportingService class reference to work in a SSIS package. This one seems to have a very detailed instruction on how to make it work, Consuming the Reporting Services web service inside SSIS. Unfortunately so far it’s not working for me because I could not find the wsdl.exe file locally
According to this article on MSDN, Web Services Description Language Tool (Wsdl.exe), the file wsdl.exe is part of the.NET Framework 2.0 Software Development Kit. Once installed, the tool (wsdl.exe) can be found in the folder: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. Since I don’t have the SDK Kit installed, no solution for me so far. I don’t want to install it right now either, since this is not on my priority list.
My priority regarding managing Subscriptions on Reporting Services
What is on my priority list:
1) Continue to use the AddEvent procedure to kick start the Subscription, but do not hard code the SubscriptionID. This should be easy to achieve. See below.
2) I need to switch from Subscription to Data Driven Subscription.
Reasons for the above priority
If you have created a Timed Subscription on Reporting Services, you must have noticed how “easy” it is to select values for your report parameters. You must have also noticed a few times when the automatic emails did not fire off. You scratched your head, and couldn’t figure out why. Later you will find out that it’s because the values you picked on the Subscription are no longer valid, or you have added more report parameters, or have deleted a few parameters. In another word, the Subscription you have created is no longer valid.
Retrieve SubscriptionID Dynamically
I need to do the first thing on the list if I am going to continue to use the Subscription, instead of the Data Driven Subscription, so at least I have a valid SubscriptionID if I changed something on the Subscription.
This is a relatively easy task. I would create two Execute SQL Tasks, and a package variable varSubscriptionID.
The variable varSubscriptionID will be populated in the first Execute SQL Tasks with a query like this:
In the second Execute SQL Tasks , I will use this to kick start the Subscription by passing the variable varSubscriptionID to it (represented by the question mark).
Data Driven Subscription
There are many articles about why we need to use Data Driven Subscription VS. just the plan Subscription extension on Reporting Services. Most bloggers have done a great job. I don’t need to report them here. To me, I need to use it because I do not want to hard code the values for my report parameters. I found out what would happen if the values are no longer valid. I also want to be able to customize the subscriptions for different business groups. The idea is the same as my first priority, I want to manage the subscriptions in a dynamic way.
I will blog about the Data Driven Subscription in the future.