Data Transformation Services (DTS) is a powerful tool included with SQL Server 7.0 that allows database administrators and developers to perform transactional-based data transformations. With DTS, you can easily import and export data into or out of SQL Server, as well as create workflow processes using simple jobs. In this article, we will explore the key concepts and features of DTS.

What is DTS?

DTS is a comprehensive tool that provides a wide range of functionalities for data transformation in SQL Server. It includes a version control system similar to Source Safe and has the ability to store data into a metadata repository. This allows users to view the attributes of the data without having to open a table in design view, reducing the risk of introducing security vulnerabilities.

A DTS file, also known as a package, consists of individual steps that can be executed in any desired order. These packages can be saved in three different formats: as a file, into the local server, or into a repository. Saving a package as a file is the fastest option and helps prevent common corruption issues. However, it limits the sharing of packages through SQL Server. Repository packages, on the other hand, are saved in the MSDB database and offer high availability of metadata for your project. Local packages are also saved in the MSDB database and provide a good compromise for sharing packages in a development environment.

Getting Started with DTS

To create your first DTS package, you can right-click on Data Transformation Services in Enterprise Manager and select “New Package”. This will open the DTS Designer, which is divided into two sections: tasks and data. The data section allows you to specify the source and destination DBMS, while the task section defines what actions DTS should perform on the data connection.

Let’s walk through a basic example. Suppose we want to create a table in the Northwind database and transfer data from a flat text file into it. Here are the steps:

1. Create a table in the Northwind database with the specified columns.

CREATE TABLE tempimport (
  column1 VARCHAR(15),
  column2 VARCHAR(15),
  column3 VARCHAR(15)
);

2. Drag and drop the SQL Server icon from the data section onto the DTS Designer workspace. Configure the SQL Server connection by selecting a default database (e.g., Northwind).
3. Drag and drop the text source icon from the data section onto the workspace. Select the flat file provided in the download section and configure the properties accordingly.
4. Connect the text file source to the SQL Server destination by clicking on the “Text File (Source)” icon and then holding the control key and clicking on the SQL Server icon. This will create an arrow between the two icons.
5. Double-click on the arrow to open the transform properties. Confirm the source and destination tables, and ensure a one-to-one relationship between the columns.
6. Execute the package by selecting “Execute Package” from the package menu. You should see the results of the data transfer.

These are the basic steps to get started with DTS. In future articles, we will explore more advanced options and error trapping techniques.

If you have any questions or encounter any issues, feel free to reach out via email. DTS is a powerful tool that can greatly simplify data transformation tasks in SQL Server, and mastering its features will enhance your database management skills.