A DTS Database Store
Data Transformation Services (DTS) is extremely useful in
solving a number of problems.In some
cases I have used it to solve data problems and in other cases I have used it
to solve some tasks that have nothing to do with SQL.
One difficulty I found with DTS was managing
the different packages that exist on servers. For example, I may have packages A,
B and C on Server 1 and packages D, E and F on Server 2,
although the packages can be made generic and abstracted so they use common data sets too often I find
subtle differences between packages on different servers. For this reason it is nice
to have a backup of each package from each server.
As you should know backing up packages is
important and can be a relatively trivial task but to gather up a group of
packages from a variety of servers and store them in one location can be more
At the heart of DTS packages is the MSDB database. All the information pertaining to DTS packages
resides in the MSDB database.
The table that contains the packages is
SYSDTSPACKAGES. This table contains the
current version of the DTS package and also the previous versions of the
To see this for yourself right click on a DTS package in
Enterprise Manager. One of the options
in the context menu is versions.
Selecting versions will list all the versions of the package stored in
the MSDB..SYSDTSPACKAGES table.
This is one way to access steps in a package
that may have been deleted as the package was modified.
Any time a package is saved and a server is specified the
package is stored in the MSDB database of that server.
When there are multiple servers and
workstations in an organization containing DTS packages the packages can be
difficult to manage and backup.
If the MSDB database is backed up on each server and workstation in the
organization the DTS packages can be recovered if needed.
In order to simplify backing up the packages for my company
I created a DTS package to gather all the packages from various servers and store
them in a single database.
The packages are gathered by querying MSDB on all the servers and using
a Data Pump to load them in a central database.
I then created a small extraction package so I
could output a package from the DTS store to a SQL server.
As an aside, it should be noted that one key to working with MSDB and the
SYSDTSPACKAGES table is in order to access the table within DTS packages
one may need to do a disconnected edit to access it.
I found this information on the site www.sqldts.com.
The disconnected edit is avoided here by
specifying a query to access the data we need.
If, however, you wanted to access the
SYSDTSPACKAGES table you would need to use a disconnected edit because
it is not possible to access the table via
the drop down list in the data pump.
The tasks involved in moving the DTS packages to a central location
are as follows. First a database and
table need to be created to hold the packages.
The packages are transfered via a data
pump. A query is used as a source for
the data pump so that only the most recent version of the DTS package is extracted.
Once the server name is appended to the
source query the data can be pumped from the MSDB database on a selected server
to the DTS Store.
In order to implement the DTS Store the first step is to
create a database and table to hold the desired information.
The name of the database I created was
DTSStore. In this database I created a table using the following SQL Script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DTSStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DTSStore]
CREATE TABLE [dbo].[DTSStore] (
[SourceServer] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[id] [uniqueidentifier] NOT NULL ,
[versionid] [uniqueidentifier] NOT NULL ,
[description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[categoryid] [uniqueidentifier] NOT NULL ,
[createdate] [datetime] NULL ,
[owner] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[packagedata] [image] NULL ,
[owner_sid] [varbinary] (85) NOT NULL ,
[packagetype] [int] NOT NULL ,
[datecreated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[DTSStore] ADD
CONSTRAINT [DF_DTSStore_datecreated] DEFAULT (getdate()) FOR [datecreated]
database and table storing our DTS packages have the same name (DTSStore)
because of the author’s inability to come up with creative names.
This table has almost the same structure as the MSDB..SYSDTSPACKAGES table.
There are two additional fields in the DTSStore table: SourceServer and
DateCreated. These two fields help
identify packages that may have the same name but exist on different servers
and also identify the date when a package was extracted from a server.
Notice that the DateCreated has a constraint so that the date defaults to the
current date and time.
The DTS Package to do the work of acquiring the packages is
straightforward. In this case it is
nothing more than a data pump between a source server (DTSPackageSource) and a
destination server (DTSStore).
The destination server is the server that
contains the DTSStore database created above.
The Get Packages DTS package is
If we look at the properties of the data pump we will see
the following on the source tab.
The source is an SQL query. I have listed the query below
because the query cannot be read in the above image.
select @@Servername as SourceServer, dts.*
from msdb..sysdtspackages dts inner join
(select name, max(CreateDate) as CreateDate from msdb..sysdtspackages
Group BY name) BT on
BT.Name = dts.Name
AND BT.CreateDate = dts.CreateDate
order by dts.name
Only the most recent version of each
package is collected from the source server. The most recent
version is selected by specifying the package with the greatest creation
date. I have also added the @@Servername
to the select statement so the source server name can be added to the DTSStore for each package.
The destination tab contains the DTSStore as our destination
If the store was created using the query above the DTSStore can be
used directly, it simply needs to be selected on the destination tab of the data pump.
The source query and the destination match up column for
column except for the datecreated. The transformation tab can then map
each column directly from the source to the destination. Datecreated does not need
to be specified because it has a default value constraint.
At this point everything is complete to copy packages from a
source server to the DTS store. By
changing the server name properties of the DTSPackageSource object the DTS
packages from a variety of servers can be copied to the store.
With a little extra work this package could be designed to
query a variety of servers and acquire the packages in an automated
manner. The package could then be scheduled
so the packages would be backed up on a regular basis by backing up a single database.
Using a simlar process it is possible to move the packages from the destination
server back to a source server. It is also possible to create a package that will extract
a single package. These topics will be covered in a future article or can be explored by the reader.