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.

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

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |