Script all SQL Server Job automatically by SSIS and SMO

Script all SQL Server Job automatically by SSIS and SMO

As per MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

I have one of my production server in SQL Server 2012 and I wanted to take backup of SQL Server Job script automatically once in a day.

We may have replication / mirroring / log shipping for database but there are lot more things out side the database too. In time of failover the server, I need all the job in other server too and that is the reason I planned to backup all SQL Server Agent jobs automatically.

Let us see, how it goes!!!

Create one SSIS project and take one “Script Task”. Double click on “Script Task” to open its editor. From the editor, click on “Edit Script” button.


Once you get script editor, you will find namespace section in the script, I have added few more namespace listed below:

using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;

Here is the code I have written in “Main()” method of script editor.

// TODO: Add your code here
StringCollection strCol = new StringCollection();
ScriptingOptions scriptOpt = new ScriptingOptions();
scriptOpt.IncludeDatabaseContext = true;

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "sa";
conn.Password = "sa";
conn.ServerInstance = "serverName";
Server srv = new Server(conn);

string script = "";
string JobName;
//Looping through the job
foreach (Job J in srv.JobServer.Jobs)
script ="";
JobName = J.Name.ToString();
strCol = J.Script(scriptOpt);

//concate the text of job
foreach (string s in strCol)
script += s;
//save the job file
TextWriter tw = new StreamWriter("D:\\RiteshShah\\ServerName\\JobBackup\\" + JobName.Replace(':', '_').ToString() + ".sql");
Dts.TaskResult = (int)ScriptResults.Success;
Dts.TaskResult = (int)ScriptResults.Failure;

Here is the screen capture of my script editor.


Now, save the script, close the script editor, click “ok” button for “Script Task” property and run your package. If the path for the script you have provided, is exists and the credential is proper, you will be able generate script of all SQL Server Agent Job.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles.

8 thoughts on “Script all SQL Server Job automatically by SSIS and SMO

  1. Pingback: Automatic script backup of email operator in SQL Server by SSIS and SMO

  2. Pingback: Automatic script backup of mail profile in SQL Server by SSIS and SMO

  3. Pingback: Backup Linked Server script in SQL Server by SSIS

  4. Pingback: Script backup of replication setup of SQL Server by SSIS and SMO

    1. Riteshshah Post author

      You can’t run this package in directly in SQL Server 2008. You can run this package in SQL Server 2012’s Job but yes, connect to your SQL Server 2008 instance from that job.

Comments are closed.