Parameter based Report Schedule problem Problem

Here is my Case
+++++++++++++++++++++++++++++
I have a common report object residing on Reporting Service. The report
shows Monthly Activity for a Department. This report is based on a Stored
Procedure and has Department ID as a parameter.
Through an application different departments either can run this report or
create a schedule so that at the end of each month, any department can get
the report related to only its data.
How can I do that?
+++++++++++++++++++++++++
You input highly appreciatble...Hi Sue,
This is more an issue for the developers of the applications calling the
report as they will be passing the DepartmentID.
How can the app know which department it is run from? Is there a setting in
Active Directory for the user? Is there a database table somewhere with who
lives in which department? (very messy to maintain).
I'd be querying AD if possible.
"Sue" wrote:
> Here is my Case
> +++++++++++++++++++++++++++++
> I have a common report object residing on Reporting Service. The report
> shows Monthly Activity for a Department. This report is based on a Stored
> Procedure and has Department ID as a parameter.
> Through an application different departments either can run this report or
> create a schedule so that at the end of each month, any department can get
> the report related to only its data.
> How can I do that?
> +++++++++++++++++++++++++
> You input highly appreciatble...
>|||Hi Mary:
Thank you for your response.
I have been using RS API calls to generate the Report from my Web Application.
While generating a report the system already know the Dapartment ID based on
the person who logged in.
Let me give you more information regarding this problem.
1. Report object already exists in the common folder located in the
Reporting Server.
2. End-User can supply Department ID is supplied throgh the program. How can
I perform generate ad-hoc report?
3. How can the end-user schedule this report?
Your input highly appreciatable.
Thanks
Sue
"Mary Bray [SQL Server MVP]" wrote:
> Hi Sue,
> This is more an issue for the developers of the applications calling the
> report as they will be passing the DepartmentID.
> How can the app know which department it is run from? Is there a setting in
> Active Directory for the user? Is there a database table somewhere with who
> lives in which department? (very messy to maintain).
> I'd be querying AD if possible.
> "Sue" wrote:
> > Here is my Case
> > +++++++++++++++++++++++++++++
> > I have a common report object residing on Reporting Service. The report
> > shows Monthly Activity for a Department. This report is based on a Stored
> > Procedure and has Department ID as a parameter.
> >
> > Through an application different departments either can run this report or
> > create a schedule so that at the end of each month, any department can get
> > the report related to only its data.
> >
> > How can I do that?
> > +++++++++++++++++++++++++
> > You input highly appreciatble...
> >
> >sql

Pakcage Execution Error under SQL server agent

Hi SSIS experts!

I have been trying to schedule a package I design to run off hour, but unable to do so. Here is a strange issue:

1. I am able to run and excute the package successfully through VSS. After I finished designing all my flows and containers, my exceution was successful to all my data sources.

2. I was able to deploy and run the actual package by sending to my local file system and it runs successfully through Execute Package Utility.

HOWEVER!!! when I tried to schedule this package through file system under sql server agent to run at night or through start job within SQL agent always failed...

I am puzzled so I added some logging on the package. The error message shows the following....

<message>The connection "{087B883F-D188-440A-9501-FF38CF5CEC28}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

<message>Failed to acquire connection "10.0.2.2.LogDB.jhwang". Connection may not be configured correctly or you may not have the right permissions on this connection.

But I thought if I had set the connection correctly to remember my passwords and using SQL server standard login within my package (connection manager) and should resolved the connection issues....

Why did it failed when I try to run it under sqlagent? But not through Execute Package Utility? Is there is a special setting I need to do for it to run under sql agent?

I notice within the job step when I choose the file source to point to my package... there was a tab called data sources where it has the connections I defined in my package. Does it matter if I put a check box on them or not? Either way they failed to connect.

Please help!

JON

Not sure what the problem is but you may want to debug it by using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie