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:
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]
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).
Let DTS automatically generate the default transformations- simple Copy Column transformations between matching columns.
Click OK, and Execute the package.