6.
Drag and drop “Transfer Database Task” from the toolbox to the Sequence
Container in the Control Flow window and rename it “Transfer Northwind Database Using Transfer Database Task”.
7.
Double click the Transfer Database Task to open up the Transfer
Database Task Editor.
In the Transfer Database Task Editor, you need to configure a SourceConnection
and a DestinationConnection by selecting the <New connection…>
option from the drop down list. This will open up the SMO Connection Manger
Editor as shown in the below snippet, where you need to set the SQL Server
Instance name and the connection parameters. Finally click OK.
In the Source Database menu, choose “Copy” as the Action
value, “DatabaseOnline” as the Method value and choose “Northwind”
database as the SourceDatabaseName.
In the Destination Database menu, set DestinationDatabaseName as “Northwind”
and click on the “…” button to provide the DestinationDatabaseFiles
details as shown in the below snippet. Click OK to save the Destination
Database Files configuration and Choose “True” as the DestinationOverwrite
value.
Finally, the Transfer Database Task Editor should look similar to the
one shown in the snippet below. Click OK to save the Transfer Database Task Editor
Configuration.
8.
Drag and drop “Execute SQL Task” from the toolbox to the Sequence
Container in the Control Flow window and rename it “Changing User Passwords and Enabling Transferred Logins”.
9.
Double click the Execute SQL Task to open up the Execute SQL Task
Editor as shown in the below snippet.
In the Execute SQL Task Editor, you will need to configure a
connection to a SQL Server 2008 Instance. For the SQLSourceType property,
choose Direct Input as the option from the drop down list. Next, copy the
below TSQL code and paste it into the SQLStatement textbox. Finally, click OK
to save changes in the Execute SQL Task Editor.
USE
[master]
GO
ALTER
LOGIN [WebUserA] WITH
PASSWORD=N’WebUserA’
GO
ALTER
LOGIN [WebUserA] ENABLE
GO
ALTER
LOGIN [WebUserB] WITH
PASSWORD=N’WebUserB’
GO
ALTER
LOGIN [WebUserB] ENABLE
GO