Transfer Logins Task and Transfer Database Task in SSIS
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.
ALTER LOGIN [WebUserA] WITH PASSWORD=N’WebUserA’
ALTER LOGIN [WebUserA] ENABLE
ALTER LOGIN [WebUserB] WITH PASSWORD=N’WebUserB’
ALTER LOGIN [WebUserB] ENABLE