A DTS Data Store
I discussed a method for collecting
DTS packages from a variety of servers and storing them in a single database. This
technique allows one to capture all of the packages from a variety of servers
and then store and use them from a single source. Using
this process can ease the life of a database administrator who has to make
backup DTS packages stored on multiple servers.
The above article, however, fails to mention how to get a
package from the DTS store to a server.
In this article I will discuss a DTS package that allows one to extract a single DTS
package from the DTS store and put it on a specific server. Additionally,
I will discuss restoring a set of packages from the store to a server.
This article assumes the user has created the
DTSStore
database and a table with the same name within that database as DTS store
article.
The above package contains all the steps one needs to restore
a single package from the DTS store to a specific server. There
are really just a few steps needed to do this restore. The
first step uses an ActiveX script to query the user for the package name and
the server the package was backed up from. The
script uses these two input parameters to create a query string and stores the
string in a global variable. The
dynamic properties task is used to pass the global variable into the
PackageSource
SQL Connection. The Data pump is
then used to move the needed data elements from the data source (PackageSource )
to the destination source (DestinationServer ) which
in this case will be the MSDB database on the destination server. This
is an overview so let’s examine each step a little more closely.
The ActiveX Script
When running a package in interactive mode it is possible to
use input boxes. The first input
box prompts for the package name. The
second input box prompts for the name of the server the package resided on when
it was backed up. If the need arose
the DTS store could be queried using Query Analyzer to find package and server
names.
Once the two parameters have been entered the SQL query string
is constructed. In the DTS package
created for this article the wild card character has been added in the SQL
query string for the package name.
This allows one to enter only part of the package name in order to extract the package
that needs to be restored. This is convenient but must be used with care. If
there is a set of package names that have the same beginning characters more
packages may be restored than desired. In
some cases you may want to edit this script so that the user has to type the
exact package name (it is the same script but the percent sign would be removed
from the
strSQL string. The query string is passed to a DTS global variable. This
is done so the dynamic properties task can update the query string of the
PackageSource
connection.
Function Main()
strPackage=InputBox ("What is the package name you want to restore?","Package ")
strServer= InputBox ("What server was the package on?","Server ")
strSQL = "select top 1 * from DTSStore where SourceServer = '" & strServer
& "' and name like '"
& strPackage & "%'"
strSQL = strSQL & " order by datecreated desc "
DTSGlobalVariables("strSQL").Value = strSQL
Main = DTSTaskExecResult_Success
End Function
SQL Connection (PackageSource )
The package source is the connection to the
DTSStore
database.
This can be setup by configuring the properties in the SQL Connection Window.
SQL Connection (DestinationServer )
The Destination Server task establishes the connection to the
MSDB database on the server where the package should be restored.
Data Pump Task
The data pump task is represented by the arrow that exists
between the two server connections. In
order to set this task up correctly we need to create the task between the
PackageSource
and the
DestinationServer . Once
the task is created we can setup the task properties. Double
Click on the arrow that was created when the data pump task was setup.
On the Source table select the
DTSStore.DBO.DTSStore
table. Although we set the package
up using this table we will end up changing this source using the Dynamic
Properties Task described below.
The table we want for the destination table is [MSDB].dbo.SYSDTSPackages .
As one can see the table is listed in the drop down list. This
is not the default behavior though, in order to be able to get this selection
we need to do a disconnected edit.
In order to accomplish this, exit out of the data pump window
and right click in the DTS Package design window. One
of the selections will be disconnected edit…
It is listed is right below package properties. Once this
selection is highlighted the following screen is presented. Navigate
to the appropriate data pump task which should be number one if the example you
are following is like the one created here. Update
the
DestinationObjectName
to equal [msdb].[dbo].[sysdtspackages]
by typing it directly into the space provided.
Now that this task is accomplished we can go back into the
data pump task and setup the transformations.
In this case the transformations should default to the way we need them. The
two fields in the
PackageSource
that don’t have equivalents in the
MSDB..SysDTSPackages
are servername and datecreated. These
two fields should not map to any fields on the transformation tab.
All the other fields should map from source field to
destination field with each field in the source matching an equivalent field in
the destination.
Dynamic Properties Task (Setup Needed Variables)
The dynamic property task allows updates to DTS package
parameters during the execution of a package.
If you are familiar with DTS this next step may be trivial but if you are new to DTS or
not familiar with this task this section may be a little confusing.
What we want to do is update the source of the data pump which
is the arrow between the two SQL connections in this case
PackageSource
and
DestinationServer . In
order to use the Dynamic Properties Task the way we need to we have to setup
the Package Source, Destination Server and the data pump as we did in the
previously.
If you recall when we created the data pump on the source tab we selected the
DTSStore
table. Using the dynamic properties
task we are going to replace the table with the query we stored in the global
variable
strSQL, to set the dynamic properties task open the task and click
add. The screen will look just like
the one we saw when we opened the disconnected edit screen. Highlight
the DTSTask_DTSDataPumpTask_1 and scroll down on the right hand side of the
screen. Click on the
SourceSQLStatement
and click Set…
The following screen will appear and you can set it up as
listed. The source should be Global
Variable and the variable should be
strSQL
(provided this is the name of the variable in the ActiveX script).
At this point the entire package should be done. When
it is run the user will be prompted for the package name and the name where the
package was originally stored. The
package will be moved to the server specified by the
DestinationServer
SQL Connection object. After doing
a refresh of Enterprise Manager the transferred task should reside in the DTS
Package section on the destination server.
Notes:
Using this same methodology one can change the query for an
individual package and restore a group of packages. This package will generate
an error if the query returns two package names with the same id and
versionid. This error occurs because
of a primary key violation in MSDB.
This situation can be remedied by editing the query that extracts the packages from the
DTSStore
database.
When restoring DTS packages make sure to test the package
first. The
MSDB..SysDTSPackages
is a system table and one needs to take care when working with system tables.