Proposed Architecture

I would be most grateful for any comments regarding the following
proposed datawarehouse architecture:
1. Backroom Box
This is where all the ETL work will be done.
Daily & monthly imports of data from a number of primary data sources.
Lots of surrogate key transformations.
- Instance of SQL Server 2000 (Enterprise) with the Data Staging Area
database.
2. Frontroom Box
This is where the star-schema, cubes & web application (XLCubed Web)
will be.
- Instance of SQL Server (Enterprise) with the Datawarehouse database.
- Instance of Analysis Services.
- Instance of XLCubed Web.
All the fact tables in the star-schema will probably total less than
50Gb.
The updates (whether daily or monthly) are likely to be less than 1Gb.
There will be max. 50 concurrent users across 4 sites.
There will be about 20 cubes (most will be small; i.e. less than 1Gb)
The biggest will have a fact table of about 2Gb (less than 20m rows &
max. 60 columns).
There will be no distinct counts.
There will be no virtual cubes.
The bigger cubes will have many measures (about 40).
With about another 20 calculated measures/members.
The biggest cube will have about 12 dimensions (all having 2 or 3
levels).
Some dimensions will be parent-child.
Some of the smaller cubes will have writeback ability which will be
done through XLCubed Excel version.
Does it make any difference where SQL Agent is run from?
Backroom instance makes most sense?
Please advise on what servers to buy.
Processors, RAM, disk, etc.
What matters most for the backroom server? Processor speed?
What matters most for the frontroom server? RAM?
2*3Ghz CPU, 4Gb RAM, 250Gb disk?
Good estimate! This is quite normal to have two dedicated boxes for
Staging and Production. Now, you need to be careful about update rate.
If it is 1 GB per day then you may need more space or need to come up
with horizontal partition and clustering but 1 GB per month is alright
although you may need partition and clustering down the track.
Cubes are seems OK. Write back is really depends on your application
but as you now, you need to keep track of your write back values which
means merge them back to fact table with DTS when write back activity
happens. Quite normal for Budgeting and Forecasting cubes and should be
OK.
SQL Agent can be run from anywhere but I think backroom box makes more
sense, please check with your system admin people.
Regarding buying server, have you actually done any research based on
price and performance yet? If you not then I strongly suggest that you
consider HP Itanium Box. SQL Server needs at least 4 CPU so that it can
share resources and at least 4 GB of RAM which you planned anyway.
Itanium is a 64 bit box, specially built for Windows 2003 server and
SQL Server. Also, IBM blade is OK but very expensive. I found Itanium
is more practical and value for money. It will be around 20-25 thousand
US Dollars these days.
Front room box should have same or more in it since it be will accessed
all the time.
Your overall estimate is quite legitimate.
Hope this helps!
Regards.
|||In my case, I prefer to use 1 big server instead of 2 dedicated.
Why?
simple, generally the loading process run during the night when the user are
not here, second a big server load data more quickly, specially if there is
a lot of and complex transformation in the staging.
also, a big server provide better response time for the end user.
another advantage if there is any data quality issue I can do some job
during working hour in a shorter time due to the better performance.
also when there are some cubes between the database and the user, playing in
the database don't impact the users.
using enterprise edition of Windows 2003 you have a tool to manage the
resources on the server (CPU, memory). so you can insure enough resources
available for the end users during the day and maximize the staging part
during the night.
Itanium CPU are good, but Dual Core CPU are good too!
regarding the license model of SQL server (per processor), dual core is
interesting (you pay for 1 CPU but you have 2 CPUs...)
I have found the AMD opteron CPU very efficient with SQL Server.
focus only on 64bits or x64 systems with at least 16Gb of memory.
Also try to use SAS drives, you can plug SATA drives on these controllers,
so you can save historical data on low price SATA drives.
"Dip" <soumyadip.bhattacharya@.gmail.com> wrote in message
news:1140392471.587633.100670@.g44g2000cwa.googlegr oups.com...
> Good estimate! This is quite normal to have two dedicated boxes for
> Staging and Production. Now, you need to be careful about update rate.
> If it is 1 GB per day then you may need more space or need to come up
> with horizontal partition and clustering but 1 GB per month is alright
> although you may need partition and clustering down the track.
> Cubes are seems OK. Write back is really depends on your application
> but as you now, you need to keep track of your write back values which
> means merge them back to fact table with DTS when write back activity
> happens. Quite normal for Budgeting and Forecasting cubes and should be
> OK.
> SQL Agent can be run from anywhere but I think backroom box makes more
> sense, please check with your system admin people.
> Regarding buying server, have you actually done any research based on
> price and performance yet? If you not then I strongly suggest that you
> consider HP Itanium Box. SQL Server needs at least 4 CPU so that it can
> share resources and at least 4 GB of RAM which you planned anyway.
> Itanium is a 64 bit box, specially built for Windows 2003 server and
> SQL Server. Also, IBM blade is OK but very expensive. I found Itanium
> is more practical and value for money. It will be around 20-25 thousand
> US Dollars these days.
> Front room box should have same or more in it since it be will accessed
> all the time.
> Your overall estimate is quite legitimate.
> Hope this helps!
> Regards.
>
sql

