• Article

Transfer Jobs Task

The Transfer Jobs task transfers one or more SQL Server Agent jobs between instances of SQL Server.

The Transfer Jobs task can be configured to transfer all jobs, or only specified jobs. You can also indicate whether the transferred jobs are enabled at the destination.

The jobs to be transferred may already exist on the destination. The Transfer Jobs task can be configured to handle existing jobs in the following ways:

  • Overwrite existing jobs.

  • Fail the task when duplicate jobs exist.

  • Skip duplicate jobs.

At run time, the Transfer Jobs task connects to the source and destination servers by using one or two SMO connection managers. The SMO connection manager is configured separately from the Transfer Jobs task, and then is referenced in the Transfer Jobs task. The SMO connection manager specifies the server and the authentication mode to use when accessing the server. For more information, see SMO Connection Manager.

Transferring Jobs Between Instances of SQL Server

The Transfer Jobs task supports a source and destination that is SQL Server 2000 or SQL Server. There are no restrictions on which of the two versions to use as a source or destination.

Events

The Transfer Jobs task raises an information event that reports the number of jobs transferred and a warning event when a job is overwritten. The task does not report incremental progress of the job transfer; it reports only 0% and 100% completion.

Execution Value

The execution value, defined in the ExecutionValue property of the task, returns the number of jobs that are transferred. By assigning a user-defined variable to the ExecValueVariable property of the Transfer Jobs task, information about the job transfer can be made available to other objects in the package. For more information, see Integration Services Variables and Using Variables in Packages.

Log Entries

The Transfer Jobs task includes the following custom log entries:

  • TransferJobsTaskStarTransferringObjects   This log entry reports that the transfer has started. The log entry includes the start time.

  • TransferJobsTaskFinishedTransferringObjects    This log entry reports that the transfer has finished. The log entry includes the end time.

In addition, a log entry for the OnInformation event reports the number of jobs that were transferred and a log entry for the OnWarning event is written for each job on the destination that is overwritten.

Security and Permissions

To transfer jobs, the user must be a member of the sysadmin fixed server role or one of the fixed SQL Server Agent fixed database roles on the msdb database on the both the source and destination instances of SQL Server.

Configuring the Transfer Jobs Task

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in SSIS Designer, click one of the following topics:

For more information about how to set these properties in SSIS Designer, click the following topic:

Configuring the Transfer Jobs Task Programmatically

For more information about programmatically setting these properties, click the of the following topic:

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.