Transfer Jobs Task in SSIS

Transfer Jobs Task

Transfer Jobs Task is used to transfer SQL Server Agent jobs from one SQL Server instance to another. This task gives you an option to copy all the jobs or selected jobs from the source server to the destination server.

Let's demonstrate how you can create a SSIS package using the Transfer Jobs Task. Goto START -> Microsoft SQL Server 2005/2008 -> SQL Server Business Intelligence Development Studio -> then goto File menu -> New -> Project -> Select "Business Intelligence Projects" in the left tree pane -> Select "Integration Services Projects" and name the project as you wish and click OK.

In this new project you will see there is one package already named "Package.dtsx". Drag a Transfer Jobs Task from the Toolbox (which is normally on the left side) to the Control Flow pane as shown below. Right click on this task and click onEdit...

In the Transfer Jobs Task Editor, select Jobs on the left and now you are ready to configure this task.

These are the items that can be configured:

• Connections
  • SourceConnection - specify the connection for the source SQL Server instance, if you have already created a connection then you can reuse it here or can create a new one.
  • DestinationConnection - specify the connection for the destination SQL Server instance.
• Jobs
  • TransferAllJobs - if this is set to True it will transfer all jobs. If this is set to False you can select specific jobs you want to transfer.
  • JobsList - this will be enabled if TransferAllJobs is set to False. Then you can select specific jobs to transfer. See the image below where I am only selecting two jobs (Backup Databases and Rebuild Indexes) to transfer to the destination.
• Options
  • IfObjectExists - If the jobs already exist on the destination then you have three choices, first FailTaskexecution, second Overwrite the destination job and third Skip the existing job and continue with others.
  • EnableJobsAtDestination - After the transfer you can also specify whether to enable or disable the jobs.

Once you are done with all these configurations you can hit F5 or click on play icon to execute the package. Your task will turn yellow during execution and then either red or green depending on the execution outcome. You can go to the progress/execution results tab to see any error messages if the execution fails.

Note: the person executing the package to transfer jobs must be sysadmin or member of any fixed SQL Server Agent fixed database role on both the source and destination instances.

---

Transfer Logins Task

Transfer Logins Task is used to transfer either all logins (except sa) or selected logins or all logins of selected databases from the source to the destination SQL Server instance. After the transfer, all the transferred SQL logins are assigned random passwords and SQL logins are disabled. The DBA needs to change the password and enable the SQL login before it can be used on the destination.

Let's walk through an example. Create another package in the current project and drag a Transfer Logins Task from the Toolbox to the Control Flow. Right click on the task and select Edit to configure the task's properties as shown below.

These are the items that can be configured:

• Connections
  • SourceConnection - specify the connection for the source SQL Server instance
  • DestinationConnection - specify the connection for the destination SQL Server instance
• Logins
  • LoginsToTransfer - You have three options for this:
    • AllLogins - this will transfer all logins from the source.
    • SelectedLogins - this allows you to select specific logins
    • AllLoginsFromSelectedDatabases - This allows you to transfer all logins associated with one or more databases as shown in the image below.
  • LoginsList - this will allow you to select specific logins if you select SelectedLogins for LoginsToTransfer
  • DatabaseList - this will allow you to select the databases if you select AllLoginsFromSelectedDatabasesfor LoginsToTransfer
• Options
  • IfObjectExists - If the logins already exist on the destination you have three choices; first FailTaskexecution, second Overwrite the destination logins and third Skip the existing login and continue with the others.
  • CopySids - if you set it to True then security identifiers (SIDs) associated with logins are also copied to the destination

Note: the person executing the package to transfer logins must be sysadmin on both the source and destination instances.