Transfer jobs task in SSIS

Hello friends, today we will learn about “Transfer jobs task” in SSIS. This task is quite useful for DBAs.We can transfer all jobs or some specific jobs from one Server to another Server using “Transfer jobs task”. Lets create a SSIS package using “Transfer jobs task” and see the results.

Firstly we will take “Transfer jobs task” in the “Control Flow” region(Shown in below screenshot).

To  configure the “Transfer jobs task”, we will right click on it and click on “Edit” menu, clicking on “Edit” menu will bring a new screen to us(As shown in the below screenshot).

In the above screen, I have selected “Jobs” tab, In this tab we have different configuration settings which we need to configure. Below are the details for each configuration settings.

Source connection:- We will define the source connection(Source Server) from which jobs needs to be transferred.

Destination Connection:- We will define the destination connection(Destination Server) to which jobs will be transferred.

Transfer all jobs:- By default this flag is set to “False” which means that we will provide the list of jobs which will be transferred. If we set this flag to “True”, all the jobs from the source server will be transferred to the destination server.

Job List:- If “Transfer all jobs” field is set to “False” this field will be enabled to specify the list of jobs which needs to be transferred. If “Transfer all jobs” field is set to “True” this field will be disabled.

If object exists:- By default this field’s value is “Fail Task” and which is the recommended setting.

Enable jobs at Destination:- By default this field’s value is “False” which means after transferring the jobs at the destination server, jobs will not be in enabled state. If we set the value of this field to “True”, after transferring the jobs at destination server, jobs will be in enabled state at the destination server.

Below is the screenshot of two server instances(Source and destination).

In the above screenshot, we can see that source server has four jobs and destination server has only one job. Now we will specify the list of jobs those will be transferred from source to destination server.To specify the jobs list , we will click on the button(shown in the below screenshot).

As soon as we click on the button, a new pop up screen will open up with the list of all jobs(As shown in the below screen shot).

I have selected three jobs which are not present at the destination server, now we will click on “OK” button to close the screen. Click on “OK” button further to close the previous screen. Now we are ready with our package. Lets run the package by pressing the “F5” key and see the results.

Green color of task says that our package ran successfully. Lets see if jobs have been transferred successfully or not.

We can see in the above screenshot, all the selected jobs have been transferred to the destination server. I hope you will find this article useful. Please post your comments for feedback about this article.