Post reply

user initiated agent jobs / concurrent job runs

  • December 10, 2013 at 11:34 am

    #301356

    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!

  • December 10, 2013 at 5:48 pm

    #1672481

    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.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 10, 2013 at 7:12 pm

    #1672485

    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

  • December 11, 2013 at 9:34 am

    #1672681

    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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply