This article will try and explain the different ways you can transfer packages between servers and other storage locations,
either directly or as files. The most common requirement is to transfer between two SQL Servers, and this is the main
focus here, but some methods can be adapted for any of the other storage locations. The DTSBackup 2000 tool incorporates several of these
methods into an easy to use interface.
The simplest method is to use Enterprise Manager to open an existing package,
and then select Save As from the Package menu. You can then save the package
directly to another server, or change the storage location to save it to a structured storage
file if you do not have direct access to the final destination. The storage file
can then be manually copied to the destination server. At the destination, open Enterprise Manager
and right-click the Data Transformation Services node to access the Open Package option.
This allows you to select a structured storage file package to open. You can then use the Save As option
to save it to the new server.
Another method is to use the object model to perform the load and save
operations required. The main problem with using the object model methods
is that they do not persist the annotation and layout information contained
in a package, so when you re-open the package yourself the layout will
revert to a default format. The advantage of using the object model is that
you can change certain properties whilst transferring.
For example you could change all SQL Server connections to use a different
server which may be useful when migrating between environments.
There is a sample application available for download from Tools & Tasks page.
The most efficient method with no negative side-affects is to use DTS to transfer
the packages directly. This quicker and easier than
using files, but is only available for local server packages,
which are stored
in the msdb.dbo.sysdtspackags. Unfortunately there is no
equivalent table
for meta data (repository) packages.
You can also use a simple DataPump task to transfer the contents of
sysdtspackages
directly
between servers, or you can export it to a text file and copy this manually
before
re-importing into the destination server. Do not be tempted to change any
of the values
along the way as they are also stored within the package object itself and
any conflicts
may prevent a package from loading in the future.
To transfer local packages directly between servers follow the simple steps
below:
1
Create a new package, and add two connections, one for the source server
and one for the destination server.
2
Select the two connections and add a DataPump (Transform Data) task.
3 SQL Server 7.0
Open the DataPump task properties sheet. System tables are unavailable
in the "table/view"
drop-down box, so you will have to type it in manually, [msdb].[dbo].[s
ysdtspackages].
Do the same for the destination, and let the transformations auto-generate.
3 SQL Server 2000
Do not open the DataPump task properties yet. First open Disconnected
Edit from the
package menu. Navigate to your DataPump task and set the
SourceObjectName and
DestinationObjectName properties to [msdb].[dbo].[sysdtspac
kages].
Now open the DataPump task properties and go to the Transformations tab
to allow the
transformations to auto-generate. In SQL Server 2000 you no longer have
the ability to
manually enter a table name for the source or destination object as in SQL
Server 7.0,
but this method allows you to overcome this as described.
4 You can now execute you package.
This will transfer all local packages from the source to the destination
server. You may
however only wish to transfer selected packages. This can easily be
achieved
by using a
SQL Query for the source instead of using sysdtspackages
directly. Some sample queries
are described below.
You can specify the packages to transfer by name:
SELECT * FROM dbo.sysdtspackages
WHERE [name] IN ('MyPackage_Number_1', 'MyPackage_Number_2')
Every time you save a local package a new version is stored. Every version
is an
individual row in sysdtspackages. You may only want to transfer
the latest or current version,
such as when moving between a development and production environment.
SELECT T1.* FROM dbo.sysdtspackages AS T1
INNER JOIN (SELECT [name], [id], MAX([createdate]) AS [createdate]
FROM dbo.sysdtspackages GROUP BY [name], [id]) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]
You may wish to transfer the most recent n versions:
SELECT T1.* FROM dbo.sysdtspackages AS T1
INNER JOIN (SELECT T2.[name] , T2.[id], T2.[createdate]
FROM dbo.sysdtspackages T2
GROUP BY T2.[name], T2.[id], T2.[createdate]
HAVING T2.[createdate] IN (SELECT TOP n T3.[createdate]
FROM dbo.sysdtspackages T3
WHERE T2.[id] = T3.[id]
ORDER BY T3.[createdate] DESC) ) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]
This works quite happily between all current versions of SQL Server,
despite the addition of the
packagetype column in SQL Server 2000. If you don't supply a
value it will be populated with the default of 0.
sysdtspackages is an undocumented system table, and as such
may change in
future versions of SQL Server. Any changes may invalidate this process,
but
in the meantime it works just fine, and is definitely the most efficient
method
of transferring packages.