Transfer Jobs Task
Applies to: 
SQL Server
SSIS Integration Runtime in Azure Data Factory
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 SQL Server source and destination. There are no restrictions on which version 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 (SSIS) Variables and Use 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.
Configuration of the Transfer Jobs Task
You can set properties through SSIS Designer or programmatically.
For information about the properties that you can set in SSIS Designer, click the following topic:
For information about programmatically setting these properties, click the of the following topic:
Related Tasks
For more information about how to set these properties in SSIS Designer, click the following topic:
Transfer Jobs Task Editor (General Page)
Use the General page of the Transfer Jobs Task Editor dialog box to name and describe the Transfer Jobs task.
Note
Only members of the sysadmin fixed server role or one of the SQL Server Agent fixed database roles on the destination server can successfully create jobs there. To access jobs on the source server, users must be a member of at least the SQLAgentUserRole fixed database role there. For more information about SQL Server Agent fixed database roles and their permissions, see SQL Server Agent Fixed Database Roles.
Options
Name
Type a unique name for the Transfer Jobs task. This name is used as the label in the task icon.
Note
Task names must be unique within a package.
Description
Type a description of the Transfer Jobs task.
Transfer Jobs Task Editor (Jobs Page)
Use the Jobs page of the Transfer Jobs Task Editor dialog box to specify properties for copying one or more SQL Server Agent jobs from one instance of SQL Server to another.
Note
To access jobs on the source server, users must be a member of at least the SQLAgentUserRole fixed database role on the server. To successfully create jobs on the destination server, the user must be a member of the sysadmin fixed server role or one of the SQL Server Agent fixed database roles. For more information about SQL Server Agent fixed database roles and their permissions, see SQL Server Agent Fixed Database Roles.
Options
SourceConnection
Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the source server.
DestinationConnection
Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the destination server.
TransferAllJobs
Select whether the task should copy all or only the specified SQL Server Agent jobs from the source to the destination server.
This property has the options listed in the following table:
| Value | Description |
|---|---|
| True | Copy all jobs. |
| False | Copy only the specified jobs. |
JobsList
Click the browse button (...) to select the jobs to copy. At least one job must be selected.
Note
Specify the SourceConnection before selecting jobs to copy.
The JobsList option is unavailable when TransferAllJobs is set to True.
IfObjectExists
Select how the task should handle jobs of the same name that already exist on the destination server.
This property has the options listed in the following table:
| Value | Description |
|---|---|
| FailTask | Task fails if jobs of the same name already exist on the destination server. |
| Overwrite | Task overwrites jobs of the same name on the destination server. |
| Skip | Task skips jobs of the same name that exist on the destination server. |
EnableJobsAtDestination
Select whether the jobs copied to the destination server should be enabled.
This property has the options listed in the following table:
| Value | Description |
|---|---|
| True | Enable jobs on destination server. |
| False | Disable jobs on destination server. |