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.
