Post reply

Is it possible to use SSIS to disable jobs on multiple servers?

  • October 6, 2006 at 7:42 am

    #153220

    I would like to use SSIS to enable/disable jobs during holidays.  Here's my plan...

    1.  Create a store procedure to query each server for a list of enabled jobs and records those jobs in a table called job_status on a single server.  For Example:  Server A would store list of enabled jobs on all servers.

    1a. The stored procedure will then disable these jobs

    2.  Create a stored procedure to enable the jobs previously disabled per server

    Job_Status Table would look like this:

    Server_Name        Job_Name        AddDate

    Server 1              Job 1               1/1/06

    Server 2              Job 1               1/1/06

    I am able to do this by creating 2 jobs and a table for each server.  To be honest, I'm a little lazy and would rather create 1 SSIS package to accomplish this for each server.  Is it possible to disable the jobs on Server B by querying the job_status table on Server A?

  • October 6, 2006 at 9:31 am

    #664634

    I believe you should be able to do this, but be aware that you will need to use msdb.dbo.sp_update_job to do it:

    EXEC msdb.dbo.sp_update_job @job_name=N'test',

      @enabled=0

    If you try and update the sysjobs table enabled column to 0 the SQL agent will not pick this up untill it is restarted, msdb.dbo.sp_update_job refreshes the sql agent but does it via a dll so you cannot control it.

Viewing 2 posts - 1 through 1 (of 1 total)

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