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.