Programmatically Creating And Executing Data Delivery Extensions In SSRS

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...