Data Transformation Services (DTS) is a comprehensive

tool now included with SQL Server 7.0. DTS allows a

database administrator or developer to setup

transactional-based transformation of data into

or out of SQL Server, or create simple jobs to be

executed as a workflow process. It includes a

version control system, similar to Source Safe

and has the ability to store data into a metadata

repository. The advantage of this is that anyone,

given the proper rights can view what attributes the

data has without having to open a table in design

view and possibly introducing a security risk.

A DTS file is known as a package, which includes

individual steps that are executed in whichever

order you wish. DTS 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 to execute a package and prevents

common corruption. It does however, hinder the

sharing of your package through SQL Server.

Repository packages are saved in your MSDB

database and are the slowest to execute,

load, as well as save. I have also

experienced a high corruption rate with

these packages. The benefit however would be high

availability of metadata for your project. To see

how to turn on package metadata see figure 1 below.

Local packages are also saved into your MSDB database

and do take longer to load due to no metadata being

saved. Local packages offer a good compromise to a

development environment where you must share the

package among others but do not wish a lower risk

of corruption.

FIGURE 1

Figure 1 shows how to turn on saving your package's

metadata by checking the two lineage buttons. To

save your package metadata you will need to save the

package into your repository. Metadata tracks

changes to the package as well as monitors who has

executed the package and when.

To create your first DTS package, hit your right

mouse button in Enterprise Manager on Data

Transformation Services, and click New Package.

This will load DTS Designer. DTS Designer left menu

is divided into two areas : tasks and data (you may

notice that if you have not installed SP1 some minor

screen paint problems). The data section will

allow you to designate your source and destination

DBMS while your task section will tell DTS what

to do with the data connection.

Let’s practice the basics. For our exercise, we will

want to create a table in the Northwind database and

transfer a flat text file into it. You can download

the sample flat file and DTS object by going to the

download section of this article.

Step 1 : Create a table in the Northwind database

with the below specifications :

create table tempimport
(column1 varchar(15),
column2 varchar(15),
column3 varchar(15))

Step 2 : You will need to click and drag the SQL

Server icon from the data section onto the Designer

workspace. This will pull up the configuration

screen for SQL Server. It is extremely important to

select a default database (Northwind in this example)

or some options may not work properly (you may need

to click refresh to see the entire list of

databases).

Step 3 : Click the text source icon from the data

section and drag the icon into the DTS Designer

workspace. In the properties select the flat file

that is provided to you in the download section.

After selecting the flat file, click properties

(see figure 2). You will want to select delimited

format on the text file properties screen then click

next. The next property screen offers you the

choice of how you wish to delimit the files. The

sample file is delimited by semicolons. In a

production environment, you can delimit the files

however you wish.

FIGURE 2

Step 3: From the Designer worksheet, click on the

“Text File (Source)” icon. Then holding the control

key, click on the SQL Server icon. You should see

now both icons darkened on your worksheet. Under

the workflow menu, click “Add Transform”. This will

create a arrow from the text file to SQL Server.

FIGURE 3

Step 4 : Double-click on the arrow between

the text file and SQL Server. This action will pull

up the transform properties. Confirm under the source

tab, that your text file is selected. Under the

destination tab, confirm that Northwind.tempimport

is selected (see figure 3). Under the transform

tab, confirm that you have a one-to-one

relationship between the table columns. We

will review the advanced tab in a different article.

FIGURE 4

Step 5 : You can now execute the package,

under the package menu and “Execute Package”. If

all goes as planned you should see results similar

to Figure 4. If not, download the sample package and

flat file, and reverse engineer it or feel free to

send me an email. To open the package provided,

you can click on Data Transformation Services in

SQL Enterprise Manager, All Tasks, and Open

Package. You may want to perform a “save as”

to save the package into your repository.

From a DOS prompt or XP_CMDSHELL you can run a DTS

Package by typing the following :

DTSRUN /F/MSSQL/BINN/DTSPACKAGENAME.DTS

If you have any questions or problems, please feel

free to email me. In future articles, we will cover

the more advanced DTS options and error trapping.