Sql agent job

  • sqlfriends

    SSC Guru

    Points: 52310

    I have an SSIS run frequently every hour in a day to load data from other sources and update data.

    I would like to set up something like this:

    if there is new record in a table then start the SSIS, otherwise don’t do anything meaning don’ t run the SSIS.

    i am thinking in the sql agent job first step is to select count * from to get new record if there is new record then call the SSIS  in second step of the job , otherwise quit the job successfully. Is there a way to do this in sql agent job?

    Thanks

    • This topic was modified 4 days, 19 hours ago by  Dave Convery.
  • Andrey

    Old Hand

    Points: 366

    First idea

    you can create second job with no schedule (and even disabled)  which invokes your SSIS package

    in the first job when there are new records just run the second job using procedure sp_start_job

    don't forget to enable/disable second job before/after

    Cons: two jobs

    Second idea

    another idea is to use only one job with two steps

    Step 1 :

    in the code - if there are no new records, report failure

    in the properties - if failed quit the job, on success proceed with Step 2

    Cons: this approach is less efficient whereas you can't be sure whether the job failed as designed (no new records)  or  something bad happened and the check itself can't be run

    Third idea

    Single job step in which  invoke SSIS package using T-SQL when there are new records

    link :

    https://docs.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-ssms

     

     

  • Phil Parkin

    SSC Guru

    Points: 243263

    The SQL Agent job steps idea could be expanded to something like this

    1. T-SQL searches for new rows. Fail the step (select 1/0, or similar) if no new rows found.

      1. On failure, go to step 2
      2. On success, go to step 3

    2. T-SQL searches for new rows, but do not fail if no new rows found

      1. On success, quit the job reporting success
      2. On failure, quit the job reporting failure

    3. Run the SSIS job

      1. On success, quit the job reporting success
      2. On failure, quit the job reporting failure

    Step (2) is an attempt to catch any 'proper' failures not caught in step 1.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sqlfriends

    SSC Guru

    Points: 52310

    Thanks,

    But how i can write code in the step like if count 0 then quite with success, if count 1 go to next step.

  • Phil Parkin

    SSC Guru

    Points: 243263

    No code required. It's in the Advanced properties for the step.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sqlfriends

    SSC Guru

    Points: 52310

    Thanks, I see in advanced, I can choose go to next step...

    But where it knows if the result is 0 or 1 to make decision?

     

    Thanks

  • Phil Parkin

    SSC Guru

    Points: 243263

    On Success and On Failure are your conditions.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sqlfriends

    SSC Guru

    Points: 52310

    Thanks, I know the condition and the process, but how can I construct the sql to let it know if it is 0 then on success, if 1 failure.

    Could you give an example of SQL:

    Select count(*) from referals

    ......

  • Phil Parkin

    SSC Guru

    Points: 243263

    sqlfriends wrote:

    Thanks, I know the condition and the process, but how can I construct the sql to let it know if it is 0 then on success, if 1 failure. Could you give an example of SQL: Select count(*) from referals ......

    With >50,000 points, I'd expect you to know how to do this!

    Here is one way:

    IF
    (
    SELECT COUNT(*) FROM Referrals
    ) > 0
    SELECT 1 / 0;
    ELSE
    SELECT 1;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sqlfriends

    SSC Guru

    Points: 52310

    I just wonder what is the syntax that should work in the T_SQL step.

  • sqlfriends

    SSC Guru

    Points: 52310

    Do I need to declare variable like in  function or not

    • This reply was modified 3 days, 13 hours ago by  sqlfriends.
  • Phil Parkin

    SSC Guru

    Points: 243263

    It's time for you to do some work yourself, rather than relying on me to spoon-feed you with every single detail.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sqlfriends

    SSC Guru

    Points: 52310

    Thanks,

    But my original question is not about how to use the tabs from advanced step about  success quit, and fail continue etc.

    I know all those.

    It is about the syntax on how to pass the result to the process.

    Anyway I will figure out myself.

  • Phil Parkin

    SSC Guru

    Points: 243263

    sqlfriends wrote:

    Thanks, But my original question is not about how to use the tabs from advanced step about  success quit, and fail continue etc. I know all those. It is about the syntax on how to pass the result to the process. Anyway I will figure out myself.

    I gave you a working solution. Nothing needs to be passed anywhere. But you seem too lazy to even try it.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeff Moden

    SSC Guru

    Points: 993640

    Shifting gears a bit, what does the SSIS job do that cannot be done in 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 18 total)

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