Sql agent job

Question

Post reply

Sql agent job

sqlfriend

SSC Guru

Points: 52769
More actions
May 15, 2019 at 11:09 pm

#3644060
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 years, 11 months ago by Dave Convery.

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

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

Andrey Right there with Babe Points: 780 More actions · Answer 1

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: 246686 More actions · Answer 2

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

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
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
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

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: 246686 More actions · Answer 4

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sqlfriend SSC Guru Points: 52769 More actions · Answer 5

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: 246686 More actions · Answer 6

On Success and On Failure are your conditions.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

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: 246686 More actions · Answer 8

sqlfriend 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 10

Do I need to declare variable like in function or not

This reply was modified 4 years, 11 months ago by sqlfriend.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 11

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sqlfriend SSC Guru Points: 52769 More actions · Answer 12

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: 246686 More actions · Answer 13

sqlfriend 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

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.

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)