Most, if not all, SQL Server DBAs have needed to script SQL Server Agent Jobs from time to time in an effort to move/copy jobs to another server, save the scripts to source control, or accomplish some other task. To accomplish this most DBAs would go to the "Object Explorer Details" tab within SSMS and script the jobs as should in Figure 1.
Figure 1
There are a number of issues with this method. First, only one file is generated no matter how many jobs are selected. If you need one file per job you will need to script only one job at a time, which is time consuming, tedious and BORING. Second, the file name is not generated automatically, which requires you to type or copy and paste the job name as the filename making it time consuming and again BORING. Third, the drop script generated by SSMS uses the Job name to determine if the job exists and then of all things hardcodes a GUID in the sp_delete_job stored procedure as shown below. Thus, if you run this drop and create job script on a new server having the existing job it will most likely fail because even though the jobs have the same name it is probable they don't have the same job_id.
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'cdc.AdventureWorks_capture')
EXEC msdb.dbo.sp_delete_job @job_id=N'8da494db-d270-4739-bb5a-9adaf9f8aa57', @delete_unused_schedule=1
GO
Upon looking for a solution to these issues I came across an article written by SQLDenis in entitled: Scripting All The Jobs On Your SQL Server Instance Into Separate Files By Using SMO which nearly addressed all the above issues and could easily be used in a SSIS package as shown by onpnt in the comments. SQLDenis's code did not address the drop code issue so I added the drop logic along with use of a dtsconfig file for setting the folder location and server name. The highlighted code from the attached dtsx package adds the drop script based on the job_name which makes the code generated more portable.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.IO;
using System.Text.RegularExpressions; public void Main()
{
//User::varFilePath,User::varServerName
string strFolder = @Dts.Variables["varFilePath"].Value.ToString();
StringCollection sc = new StringCollection();
ScriptingOptions so = new ScriptingOptions();
so.IncludeDatabaseContext = true;
string filename = ""; //Setup connection, this is windows authentication
ServerConnection conn = new ServerConnection();
conn.LoginSecure = true;
conn.ServerInstance = Dts.Variables["varServerName"].Value.ToString();
Server srv = new Server(conn); /* Setup connection, this is SQL Server authentication
ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "YourUserName";
conn.Password = "YourPassword";
conn.ServerInstance = Dts.Variables["varServerName"].Value.ToString();
Server srv = new Server(conn);
*/ if (!System.IO.Directory.Exists(strFolder))
{
System.IO.Directory.CreateDirectory(strFolder);
}
string script = "";
string strDrop = "";
string JobName;
//Loop over all the jobs
foreach (Job J in srv.JobServer.Jobs)
{ //Output name in the console
Console.WriteLine(J.Name.ToString()); JobName = J.Name.ToString();
sc = J.Script(so);
strDrop = "USE [MSDB] " + (char)13 + (char)10;
strDrop = strDrop + "IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'";
strDrop = strDrop + J.Name.ToString() + "')";
strDrop = strDrop + (char)13 + (char)10;
strDrop = strDrop + " EXEC msdb.dbo.sp_delete_job @job_name=N'" + J.Name.ToString();
strDrop = strDrop + "', @delete_unused_schedule=1";
strDrop = strDrop + (char)13 + (char)10 + "GO";
strDrop = strDrop + (char)13 + (char)10;
//Get all the text for the job
foreach (string s in sc)
{
script += s;
}
script = strDrop + script;
//Generate the file
filename = JobName + ".sql";
filename = Regex.Replace(filename, @"[\/:?'=|]", "");
if (strFolder.Substring(strFolder.Length - 1, 1) == @"\")
{
filename = strFolder + filename;
}
else
{
filename = strFolder + @"\" + filename;
}
TextWriter tw = new StreamWriter(filename);
tw.Write(script);
tw.Close(); //Make the string blank again
script = "";
strDrop = "";
}
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
}
In order to run the attached SSIS package, SQL Server 2005 or 2008 needs to be installed on the PC or server running the package with the development SDKs in the default location of C:\Program Files\Microsoft SQL Server\100(90)\SDK\. The added references used in the script task are Microsoft.SqlServer.Management.Sdk.Sfc.dll, Microsoft.SqlServer.ConnectionInfo.dll, Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.SmoExtended.dll, and Microsoft.SqlServer.SqlEnum.dll. These references are required to use SMO with scripting and to handle the SQL server connection.
The package uses a dtsconfig file named ScriptJobs.dtsConfig stored in C:\Temp by default. The dtsconfig file contains the variables of varFilePath and varServerName which indicate the folder to write job files and the name of the server containing the Jobs to be scripted, respectively. The package does create the folder if it does not exist. The SQL Server connection in the script uses integrated security by default but if you need to use a SQL Server login you will need to use the commented connection code in the script task.
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="xxxx" GeneratedFromPackageName="ScriptJobs" GeneratedFromPackageID="{BE7E98AD-FC33-4C70-BB2B-B081DBBB6B97}" GeneratedDate="6/10/2010 3:47:27 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::varFilePath].Properties[Value]" ValueType="String">
<ConfiguredValue>C:\MyJobs</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::varServerName].Properties[Value]" ValueType="String">
<ConfiguredValue>PC01</ConfiguredValue>
</Configuration>
</DTSConfiguration> /* Setup connection, this is SQL Server authentication
ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "YourUserName";
conn.Password = "YourPassword";
conn.ServerInstance = Dts.Variables["varServerName"].Value.ToString();
Server srv = new Server(conn);
*/
This package will script jobs from SQL Server 2000/2005/2008 as long as the package is run from again a PC or Server with SQL Server 2005 or 2008 installed with SDKs.