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.