Hi,
I am working on a subscription reporting system with SQL Server RS reports
that report from Oracle 9i warehouse with Oracle stored procedures used for
data processing. Users select report parameters through a custom UI, the
parameters are held on a table in SQL Server then outside of normal business
hours the reports are run by a programmatic rendering process (C#). I am
trying to find information regarding control of the report processing for the
system. I need to decide whether to run jobs sequentially or concurrently and
if concurrently then how to set the maximum number of concurrent jobs
allowed, how to control queue time and the number of jobs allowed in a queue
and how to control the number of connections to the Oracle warehouse. Does
anyone have any information regarding best practice for configuration
settings for subscription systems? For example if I have 200 reports to run
each night and each report takes 10 minutes for the data processing component
and I am only allowed 10 open connections to my warehouse then would the
approach to be submit all 200 reports to Reporting services at once and have
the RS configuration settings handle the processing or should I write my own
job monitoring application to limit the number of concurrent jobs being sent
to reporting Services?
I've looked at Data driven subscriptions and snap shots, the report
parameter UI seems the only way to go as I need to pass different oracle
credentials to the reports depending upon the user who subscribes to a report. Any help appreciated.