Using The Transfer Jobs Task in SQL Server Integration Services

Introduction

SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers or administrators to perform Extract, Transform & Load (ETL) operations. In my previous article entitled Using Transfer Error Messages and Transfer Master Stored Procedures Tasks in SSIS I discussed how to use the Transfer Error Messages Task and Transfer Master Stored Procedure Tasks which are available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of the Transfer Jobs Task which is available in SQL Server 2005 and later versions.

Overview of SQL Server Tasks in SSIS 2005 & Later Versions

The SQL Server Tasks within SSIS can be used to copy, modify and delete SQL Server objects and data. The different types of SQL Server Tasks available in SSIS are Execute SQL Task, Bulk Insert Task, Transfer Database Task, Transfer Login Task, Transfer SQL Server Objects Task, Transfer Error Message Task, Transfer Master Stored Procedure Task and Transfer Jobs Task. If you are new to SQL Server Integration Services then I would recommend you to start with my previous article entitled SQL Server 2008 Integration Services Tasks to understand different types of Integration Services Tasks which are available in SQL Server 2005 and later versions. However, in this article you will see an example for how to configure and use Transfer Jobs Task.

Overview of the Transfer Jobs Task

Database Administrators can use the Transfer Jobs Task to transfer one or more SQL Server Agent Jobs between different SQL Server instances. The Transfer Jobs Task can be configured to transfer not only a single job but also all the SQL Server Agent Jobs from one instance of SQL Server to another instance. There is one more very important option available -  to specify whether the transferred SQL Server Agent Jobs should be enabled at the destination or not. The Transfer Job Task can be configured to handle the below scenarios:

a)      Overwrite an existing SQL Server Agent Job if there are existing jobs at the destination.

b)     If a duplicate SQL Server Agent Job exists in the destination server, then fail the Transfer Jobs Task.

c)      Skip the Transfer Jobs Task where there is a duplicate job existing in the destination server.

Create a SQL Server Agent Job to Backup Products Database

You can create a new SQL Server Agent Job namely “Products Database Backup” to backup the Products database by expanding the SQL Server Agent Node and then right clicking the Jobs node to select the New Jobs… option from the drop down menu as shown in the snippet below.

In New Job window, select the General page on the left side panel and provide the Name, Owner, Category and Description for the job. However, you will also need to ensure that the Enabled checkbox is selected, this will make sure that once the job is created successfully it will be enabled and will run on the specified schedule. 

Next, select the Steps page on the left side panel and click the New… button at the bottom of the window to add a new step.

In the New Job Step window, you will need to provide a suitable step name, select type as “Transact-SQL script (T-SQL)” from the drop down list and choose database as “master”. Next, enter  the below TSQL code in the command text box to backup the Products database.

Backup Database Products

To Disk = N'E:\DatabaseBackups\Products.Bak'

With Init