Using The Transfer Jobs Task in SQL Server Integration Services

Next, click the Advanced page within the Job Steps Properties – Backup Products Database window and select the option “Quit the job reporting success” for On Success action and also select the checkbox “Include step output in history” as shown in the snippet below. Click OK to save Job Step Properties.

Next, within the parent window, select the Schedules page from the left panel to open the New Job Schedule window as shown in the below snippet.   

In New Job Schedule window, you will need to provide the name, schedule type, Enabled, Frequency, Daily Frequency, Duration etc as shown in the above snippet and click OK to save the schedule changes. Finally click OK once again to create and save Products Database Backup Job.

Example Using The Transfer Jobs Task

In this example we will be using the Transfer Jobs Task to transfer the newly created “Products Database Backup” Job which is available in SQL Server 2005 to SQL Server 2008.

1.       Create a new SQL Server Integration Services Project and rename the default package TransferJobsTask.dtsx

2.       Double click the TransferJobsTask.dtsx package to open it in Design mode.

3.       Drag and drop a Sequence Container from the toolbox to the Control Flow window.

4.       Drag and drop “Transfer Jobs Task” from the toolbox to the Sequence Container in the Control Flow window and rename it   “Transfer Products Database Backup Job”.

5.       Double click the Transfer Jobs Task to open up the Transfer Jobs Task Editor as shown in the snippet below.

6.       In the Transfer Jobs Task Editor, you will need to configure a Source Connection and a Destination Connection. In this example, the source server is a SQL Server 2005 instance and the destination server is a SQL Server 2008 instance.

In this example, you need to choose False as the value for the TransferAllJobs option. Next, click on the Collection … option for the JobsList to select one or more jobs to transfer as shown in the snippet below.

In the Select Jobs window, select “Products Database Backup” job as shown in the above snippet and click OK to save the changes. This will take you back to Transfer Jobs Task Editor, where there are three different options available under the IfObjectExists drop down box, namely:

a.       FailTask

b.      Overwrite

c.       Skip