Transfer SQL Server Jobs Task and Transfer SQL Server Logins Task in SSIS

By: Arshad Ali   |   Updated: 2010-08-04   |   Comments (6)   |   Related: More > Integration Services Control Flow Transformations

Problem

Making a replica of your production server to create a dev or test environment or moving your production data to new hardware are tasks a DBA needs to perform. So what are different methods to perform these tasks efficiently and without much effort? In this tip I am going to demonstrate how you can use the Transfer Jobs Task and the Transfer Logins Task to perform some of these operations.

Solution

In my last tip, Transfer Database Task and Transfer SQL Server Objects Task in SSIS, I showed how you can transfer your databases and SQL Server objects with the Transfer Database Task and Transfer SQL Server Objects Task. In this tip I am going to demonstrate you how you can transfer jobs and logins using the Transfer Jobs Task and Transfer Logins Task respectively without writing any code.

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 on Edit...

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 FailTask execution, 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 AllLoginsFromSelectedDatabases for LoginsToTransfer
• Options
  • IfObjectExists - If the logins already exist on the destination you have three choices; first FailTask execution, 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.

Next Steps

• Review SQL Server Integration Services tips.
• Review Transfer Jobs Task on msdn.
• Review Transfer Logins Task on msdn.

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips