SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Restore a DTS package from a DTS Store

By Bruce Szabo,

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.


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.

Total article views: 9783 | Views in the last 30 days: 2
Related Articles

SSIS Package Query Mapping RawFile (Source - .RAW) to FlatFile (destination - .TXT)

SSIS Package Query Mapping RawFile (Source - .RAW) to FlatFile (destination - .TXT)


Running a DTS Package from Query Analyzer

Can you run a DTS package from Query Analyzer...you sure can! Read how to integrate DTS, Jobs and S...


Adding a folder to the SSIS Package Store

The SSIS Package Store is a nice option available for storing SSIS packages.  The Package Store list...


Learning the Query Store

Links and references to understand what the Query Store is in SQL Server 2016.


Query Data Store–SQL Server 2016

SQL Server’s new Query Data Store is a brilliant option for databases if you are a DBA or have to......

sql server 7