Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSIS Package to Script All SQL Server Jobs to Individual Files

By Thomas Lane,

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.

 

Resources:

ScriptJobs.dtsx | ScriptJobs.dtsconfig
Total article views: 9787 | Views in the last 30 days: 28
 
Related Articles
FORUM

rename the filename using script task in ssis

rename the filename using script task in ssis

FORUM

Scripting help - Need a query to find filenames and filepaths under a certain directory

Scripting help - Need a query to find filenames and filepaths under a certain directory

FORUM

Use Database table to dynamically get the excel filename

Dynamic excel filename and path from database table

FORUM

SSIS - FTP TASK - DYNAMIC FILENAME

How to use the FTP Task with a dynamic localpath or filename

FORUM

SQL Server 2005 script

SQL Server 2005 script

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones