Hi All,
I have been stuck with this problem since few days, need help regarding the same. I am enclosing the problem description and possible solutions that I have found.
Can anyone please help me out here?
Thanks and regards,
Virat
Problem Description:
I have a requirement for which I have created a data driven subscription in
SQL Server 2005, the whole thing works like this:
I have a report on Report Server which executes a stored procedure to get
its parameters; then it calls another stored procedure to get data for the
report; then it creates the report and copies it to a file share. This is
done using data driven subscription and the time set for repeating this
process is 5 minutes.
You can assume that following are working fine:
1. I have deployed the report on the Report Manager (Uploaded the report,
created a data source, linked the report to data source) - manually, the
report works fine.
2. Created a data driven subscription.
3. The data driven subscription calls a stored procedure, say
GetReportParameters which returns all the parameters required for the report
to execute.
4. The Report Manager executes the report by calling a stored procedure, say
GetReportData with the parameters provided by GetReportParameters stored
procedure; after it has generated the report file (PDF) is copied to a file
share.
For each row that GetReportParameters stored procedure returns a report (PDF
file) will be created and copied to file share.
Now, my question is
1. How to I get a notification that this file was successfully created
or an error occurred?
2. The only message that reporting service shows on 'Report Manager >
My Subscriptions' is something like "Done: 5 processed of 10 total; 2
errors."
How do I find out which record was processed successfully and which ones
resulted in an error?
Based on above results (success or failure), I have to perform further
operations.
Solutions or Work around that I have found:
1. Create a windows service which will monitor the file share folder
and look for the file name (each record has a unique file name) for the
reports that were picked up for PDF creation. If the file is not found, this
service will report an error. Now, there's a glitch there; if a report takes
very long time to execute it will also be reported as error (i.e. when this
service checks for the PDF file, the report was currently being generated).
So, I can't go with this solution.
2. I have also looked at following tables on ReportServer database:
a. Catalog - information regarding all the reports, folders, data
source information, etc.
b. Subscriptions - all the subscriptions information.
c. ExecutionLog - information regarding execution of the subscriptions
and the also manual execution of reports.
d. Notifications - information regarding the errors that occurred
during subscription execution.
For this solution, I was thinking of doing a windows service which will
monitor these tables and do further operations as required.
This looks like most feasible solution so far.
3. Third option is to look at DeliveryExtensions but in that case I
will have to manually call SSRS APIs and will have to manage report
invocation and subscription information. What is your opinion on this?
My environment details:
Windows XP SP2
SQL Server 2005
Reporting Services 2005
Please let me know if I am missing something somewhere...
I’ve added some comments for all solutions you proposed:
1. Create a windows service …
[Igor] SSRS does not provide information regarding which particular subscription instances (resulted from a data-driven subscription) failed or succeeded. In case of file share delivery monitoring the share may be the only way to get this information until a custom file share delivery extension is implemented.
2. I have also looked at following tables on ReportServer database …
[Igor] Note that using ReportServer database directly is not supported.
Data driven status always is in the format ‘Done: {0} processed of {1} total; {2} errors’. More detailed information is not available.
3. Third option is to look at DeliveryExtensions …
[Igor] I would rank this as a best approach for your needs. Implementing custom delivery extension is not as hard. You just need to implement the IDeliveryExtension interface with two methods and one property. You can start from here http://msdn2.microsoft.com/en-US/library/ms154481.aspx. Deliver method in your extension will be called per every subscription instance created out of your data-driven subscription, and so you will have a complete control over delivering/processing success or failure information.
- Igor
|||Thanks a lot!
I will look into delivery extensions, do you have any code samples for the same?
Thanks again!
|||Printer delivery is the only available delivery extension sample. It implements IDeliveryExtension – should be good enough to start from.
Good luck,
-Igor
|||Thanks! I implemented my own data delivery extension and it worked.|||hay virat hi,
I need the same functianality can u plz send me your implemented Delivery Extension project
thanks in advance
No comments:
Post a Comment