Fail Job Step if record count on table is zero

I have a job that runs several steps, starting with a truncate of a temp table and then insert from a text file.  After that (a DTS) it runs several stored procedures to do inserts/updates/deletes on the live table from the temp table. Everything runs fine unless a bad field in the text file is sent and it fails during the DTS, therefore importing no new records. (as mentioned above, I truncate the temp table first). So, if it fails to insert the bulk data during the DTS, the JOB doesn't see/read a failure from the DTS and moves on to the next step which is to update the live table, however, it is now updating the live table from a table with 0 records so the live table updates to 0 records, causing item information no longer to display on our website.  I do indicate in the JOB to quit on failure of the DTS, but the JOB just ignores that and continues to the stored procedures.
I need to know what step, or more how to write one in between the DTS and the strored procedures to check the count on the temp table and if the temp table has 0 records, to fail the job and not continue with the stored procedures.
I'm sure it is a simple tsql statement step I can add to the job, but can't get all the syntax correct. I think I use the RAISERROR command but don't know how to write it in to get a correct syntax.
I even created another table to update with the count so I can read off that instead of a straight count.
Here's an example of what I tried. Please note that I have never done this and just tried to put together solutions I found when searching for an answer.

update Item_Master_check
 set success=(Select Count(*) from Item_Master_Temp)

IF (Select Success from Item_Master_check WHERE Success = 0)

RAISERROR ('Not updated.', 16, 1)

I'm sure I can leave out the update and just do a count on Item_Master_temp and if 0 then fail the job.

Can someone help tell me what I need to write into my new step in my job that would help.

If you need more info, just let me know.

Thanks.
lameyersAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
lluthienConnect With a Mentor Commented:
assuming you have you items in the table Item_Master_Temp.

you could try this:
declare @item_count as int
set @item_count = (select count(*) from Item_master_Temp)
-- counting the number of items in the temp table.

then check this with:
if @item_count <= 0
begin
 raiserror('nice message', 16, 1)

end

then in the properties of the job STEP, make sure you set the option "on failure"
to "finish with fail", to make sure the job stops there.

i'm not sure you set the properties of the step, instead of the job.

0
 
lameyersAuthor Commented:
Thank you for the quick response.
I did some initial tests and all appears to work as supposed to.
I've implemented it. If this all goes well over the next week or so, then I'll apply to my other jobs.

Thanks again. Points well deserved!
0
 
lluthienCommented:
glad to help
0
All Courses

From novice to tech pro — start learning today.