BCP Utility and DTS package

Describe in brief exporting and importing utility

BCP Utility
The Bulk Copy is a command utility that transfer SQL data to or from a data file.
This utility mostly used to transfer huge data to SQL server from other database.
With this, data is first exported from source file to a data file and then imported from the data file to SQL server table.
It is also used to transfer data from SQL server table to a data file.
You can use ‘Bulk Insert’ statement to transfer data from data file to SQL server table.

DTS Packages
It is a set of tools that allows you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You can create custom data movement solution using DTS object model.

DTS packages can provide following services:
It can import or export data to or from text file or OLE DB data source.
It supports data transformation using DTS transformation which means that data can be operated using one or more functions before hitting the destination.
You can transfer database objects along with data using DTS package.
DTS package also notifies if package steps succeed or failed by sending mails to source and destination.

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

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.

Describe in brief exporting and importing utility

SQL Server has an export/import wizard that allows creation of a Integration services package enabling export import of data from one location to another.
To start the wizard:
Start menu-> SQL Server Management Studio-> Business Intelligence Development Studio.
This wizard can import/export data from any data source that has a managed .Net Framework data provide or its own native OLEDB provider. E.g.: SQL Server, Flat files, MS Access, MS Excel etc.