Why the Tutorial?
This tutorial came about from watching the Newsgroups and realising that although people had heard of DTS they were unsure as to what it is and where to find it. We wanted to explain DTS to people and
give them an understanding of what it does so they can go away and use it themselves.
What is DTS and what can it do?
DTS or Data Transformation Services is an Extraction, Transformation and Loading (ETL) tool found in Microsoft SQL Server 7 and above. It allows you to move data from nearly any
data source to any data source performing transformations in the process if you require. Here are a couple of practical examples of what DTS is used for.
1. Loading a Data Mart from an OLTP database
2. Analysing data from your web server logs
3. Upgrading an Access Database. Note though that it only imports data not relationships. If the latter is what you want then Microsoft provide an upgrade tool to do this.
4. Consolidation of many business information systems.
These are just a few and are no means exhaustive. We have seen people use DTS for some pretty wild things so it's unlikely that someone else isn't doing what you
are trying to do already. DTS comes with its own Graphical User Interface (GUI) which is intuitive and makes understanding things easier. We'll be taking a look at the DTS designer later on.
How do I use DTS?
There are two main ways you can use DTS to transform data. One is wizard based and the other is through the use of the DTS designer. Because we're starting from the beginning we're going to go
through the steps required to use the wizard and move data between a text file and SQL Server.When we're happy with that we're going to do the same thing but using DTS designer. A big advantage
of the Designer is its flexibility. A big advantage of the wizard is it can do a lot of the work for you allowing you to go into the package later and tweak the bits you want to. We'll come to that later though.