How to Transfer SQL Server Jobs Task from Source Server to Distention Server!

Question :

How i can transfer SQL Server jobs Task from Source Server to Distention Server without T-SQL Script ?

Answer :

We can do that by SSIS ” SQL SERVER INTEGRATION SERVICES ”  by creating new package on it to transfer the jobs task from one server to another server.

Note : also by SSIS i can trnsfer

1- Database Task

2- Error Massage Task

3- Login Task

4- Master Stored procedure Task

5- SQL Server objects Task

 

Demo to how to transfer SQL Server jobs task :

1- open Start menu >>> SQL Server >>>  SQL Server Business Intelligence Development Studio >>> File Menu >>> New >>> project >>> Select project type is Business Intelligence Projects from the left bar >>> select from the Right template Integration Services Project >>>write name for this Project EX (Transfer Jobs) >>> Choose your path to save the project >>> OK

2- from the left Select from the Toolbox Menu under the Control flow items >>> Transfer job Task  >>> Drag and Drop this task on Control flow page >>> right click on the task >>> Select Edit .

 

 

3- After the Edit we will view New Screen ” Transfer Jobs Task Editor ” >>> Select jobs from the left bar .

4- We have 3 Group :

A- Connection :

Source Connection           : The SQL Server Instance the Jobs Crated on it .

Destination Connection : The SQL Server you need to referrer the jobs to it .

B- Jobs :

Transfer All Jobs : False if you don’t need to transfer All jobs and this is Default Value , True  if you need to transfer All jobs .

Job List : if you select The transfer All jobs is FALSE Select from here the Jobs list you need to transfer it to another Server .

C- Option :

If Object Exist : Meaning if the one Job from the List job you need to transfer it to another Server it’s Created already on this Server you will Select from this 3 Value

• FailTask : If the job Found Fail The task.
• Overwrite : if the job Found Overwrite on it .
• Skip : if the job Found Skip this job

EnablejobsAtDistention : False = Disable , True = Enable .

After you finish your Configuration Click OK

4- now we need to run the task by F5 or Click on play icon to execute the package.

5- Now Check your Distinction SQL Server Agent Jobs you will found the New Jobs you Selected it to Transfer .

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator