Home > SSIS > Transfer SQL Server Agent Job(s) between servers

Transfer SQL Server Agent Job(s) between servers


SQL Server agent service the most valuable service in the SQL Server. It helps SQL Server administrator to automate their tasks by scheduling the SQL job in SQL Agent service.

Job is a piece of task which may have more than one step in it. Basically, SQL Server administrators use this facility to automate their repetitive tasks such as database backup, database maintenance task, and SSIS team may have some jobs to download data from a specific server to a target server. This list may go for miles. My intention is to show you how to transfer SQL server agent job(s) from one server to another using SSIS task.

Transfer Jobs Task

Implementation:

Step 1: Create a package and Add Transfer Jobs Task.

Step 2: Open Transfer jobs Task editor and navigate to Jobs option.

Step 3: configure SourceConnection and DestinationConnection property.

a. SourceConnection points to copy from server details.

b. DestinationConnection points to copy to server details.

Step 4: Under Jobs category, we can select all jobs in the source server or the list of job(s).

a. if you wish to copy all jobs from the source then change TansferAllJobs property to true else Choose the list of jobs from the JobsList collection property.

Step 5: Options property category has two properties.

a. IfObjectExists – select any one action from the drop down list (FailTask/Overwirte/Skip)

b. EnableJobsAtDestination – this option will enable jobs at the destination server if the property value is true otherwise jobs will be copied to destination server and marked disable.

Note: you should have at least two servers to perform transfer jobs task.

SQL Agent jobs can be transfer using other ways also.

1. Generate the script of all job(s) in the source server and execute the script file at the destination server. It is possible manually or using sqlcmd.

sqlcmd: sqlcmd -S Servername\InstanceName -i ScriptFileName.sql

2. Using .Net SMO, Most of the applications in the market using this method to manage Agent jobs.

3. Transfer Jobs Task using SSIS.

SSIS is a very simple ETL tool that performs the most powerful tasks.

What do you think about this article? write your comments.

Advertisements
  1. Nevin
    March 1, 2012 at 6:26 am

    Hi

    I have created a Transfer job task but the package fails when it encounters an error though I have set the maximum error count to 10 . Is there a way to ignore this error so that the transfer of the rest of the SQL jobs continues? Package fails at 56%….

    • March 1, 2012 at 10:45 pm

      It could have happend for some other cause. So, please post your error message.

  1. October 15, 2011 at 9:37 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: