What is the best way to include SQL Server Agent jobs in Source Control?

I'm really glad you asked this question. I had this very same need for SQL Server jobs in a Visual Studio database project and SQL Server job deployments along with a database. I wanted the solution file to contain everything that is needed for the SQL database process I had implemented, all in one place, to Create this solution AND Deploy this solution to any given server (in just a few clicks). Components in this solution are:

Database schema

SSIS package

SQL Server configurations (master DB “Credential” creation; msdb “Proxy” creation; SQL Agent “Job” creation).

I wanted to keep it all together for two reasons:

• Simplified deployment (SOURCE: Local Dev Instance / TARGET: Server Dev Instance)
  
• Solution documentation So I set to work on figuring this out. Here is what I created and how I sourced this setup. It works great for the intended purposes.

Overview: Create the script files with the TSQL code. I used the CHECK FOR EXISTENCE method to avoid errors where I could possibly be creating something that already exists in the environment. Reference: How to check if a Credential exists in a SQL Server instance?

Example:

  IF EXISTS (SELECT job_id FROM msdb.do.sys jobs WHERE name = N'MyJobNameInSqlAgent') BEGIN print 'Yep, found it!!!' --Really you need to add the script to create it... END

Create a script file that executes the script files. More details are below. Publish the database project, and BOOM you've just done something awesome.

SSDT Setup: Pluralsight: "DB Project development in VS" by Robert Cain | Module: Data and Schema Comparisons, Pre and Post Deployment Scripts First I reviewed this helpful Pluralsight Video. I was able to follow along, and what I did was...

• A) New Folder. Create a new folder in the project called [ScriptsPostDeployment]
  
• B) NewScript. Create a newscript to the folder called [Create_SqlAgentJobs.sql].
  

ERROR MSG: "You can only have one post-deployment script in visual studio database project"

You will get this error message if you try to create multiple script files with [Build Action=" Post Deploy"].

In total, my project needed not only this one script, but it need some other scripts as well-- Create_SqlAgentJobs.sql, Credential_GEORGES-bl0040ep.sql, ProxyAccount_GEORGES-bl0040ep.sql. The error was discussed and solved in this thread on StackOverflow: SQL Server project executing multiple scripts post-deploy. In total, the new folder has 4 scripts-- 1 to run the scripts; 3 scripts that need to be executed. 

SSDT-dbproj_DB-ScriptsPostDeployment.png SSDT Publish:

I ran the Publish file and chose the option to "Generate Script". I viewed the script and... POST DEPLOYMENT code was automatically inserted to add the SQL AGENT JOB!! Success! Below are the script and script execution messages after running the query on the SQL instance in SSMS...

  /* Deployment script for DBA This code was generated by a tool. Changes to this file may cause incorrect behavior and will be lost if the code is regenerated. */ GO SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF; GO etvar DatabaseName "DBA" etvar DefaultFilePrefix "DBA" etvar DefaultDataPath "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA" etvar DefaultLogPath "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA" GO n error exit GO /* Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported. To re-enable the script after enabling SQLCMD mode, execute the following: SET NOEXEC OFF; */ etvar __IsSqlCmdEnabled "True" GO IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True' BEGIN PRINT N'SQLCMD mode must be enabled to successfully execute this script.'; SET NOEXEC ON;

Hope this helps you resolve SQL Server Jobs!