Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

user initiated agent jobs / concurrent job runs Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 11:34 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 4:26 PM
Points: 400, Visits: 946
Currently we have an agent job that runs an SSIS package on a schedule and produces reporting data needed, but in some cases users will need this data for a custom set of parameters. This is operational data that is constantly changing so everything runs as close to realtime as possible.

We've duplicated the job and modified the SSIS packages to accept these custom parameters.

Now we need to give users the ability to run the job as needed. We'll do this using a stored procedure to call the job either through a windows form or our intranet.

The main issue is how to handle concurrent runs.
after struggling with this for a bit I realized I could simply create a new job for each request run it and drop it. The only change needed in the script is to give it a unique name and provide the parameters.
I'm guessing on the high end about 50 reports will be run per day and each will run in less than 30 seconds.

Does this sound like a reasonable approach? any issue or caveats to be aware of.

This must designed to run on 2008 R2 Standard Edition.

thanks!
Post #1521620
Posted Tuesday, December 10, 2013 5:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,979, Visits: 30,270
I guess my question would be.... why are you using SSIS to create reports? Why not SSRS or just a stored procedure?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1521718
Posted Tuesday, December 10, 2013 7:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
Jeff Moden (12/10/2013)
I guess my question would be.... why are you using SSIS to create reports? Why not SSRS or just a stored procedure?
That was my question as well..

CEWII
Post #1521724
Posted Wednesday, December 11, 2013 9:34 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 4:26 PM
Points: 400, Visits: 946
I could say something like "it's not possible to reproduce this using TSQL... well maybe with cursors" .... LOL, Just kidding Jeff

The main objective of this whole project is to monitor an operational system, (non SQL Server) Look for certain conditions, gather data about the condition, and display this in a dashboard format on large flat screen TV's in our operations center. The users can dig into the issues using Excel and view the detailed data using pivots and graphs.

We actually do pull the report data using stored procedures for displaying in Excel which we could do using SSRS as well, that's not the issue.

The issue is when users want to see data about a condition that doesn't exist yet. In this case there is no data to report on. So the on demand option allows them to specify custom parameters and run a slimmed down version of the ETL.

I hope that helps clear it up, sorry I didn't explain better in my original post.


Post #1521964
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse