To allow us to add SQL Server Agent jobs into source control, allow us to select system tables as static data.
At least dbo.sysjobs, dbo.sysjobsteps and dbo.sysjobschedules in msdb

I keep SQL Agent creation scripts in source control. In one place I worked, when you checked a .sql file in it would write VSS tags into the file. I made them write into the @description variable so that my agent jobs had an obvious version number. I haven't found a way to do that in TFS yet. Thing is though, it is still a drop and create process so all the execution history gets lost as is not associated with the new job as the new job gets a new job_id.

I am very interested in getting a better way to manage this.

Not just agent jobs but other instance level objects may be necessary for a solution. Linked servers are another example in addition to Agent jobs.

Please make sure that configuration variables are available for this feature so jobs can be configured differently across enviroments. Masking of sensitive variables is also needed in an enterprise environment.

Wow ... pretty amazing that this still has not been done. This is an important feature, There were some good ideas about static data for sysjobsteps ... I may just do that for now.

would be nice to have but as it is open since 2010 not much hope :(

Ditto to all comments below

@jonesjw444 - I couldn't agree more. Scheduled Jobs can be so complex, that having to rebuild them from scratch on various machines is a complete pain. It would be great to just be able to push the changes that have been checked in, and not worry about it. And since they're part of SQL, they ought to be part of SQL Source Control.

One solution using RedGate Sql Source Control with TFS 2013:

Use a "DBA" database, link it to RedGate source control.
Write a stored procedure to manage each SQL Agent Job and check it in
- Start with the SSMS "Script Job As Create To..." and modify the output
- See example below

In the example:
- we create a "SqlAgent job proc" [prcAgentJob_Foo] that does the script work to create or drop+create the Job, called "Foo"
- the Job, in a JobStep, runs a proc [prcTheProcToExecute] that also is under source control
- Job Schedules, Alerts, Notifications, Operators can all be included!

The final "stupid pet trick" is to include a post-build script in TFS that runs a "master script" whose name never changes (so your Build Definition doesn't have to change).
In this TFS post-build script, you reference any "child" scripts that need to be executed.
One of these "child scripts" contains a command to execute the stored procedure that manages the Agent Job ([prcAgentJob_Foo])

Please review and modify the code as necessary for your own testing. The job, as scripted below, won't actually run.

--------------------------
USE [Dba]
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prcAgentJob_Foo]') AND type in (N'P', N'PC'))
EXEC (N'CREATE PROCEDURE [dbo].[prcAgentJob_Foo] AS SELECT 1 AS [foo];');
GO

ALTER PROCEDURE [dbo].[prcAgentJob_Foo]
AS BEGIN
SET NOCOUNT OFF;

BEGIN TRANSACTION;
DECLARE @JobName NVARCHAR(128) = N'Foo';
DECLARE @JobOwner NVARCHAR(20) = (select name from sys.server_principals where principal_id = 1);
DECLARE @jobId BINARY(16);
DECLARE @ReturnCode INT = 0;

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule=1;

SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = @JobName)
IF (@jobId is NULL)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @JobName,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Foo- demonstrate SQL Agent Job SCC',
@category_name=N'DBA',
@owner_login_name = @JobOwner,
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Foo Step 1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DBA.prcTheProcToExecute;',
-- @database_name=N'Foo', -- SET the correct DatabaseName here!
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 15 minutes at :00',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20131107,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 15 minutes at :07',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20131028,
@active_end_date=99991231,
@active_start_time=700,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

---- May want to disable the job initially...
-- EXEC msdb.dbo.sp_update_job @job_name = @JobName, @enabled=0

GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

END
END

SCC and automated build & deployment of SQL Agent Jobs is a must-have in an Enterprise environment. Currently, we have many environments in our path to Production, and are using TFS 2013 for automated build and release, integrated with RedGate SQL Source Control. But any SQL Agent Jobs "solution" is a kludge!

Please, REDGATE, tell us if we should abandon your product in search of something better, or if you are working on an update to provide this capability.

Please ?

Gaping hole in the redGate SCC tool! *CAN* this be accomplished, and it is yet on the RedGate features list?

This would bevery useful, jobs (searchable would be great) in TFS.

It's currently a real pain keep track of all that once you accumulate any number of production jobs.

We do the same as what Pete Cousins described. It works pretty well for us in terms of saving the job, but it doesn't help us with deployment. It would seem that controlling the jobs more tightly to the databases in SVN would make it more likely that the tools (compare or deployment manager) could deploy those more easily than a series of manual script executions.

I would also like to see jobs incorporated into TFS

Yes.. a great new feature .. but for SVN and TFS :)

This can already be done. Script the SQL Agent Job from SSMS (amend script so it drops existing by name, and not job_id), save the file, and add it into to SVN. I have a seperate folder under Trunk called SQLAgentJobs.
We also use VisualSVN (http://www.visualsvn.com/) to version control SSIS packages & SSRS reports using SVN too.

Lakeland's idea makes sense. much easier to keep track of changes that way.

If you wanted to keep jobs like they are though, theres a few options. I wrote the idea of putting MSDB into source control before i tried it, only to find that i cant actually do it.

Im quite new to sql so am constantly learning about useful things... like triggers! As such ive created a trigger that automatically updates a table when a change is made to a job :)

Hopefully this turns out okay on this form:

CREATE TRIGGER AFTER_JOBS_CHANGE
ON msdb.dbo.sysjobsteps
AFTER INSERT, UPDATE, DELETE
AS

MERGE DBMan.dbo.JOBS
USING
(
Select
msdb.dbo.sysjobs.name AS [job_name],
msdb.dbo.sysjobsteps.step_id AS [step_no],
msdb.dbo.sysjobsteps.step_name AS [step_name],
msdb.dbo.sysjobsteps.command AS [step_details],
msdb.dbo.sysjobsteps.step_uid AS [step_uid]
From
msdb.dbo.sysjobs
JOIN msdb.dbo.sysjobsteps
ON msdb.dbo.sysjobs.job_id=msdb.dbo.sysjobsteps.job_id
) AS TEMP

ON
( JOBS.STEP_UID = TEMP.step_uid
)

WHEN MATCHED AND -- If it exists AND has changes
(
JOBS.JOB_NAME <> TEMP.job_name OR
JOBS.STEP_NO <> TEMP.step_no OR
JOBS.STEP_NAME <> TEMP.step_name OR
JOBS.STEP_DETAILS <> TEMP.step_details
)
THEN
UPDATE Set
JOBS.JOB_NAME = TEMP.job_name,
JOBS.STEP_NO = TEMP.step_no,
JOBS.STEP_NAME = TEMP.step_name,
JOBS.STEP_DETAILS = TEMP.step_details,
JOBS.STEP_UID = TEMP.step_uid

WHEN NOT MATCHED AND TEMP.step_uid IS NOT NULL THEN -- New files
INSERT (JOB_NAME, STEP_NO, STEP_NAME, STEP_DETAILS, STEP_UID)
VALUES (TEMP.job_name, TEMP.step_no,TEMP.step_name,TEMP.step_details,TEMP.step_uid)

WHEN NOT MATCHED BY SOURCE THEN
DELETE

;

After youve done this, Just link the 'JOBS' table as static data and it works well :)

I currently work around this by making my jobs short and trivial - all calling a SP which is under source control. e.g. sp_daily_load. That way the only bits I miss out on are things like scheduling and notifications but I get the upside of tightly tying the version of the job to the version of the database.

I like your idea Aleksandar. Looks like SQL Source Control can't be used to put system tables under static data control. Boo.

I know this might be a bit late for some, but a workaround for this would be to to put the msdb database in source control and filter out everything other than the table below:

msdb.dbo.sysjobsteps

It stores all the steps for all jobs, any updates to jobs will update this table. only issue is that you'd have to get people to remember to commit the change when they change a job.

Might even be useful for the redgate team to use this table and integrate jobs into version control... ;)