In my last article,

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.