Data Transformation Services (DTS) is a set of tools that lets you quickly and easily move and manipulate data. If you do any work with current versions of SQL Server, you have probably used the DTS wizard to import or export data from SQL Server into other data sources. In this article, we give you a quick overview of why DTS is a great tool and what you can accomplish with it. The other two articles in this series talk about the DTS object model, and give you tips for implementing your own DTS solutions.
Need for Data Transfer
Today's IT environment is very diverse. Most companies store their data in multiple relational database management systems (RDBMS). The most popular RDBMS on the market are Microsoft SQL Server, Oracle, Sybase, and DB2. Many organizations also store some of their data in non-relational formats such as mainframes, spreadsheets, and email systems. Smaller databases are commonly built and maintained using one of the desktop RDBMS, such as Microsoft Access. Despite the fact that data is disseminated among multiple data stores, the organization still has to operate as a single entity. Therefore, there needs to be a way to relate and often interchange data among various data stores.
Need for exchanging data among multiple systems has been around for a long time. Prior to DTS' debut in SQL Server 7.0, the only tool for importing and exporting data to and from SQL Server was the Bulk Copy Program (BCP). This command line utility is relatively straightforward (although somewhat cryptic) to use, and offers fair performance. However, the capabilities of BCP are quite limited: You can either export data from SQL Server into a text file or from the text file to SQL Server.
Another predecessor of DTS was the SQL Server object transfer utility, which let you transfer database objects and data between two SQL Servers.
It's easy to guess that neither BCP nor the object transfer utility could sufficiently serve data exchanging needs. Many companies spent top dollars to create their own custom tools for transferring and transforming data among various sources.