SQL Server DTS

What are the different ways of moving data/databases between servers and databases in SQL Server?

There are several ways of doing this. One can use any of the following options:

-          BACKUP/RESTORE, 
- Dettaching/attaching databases, 
- Replication, 
- DTS, 
- BCP, 
- logshipping, 
- INSERT...SELECT, 
- SELECT...INTO, 
- creating INSERT scripts to generate data.

-          SQL Server DTS 

Describe how DTS is used to extract, transform and consolidate data.

Answer
Data Transformation Services is a set of tools available in SQL server that helps to extract, transform and consolidate data. This data can be from different sources into a single or multiple destinations depending on DTS connectivity. To perform such operations DTS offers a set of tools. Depending on the business needs, a DTS package is created. This package contains a list of tasks that define the work to be performed on, transformations to be done on the data objects.

Import or Export data: DTS can import data from a text file or an OLE DB data source into a SQL server or vice versa.

Transform data: DTS designer interface also allows to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. For parameterized queries and mapping purposes, Data driven query task can be used from the DTS designer.

Consolidate data: the DTS designer can also be used to transfer indexes, views, logins, triggers and user defined data. Scripts can also be generated for the sane For performing these tasks, a valid connection(s) to its source and destination data and to any additional data sources, such as lookup tables must be established.