Proposed Architecture

I would be most grateful for any comments regarding the following
proposed datawarehouse architecture:
1. Backroom Box
This is where all the ETL work will be done.
Daily & monthly imports of data from a number of primary data sources.
Lots of surrogate key transformations.
- Instance of SQL Server 2000 (Enterprise) with the Data Staging Area
database.
2. Frontroom Box
This is where the star-schema, cubes & web application (XLCubed Web)
will be.
- Instance of SQL Server (Enterprise) with the Datawarehouse database.
- Instance of Analysis Services.
- Instance of XLCubed Web.
All the fact tables in the star-schema will probably total less than
50Gb.
The updates (whether daily or monthly) are likely to be less than 1Gb.
There will be max. 50 concurrent users across 4 sites.
There will be about 20 cubes (most will be small; i.e. less than 1Gb)
The biggest will have a fact table of about 2Gb (less than 20m rows &
max. 60 columns).
There will be no distinct counts.
There will be no virtual cubes.
The bigger cubes will have many measures (about 40).
With about another 20 calculated measures/members.
The biggest cube will have about 12 dimensions (all having 2 or 3
levels).
Some dimensions will be parent-child.
Some of the smaller cubes will have writeback ability which will be
done through XLCubed Excel version.
Does it make any difference where SQL Agent is run from?
Backroom instance makes most sense?
Please advise on what servers to buy.
Processors, RAM, disk, etc.
What matters most for the backroom server? Processor speed?
What matters most for the frontroom server? RAM?
2*3Ghz CPU, 4Gb RAM, 250Gb disk?Good estimate! This is quite normal to have two dedicated boxes for
Staging and Production. Now, you need to be careful about update rate.
If it is 1 GB per day then you may need more space or need to come up
with horizontal partition and clustering but 1 GB per month is alright
although you may need partition and clustering down the track.
Cubes are seems OK. Write back is really depends on your application
but as you now, you need to keep track of your write back values which
means merge them back to fact table with DTS when write back activity
happens. Quite normal for Budgeting and Forecasting cubes and should be
OK.
SQL Agent can be run from anywhere but I think backroom box makes more
sense, please check with your system admin people.
Regarding buying server, have you actually done any research based on
price and performance yet? If you not then I strongly suggest that you
consider HP Itanium Box. SQL Server needs at least 4 CPU so that it can
share resources and at least 4 GB of RAM which you planned anyway.
Itanium is a 64 bit box, specially built for Windows 2003 server and
SQL Server. Also, IBM blade is OK but very expensive. I found Itanium
is more practical and value for money. It will be around 20-25 thousand
US Dollars these days.
Front room box should have same or more in it since it be will accessed
all the time.
Your overall estimate is quite legitimate.
Hope this helps!
Regards.|||In my case, I prefer to use 1 big server instead of 2 dedicated.
Why?
simple, generally the loading process run during the night when the user are
not here, second a big server load data more quickly, specially if there is
a lot of and complex transformation in the staging.
also, a big server provide better response time for the end user.
another advantage if there is any data quality issue I can do some job
during working hour in a shorter time due to the better performance.
also when there are some cubes between the database and the user, playing in
the database don't impact the users.
using enterprise edition of Windows 2003 you have a tool to manage the
resources on the server (CPU, memory). so you can insure enough resources
available for the end users during the day and maximize the staging part
during the night.
Itanium CPU are good, but Dual Core CPU are good too!
regarding the license model of SQL server (per processor), dual core is
interesting (you pay for 1 CPU but you have 2 CPUs...)
I have found the AMD opteron CPU very efficient with SQL Server.
focus only on 64bits or x64 systems with at least 16Gb of memory.
Also try to use SAS drives, you can plug SATA drives on these controllers,
so you can save historical data on low price SATA drives.
"Dip" <soumyadip.bhattacharya@.gmail.com> wrote in message
news:1140392471.587633.100670@.g44g2000cwa.googlegroups.com...
> Good estimate! This is quite normal to have two dedicated boxes for
> Staging and Production. Now, you need to be careful about update rate.
> If it is 1 GB per day then you may need more space or need to come up
> with horizontal partition and clustering but 1 GB per month is alright
> although you may need partition and clustering down the track.
> Cubes are seems OK. Write back is really depends on your application
> but as you now, you need to keep track of your write back values which
> means merge them back to fact table with DTS when write back activity
> happens. Quite normal for Budgeting and Forecasting cubes and should be
> OK.
> SQL Agent can be run from anywhere but I think backroom box makes more
> sense, please check with your system admin people.
> Regarding buying server, have you actually done any research based on
> price and performance yet? If you not then I strongly suggest that you
> consider HP Itanium Box. SQL Server needs at least 4 CPU so that it can
> share resources and at least 4 GB of RAM which you planned anyway.
> Itanium is a 64 bit box, specially built for Windows 2003 server and
> SQL Server. Also, IBM blade is OK but very expensive. I found Itanium
> is more practical and value for money. It will be around 20-25 thousand
> US Dollars these days.
> Front room box should have same or more in it since it be will accessed
> all the time.
> Your overall estimate is quite legitimate.
> Hope this helps!
> Regards.
>

Programmatically iterating tasks/components in the data flow portion of a package.

HI All,

In several threads there has been discussion regarding adding connection managers to a package's data flow, etc. My challenge is that I have a large solution that contains many packages, and I need to change the connection manager linked to the data flow in all of the packages. When the solution was initially designed, data sources were used, and it has become a tedious maintenance issue to keep those in sync. We want to use a standard OLEDB connection manager, but adding a connection manager to each package and editing the corresponding data flow tasks in each package to use that new connection manager is a daunting task. I've coded a .Net module to access the packages, remove the old connection manager (data source) and add the new OLEDB data source. However, as I traverse the objects in the package hierarchy, when I come to the data flow object, the innerobject is not a dts object, but rather a _com object.. I can't seem to find any documentation/examples as to how to iterate the tasks within a data flow and change the connection manager. If you have any information, that would be quite helpful. If you reply with a code sample, if you would be so kind as to relate it to one of the sample packages provided with SSIS so I can run it, that would be great.

Thank you.

Steve.

Hiya Steve,

I know we discussed this offline but just for anyone else's benefit.... the following may help:

Iterate over a package programatically
http://blogs.conchango.com/jamiethomson/archive/2007/03/06/SSIS_3A00_-Iterate-over-a-package-programatically.aspx

-Jamie

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

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