provided by: 
Originally published at Internet.comThe primary focus of my book is on the details of DTS implementation - how to use Data Transformation Services. This article discusses some of the opportunities for data transformation provided by DTS.
Upgrading a Database from Microsoft Access to SQL Server
Microsoft has created an upsizing tool for moving a Microsoft Access database to SQL Server. This tool creates the links from the Access application to the SQL database.
However, sometimes people will move a database from Microsoft Access (or some other database) to SQL Server, where it's not important to maintain the user interface that was being used previously. In that case, the DTS Import/Export Wizard is a very convenient tool for creating the new tables in SQL Server and importing the data.
Consolidating Data from Multiple Sources
Companies typically have data in a variety of different OLTP and business analysis systems. DTS is a tool that can connect to a variety of different types of data sources and load that information into one central location. In some cases, companies will use this consolidated data to replace the data stored in diverse places. Other companies will continue to use their data in separate locations, but will use their consolidated information for enterprise-wide business analysis...
Read article at Internet.com site