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.