6 How to Transfer Packages between Servers
There are two ways to transfer packages between servers.
The simple method
is to save packages as COM Structured Storage Files, copy them to the new
location, then open and save them to the new server. Whilst this works, it is very tedious when you have a lot of packages.
You can automate much of this work by using my DTSBackup utility, but there is still a quicker method as outlined below.
Local Packages, as opposed to Repository packages, are stored in the sysdtspackages table on the msdb database.
It therefore follows that you can transfer packages between servers, by transferring data between the respective sysdtspackages tables.
Here is a simple guide on transferring packages directly between servers:
| Source | Data Pump | Destination |
Source 
For a simple transfer set the Table/View to [msdb].[dbo].[sysdtspackages].
System tables are not available in the drop-down box, so you will have to type it in manually, as shown below:
For SQL Server 2000 this is not an option, as you cannot manually type the table name.
Once you have added the DataPump task, but before you open the double-click to set properties,
use Disconnected Edit to set both the DestinationObjectName and SourceObjectName properties
to [msdb].[dbo].[sysdtspackages]. Then open the properties sheet and
select the Transformations sheet to auto-build the transformations correctly.
Instead of transferring all versions, of all packages, you can use SQL Query to limit the rows transferred.
One instance where this is useful is when you have been developing packages locally. Each time you save server packages, a new version is created, each of which will be a row in sysdtspackages.
Here are some samples scripts to limit the number of versions you transfer:
| – Select the latest version only | |
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] |
|
– Transfer the most recent X versions, where X is the number of versions to transfer |
|
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 X 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]
|
Destination
The destination Table is always [msdb].[dbo].[sysdtspackages]. As with the Source,
system tables are unavailable in the drop-down box, so you will have to type it in manually. (See Source above for SQL 2000 work around).
Transformations
Let DTS automatically generate the default transformations- simple Copy Column transformations between matching columns.
Click OK, and Execute the package.
