none
SQL Server Agent - Making 1 job dependent on another

    Question

  • I have one job that refreshes my SQL Server database from a legacy application using SSIS packages. If that job completes successfully, I want to run another job that uses that data to create an extract of our clients for sending to head office. I have searched for a method of making the second job wait until the first one is finished and then to only run if the first is successful but I haven't found anything. My only thought is that I would have to run the second job from the first one - is this the only way or is there a neater solution?

    Monday, May 05, 2008 9:15 PM

Answers

All replies

  • If one is dependent on the other, then I would make an additional step to do the secondary process.  This way you have immediate execution of that process.

     

    Monday, May 05, 2008 9:56 PM
    Moderator
  • That is the solution I thought of. The slight problem with this approach is that the first job is not built by me but is created by a program written by the supplier of the legacy system. They created the ADO drivers to read the data out of their proprietary database and created a generator to build the SSIS packages to take the data out of that and load it into the SQL Server database. If they make changes to the database structure and I have to regenerate the job and packages then I always have to add in my extra steps. That's OK but it is obviously neater if I could find a way that the second job does the checking as that will be unaffected by the regeneration of the first job.

     

    If there is no such function then I will just have to do it the way you suggest.

     

    Monday, May 05, 2008 10:24 PM
  • I've decided that this is the way I should perform my task. My problem is, I can't find out how to call a secondary job from a job step. Is that possible or do I need to replicate all the steps of my second job in the first one?

     

    Monday, May 05, 2008 11:13 PM
  • Tuesday, May 06, 2008 12:29 AM
    Moderator
  • Did you mean to post that last reply without any content?

     

    Tuesday, May 06, 2008 12:59 AM
  • Hmm.  That is weird.  It sure had content when I clicked post.  Lets try this again:

     

    You can check a jobs status with the sp_help_job stored procedure.  Its reference in the BOL is:

     

    http://msdn2.microsoft.com/en-us/library/ms186722.aspx

     

    To Start a job, you can use sp_start_job.  Its reference in the BOL is:

     

    http://technet.microsoft.com/en-us/library/ms186757.aspx

     

    Tuesday, May 06, 2008 1:03 AM
    Moderator
  • Here is a real quick method to find out if a job is running in simple tsql.  You will need to create a user defined function to do this:

     

    Code Snippet

    CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)
    RETURNS VARCHAR(8)
    AS
    BEGIN
    RETURN (substring(left(@job_id,8),7,2) +
      substring(left(@job_id,8),5,2) +
      substring(left(@job_id,8),3,2) +
      substring(left(@job_id,8),1,2))
    END

     

     

    Create that in a database of your choice, master works fine if you want.  Then run the following:

     

    Code Snippet

    DECLARE @success bit

    SET @success = 0

    WHILE @success = 0

    BEGIN

    IF EXISTS (SELECT 1

    FROM master..sysprocesses p

    JOIN msdb..sysjobs j ON master.dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)

    WHERE program_name like 'SQLAgent - TSQL JobStep (Job %'

    AND j.name = 'JobName')

    BEGIN

    WAITFOR DELAY '00:05' -- wait 5 minutes and it will loop

    END

    ELSE

    BEGIN

    EXEC msdb..sp_start_job 'jobname'

    SET @success = 1

    END

    END

     

     

    You should test this, but it should create a loop until the first job stops with 5 minute wait delays between looking if the job has run or not.  When the second job runs, it will set @success = 1 and end the loop.

     

    I don't know how well this is with keeping best practices, but it will do what you want it to.  This could become a long running process in SQL, not that it is doing anything but sleeping until the second job kicks in, but just a warning I want to provide here.

    Tuesday, May 06, 2008 1:34 AM
    Moderator
  •  Nick Ryan wrote:

    I have one job that refreshes my SQL Server database from a legacy application using SSIS packages. If that job completes successfully, I want to run another job that uses that data to create an extract of our clients for sending to head office. I have searched for a method of making the second job wait until the first one is finished and then to only run if the first is successful but I haven't found anything. My only thought is that I would have to run the second job from the first one - is this the only way or is there a neater solution?

     

    Why not continue to use SSIS for the second step of this?

     

    Create a master package that uses two Execute Package Tasks...  Connect the two tasks together with a precedence constraint so that the first execute package task runs before the other.

     

    The first Execute Package task would simply point to the package you already have built to refresh your SQL Server database from your legacy application.  The second execute package task would point to a new (or perhaps one that you've already built) SSIS package that creates the data extract for your clients.

    Tuesday, May 06, 2008 1:26 PM
    Moderator
  • I want to avoid placing any of my own code in the package generated from the supplier because we have to regenerate the job and package when changes are made to the legacy database (which is quite frequent). That means that any code I put in there would be lost by the rebuild and have to be recreated.
    Tuesday, March 17, 2009 12:31 AM
  • ravikiransid,

    You should start a new thread rather than appending to a 3 1/2 year old thread.  Also, your post does not sound like a question, so you probably need to make it clearer what you are trying to do.  Having said that, it should be possible for JOB2 to check for the file created by JOB1.

    RLF

    Monday, December 24, 2012 2:05 PM