A DTS Data 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 involved.

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 package.

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]
GO

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]
GO
ALTER TABLE [dbo].[DTSStore] ADD
CONSTRAINT [DF_DTSStore_datecreated] DEFAULT (getdate()) FOR [datecreated]
GO

NOTE: The 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 shown below.

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 table.

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.