Transfer Logins Task and Transfer Database Task in SSIS
Server Integration Services (SSIS) is a Business Intelligence tool which can be
used by database developers or administrators to perform Extract, Transform
& Load (ETL) operations. In my previous article entitled Execute SQL and Bulk Insert Tasks in SSIS I discussed how to use the Execute SQL and Bulk Insert tasks
which are available in SQL Server 2005 Integration Services and later versions.
In this article I will examine the use of the Transfer
Logins Task and Transfer Database Task which are available in SQL
Server 2005 and later versions.
Overview of SQL
Server Tasks in SSIS 2005 & Later Versions
The SQL Server Tasks within SSIS can be used to copy, modify and
delete SQL Server objects and data. The different types of SQL Server Tasks
which are available in SSIS are Execute
SQL Task, Bulk
Insert Task, Transfer Database Task, Transfer
Error Message Task, Transfer Job Task, Transfer Login Task, Transfer Master
Stored Procedure Task and Transfer SQL Server Objects Task. If you are new to
SQL Server Integration Services then I would recommend you to start with my
previous article entitled SQL Server 2008 Integration Services Tasks to understand different types of Integration Services Tasks which
are available in SQL Server 2005 and later versions. In this article you will
see an example for how to configure and use the Transfer
Logins and Transfer Database tasks.
Overview of the Transfer
Administrators can use the Transfer Logins Task to transfer logins between
different instances of SQL Server. Using this task you can either transfer all
the logins from one SQL Server Instance to another SQL Server Instance, transfer
only specific logins, or transfer only logins which have access to a specific
user database. At the destination server, the transferred SQL Server Logins are
by default disabled and are assigned a random password. Once the task is
complete, the DBA needs to assign the correct password for the SQL Server
account and enable the logins to allow users to access the database. However,
using the Transfer Logins Task you cannot transfer sa logins (including
renamed sa logins) between different instances of SQL Server.
Overview of the Transfer
Database Administrators can use the Transfer Database Task to
transfer a SQL Server database between two instances of SQL Server. Using this
task you can either copy or move the database between two different instances
of SQL Server. This task basically supports two modes of database transfer –
namely DatabaseOnline and DatabaseOffline.
DatabaseOnline Mode: – Using database
online mode the database will remain attached on the source SQL Server and the
entire object within the database will be transferred to the destination SQL
Server using SQL Management Object (SMO).
DatabaseOffline Mode: – Using database
offline mode the database is detached from the source SQL Server, and all the
related mdf, ndf and ldf files for the source server are copied or moved to the
destination server. Once the database files are successfully copied, the
database is attached to the destination SQL Server and made available for
users. It is always better to use DatabaseOffline mode as it is much faster than
DatabaseOnline mode. The only disadvantage to using DatabaseOffline mode is that
the database will not be available for end users during the activity.
Worked example Using
Transfer Logins and Transfer Database Tasks
In this example we will first use the Transfer Logins Task
to transfer two SQL Server Logins namely WebUserA and WebUserB
from SQL Server 2005 to SQL Server 2008. Once the SQL Server Logins are
transferred successfully, we will use the Transfer Database Task to copy
Northwind database in DatabaseOnline Mode from SQL Server 2005 to SQL
Server 2008. Finally, we will use the Execute SQL Task to run a TSQL script
which will assign the appropriate password to the copied SQL Server Logins and then
Create a new SQL Server Integration Services Project and rename the
default package TransferLoginsAndDatabasesTask.dts
Double click the TransferLoginsAndDatabasesTask.dtsx package to
open it up in Design mode.
Drag and drop a Sequence Container from the toolbox to the Control
Drag and drop “Transfer Logins Task” from the toolbox to the Sequence
Container in the Control Flow window and rename it “Transfer Logins Using Transfer Login Task”.
Double click the Transfer Logins Task to open up the Transfer
Logins Task Editor as shown in the snippet below.
In Transfer Logins Task Editor, you need to configure a Source Connection
and a Destination Connection. In this example, the source server is a SQL
Server 2005 instance and the destination server is a SQL Server 2008 instance.
There are three different options available under the LoginsToTransfer drop
down box namely
In this example select “SelectedLogins” option and then
from the “LoginsList” select WebUserA and WebUserB SQL Server
Logins which need to be transferred to the SQL Server 2008 instance.
In the Options sub menu choose “Overwrite” as the value for
“IfObjectExists” and also make sure you have selected “True” as the value for
“CopySids”. If you don’t choose to copy Security Identifiers (SIDs) to the
destination server, then the transferred logins will not be recognized by the
destination databases. Finally, click OK to save the changes in the Transfer