Transfer Jobs Task in SSIS 2008 R2 with Example

Friends,

This task allows to transfer any of the existing SQL Server Agent jobs between SQL Server instances. Just like the other SMO tasks, we can either select to transfer all jobs to synchronize two instances, or use the task to selectively pick which jobs you want to move to another server.

Let’s see how we configure this task. To demonstrate the same, I am creating a JOB in the Sql Server Agent in default instance and move it from there to another instance using “Transfer Jobs Task”.

Open the SSMS and click on SQL Server Agent->Job->New Job.

Under new job we configured the job that will be run on daily basis.

Name: name of the new job

Owner: Job owner (user name)

Category: In this we will select, for which category this task will be fallen. Here I have selected the database maintenance.

Enable: If the Enable checkbox is checked means the task is ready for running.

T- SQL Backup database Script

BACKUP DATABASE [AdventureWorksDW] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW.bak’ WITH NOFORMAT, NOINIT,  NAME = N’AdventureWorksDW-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Step Name: Assign the step name

Type: Which type of job will be schedule, here I am gona selected as TSQL script.

Database: select the database

Command: Under this option we provide the TSQL script

Now we define the schedule date and time.

Name: Schedule job name

Schedule type: It carry out the schedule type like one time, Recurring or start when the SQL server Agent will be start etc. Here I am gona select the one time.

Click on OK and close it now the Job has been configured. We can see in given screenshot also it is showing.

Now open the solution and configure the task.

Right click on task or double click on task the configuration page will be open

Source Connection: Provide the source connection from where the job will be copied to destination server.

Destination server: Provide the destination server here.
Transfer All Job: Put it to False because we gonna transfer only selected job not all the job(which can be taken a lot of time if multiple jobs are presents).

Job List: List out the job from here (which will be copied).

Go to next option i.e.

If Object Exist: Here we have 3 options like Fail task, over right and Skip. I have selected the Skip option means it will be skip the job if it is available.

Now execute the task and see the output

Task is successfully completed, now to see the result again go to SSMS and check the destination server.

In destination server the job Database Backup is created. We can see the history of job after runs, the sample screenshot is here.

That is the steps for creating Transfer Job task in SSIS. Hope you have understood the concept. Try it guys !!

Thank a lot Sushil Mishra for very good documentation work. Keep it up !!

Regards,
Roopesh Babu V