Why Do We Need DTS (Data Transformation Service?)

Lets say you have data in one format(It can be a database/file/excel spreadsheet also). You want to convert it into another format. You will have a thought of writing a VB program and convert the source file into the format of destination file. But wait a minute. If you have lots of files in the different format and you want to convert it into different formats, then would you write VB programs for each conversion? You can write, but it is very time consuming and error prone.

Here comes into picture the DTS. As name suggests, it is a service for transforming data from one format to another. We can draw a workflow diagram of the data and SQL server Enterprise Manager will automatically create code to execute it. Here we need not have knowledge of how to open a file in VB, how to manipulate data, and how to store in the destination. DTS package handles all these intricacies automatically. Suppose you have excel file in which user data is stored. Now you want to put only certain data (for ex. all persons whose email address ends with yahoo.com) to SQL Server. In DTS, you have to drag excel sheet object, SQL Server object and make them point to appropriate source and destination. Put an arrow pointing from excel sheet to SQL Server. An arrow represents transformation of data from one form to another. By default, it transforms all data. We can create a custom transformation and depending on the condition, we can insert data into SQL Server.

So the ease with DTS is, if we have multiple transformation, it would be very easy for a programmer to transform data from one form to another without lots of coding.

So lets start using DTS with a simple example. You have excel file and you want to upload it in SQL Server. So here are the steps.

1. Enterprise Manager > Microsoft SQL Servers > [SQL Server Group] > [SQL Server] > Data Transformation Services > Local Packages.
2. Right click on it and choose “New Package”
3. On the left hand side, you can see “Connection” and “Task”. Connection represents source and destination of data. Task represents different tasks to convert the data from one form to another
4. In “Connection” 3rd button is Excel. Click on it. It will open a dialog box. Provide the name of the new connection and browse the source excel file and put the path in “File Name”.
5. In “Connection” 1st icon represents SQL Server. Click on it and provide valid credentials and select appropriate database.
6. In “Task” menu 3rd button is “Transform Data Task”. Now click on Excel icon which will become source. Click on SQL Server icon. It will become destination.
7. Double click on the arrow. It will open up property which is important to transform Excel data into SQL data.
8. Select appropriate sheet in Source tab. In Destination tab, select appropriate table name. For our case it is “Item”.
9. You can see one to one mapping from source field to destination fields. Now press “OK”.
10. In the main menu, go to “Package” > Execute. This command starts executing the package. Now if you open the table, you will see the data.

You can download the whole dts package by clicking on DTS Package Example. Please save the package in c:\. From enterprise manager, right click on “Data Transformation Services” and choose “Open Package”, browse c:\DTS\DTSExample.dts. Once you open it, double click on SQL Server icon and change the credentials. Run the script
DTS.sql to create a table “Item”.