Programmatically determining if a data driven subscription is running

Hi,

Quick question: how do I determine programmatically if a data driven subscription is currently running?

More info:

I’m writing a web application which allows the user to kick off an existing data driven subscription (reporting services 2000), which runs from a table with parameters, paths, etc which the user has populated.

The subscription can take several minutes to run, during which time I need to prevent other users from attempting to run the subscription or alter data on the table driving the subscription.

All I’ve found in the docs so far is

1. The ActiveState on the subscription.

This seems to have more to do with weather or not it can run than if it is running.

2. The status of the subscription.

This seems to only return “done: {0} of {1} with {2} errors” Parsing this is likely to be too flaky to be acceptable.

I really need to move on this as soon as possible, any help is appreciated.

Thanks

If you look at the ReportServer.dbo.Subscriptions table, isn't there a LastRunTime column? Will that get you what you want?|||

I should have clarified that I want to obtain the information through soap access. I belive I can actually obtain that value through soap access, but I believe the lastruntime states the time at which the process was kicked off, not the time at which it finished. From the reading i've been doing I'm staring to think that this is a very difficult problem to solve. If I come up with a solution more interesting than parsing the status field I'll post the details.

Thanks anyway

|||

Ok,

Looks like I was wrong in my original post, the status returned from the subscription will say either Done: blah of blah or Processing Blah of Blah. So as an imperfect (and quick) solution I can and will parse that. I'm still not certain if i'm missing something and everything I need is already available through the soap api, but anyway... If I had more time and we weren't switching to 2005 in a few months I'd probably write a custom delivery extension that fires off an event to a web service or something to that effect.

Programmatically creating and executing data driven subscriptions 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...

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

Programmatically creating and executing data driven subscriptions 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...

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

Programmatically creating and executing data driven subscriptions 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...

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

Programmatically create a Data Driven Subscription

Hi,
I have seen the examples of creating a Subscription via code.
I actually wish to create a data driven subscription. Can someone point me
to an example of this or post me an example?
many thanks
MattFound an example.
Have question on this though, how to set the data source to a shared data
source rather than a literal?
I assume you need to use DataSourceDefinitionOrReference rather than
dataSourceDefinition. How do you populate this?
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:%23vHHGkbeEHA.3616@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have seen the examples of creating a Subscription via code.
> I actually wish to create a data driven subscription. Can someone point
me
> to an example of this or post me an example?
> many thanks
> Matt
>|||Got it. Sorry pls ignore
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uAx8KaceEHA.1424@.tk2msftngp13.phx.gbl...
> Found an example.
> Have question on this though, how to set the data source to a shared data
> source rather than a literal?
> I assume you need to use DataSourceDefinitionOrReference rather than
> dataSourceDefinition. How do you populate this?
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:%23vHHGkbeEHA.3616@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have seen the examples of creating a Subscription via code.
> >
> > I actually wish to create a data driven subscription. Can someone point
> me
> > to an example of this or post me an example?
> >
> > many thanks
> >
> > Matt
> >
> >
>