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

conditional job step Expand / Collapse
Author
Message
Posted Tuesday, October 02, 2012 4:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 89, Visits: 300
Hi all,
I have a job that has one step (to populate table A). There is an external app that processes data from table A and deletes all rows from it. I need to add next step to a job ONLY if table A is empty. Is it possible via GUI? Something like IF EXISTS (select 1 from A) execute step 2. The problem is that amount of time for the app to process table A is different every time.
How would you do it?

Thanks,
Eugene
Post #1367330
Posted Wednesday, October 03, 2012 4:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
How about making the second job step script check the table with COUNT(*) & return if the table is not empty.

If you need to wait between steps 1 & 2, could you make the second step a separate job & run it after an interval that's enough for the app to finish?
Post #1367530
Posted Wednesday, October 03, 2012 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:05 AM
Points: 43, Visits: 162
Hi eugene,

Have a look at the below URL. It will help you...

http://stackoverflow.com/questions/9530983/ssis-execute-first-task-if-condition-met-else-skip-to-next
Post #1367544
Posted Wednesday, October 03, 2012 10:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 89, Visits: 300
Thanks for replies,
I don't use SSIS.
Not sure if it makes sense, but I was hoping to place the check for number of rows in table A (function call below) at the beginning of the second step, but it fails:

CREATE FUNCTION [dbo].[num_rows_in_A_fn] ()
RETURNS int
BEGIN
DECLARE @num_rows int
SET @num_rows = (select COUNT(*) from A)

if @num_rows = 0
begin
WAITFOR DELAY '00:00:10';
-- call itself
exec [dbo].[num_rows_in_A_fn]
end
RETURN @num_rows
END

------
Msg 443, Level 16, State 14, Procedure num_rows_in_queue_fn, Line 11
Invalid use of a side-effecting operator 'WAITFOR' within a function.
------

Thanks,
Eugene
Post #1367845
Posted Wednesday, October 03, 2012 10:40 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 34, Visits: 194
If your app has access to delete data on your server, does it also ave access to run a job? If the app kicks off the job only after its done its processing then you won't have any downtime between the two steps. It could use something like:

EXEC msdb..sp_start_job @job_name= 'jobname here'





SQL Tips and Scripts
SQLWorks Blog
Post #1367847
Posted Wednesday, October 03, 2012 1:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
eugene.pipko (10/3/2012)
Thanks for replies,
I don't use SSIS.
Not sure if it makes sense, but I was hoping to place the check for number of rows in table A (function call below) at the beginning of the second step, but it fails:

CREATE FUNCTION [dbo].[num_rows_in_A_fn] ()
RETURNS int
BEGIN
DECLARE @num_rows int
SET @num_rows = (select COUNT(*) from A)

if @num_rows = 0
begin
WAITFOR DELAY '00:00:10';
-- call itself
exec [dbo].[num_rows_in_A_fn]
end
RETURN @num_rows
END

------
Msg 443, Level 16, State 14, Procedure num_rows_in_queue_fn, Line 11
Invalid use of a side-effecting operator 'WAITFOR' within a function.
------

Thanks,
Eugene


If you want a wait loop, just do the check in the loop & exit when it passes. Do it all within the same sp - something like this:


CREATE PROCEDURE [dbo].[RunJob] AS
BEGIN
SET NOCOUNT ON;
DECLARE @num_rows int;
SET @num_Rows = 999;

WHILE @num_rows > 0
BEGIN
SET @num_rows = (select COUNT(*) from A);

if @num_rows > 0
WAITFOR DELAY '00:00:10';

-- Possibly add some code to prevent an infinite loop?
END

-- Call job:
exec [dbo].[TheJobYouWantToRun]

END


Post #1368012
Posted Wednesday, October 03, 2012 5:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 89, Visits: 300
Thank you, it is close to what I wanted to achieve.
Post #1368111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse