DTS How to…Transfer Packages between Servers

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:

  • Create a New Package

  • Add SQL Server Connections for the Source and Destination Servers, ensuring you set the database to msdb.

  • Link the two with a Transformation (Data Pump) Task as shown below

    Connections with Transformation Task

    Source Data Pump Destination

  • Now double-click the Transformation Task to set it’s properties:

    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:

    Source for Transformation

    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
     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
      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.

  • Get the Free Newsletter!

    Subscribe to Cloud Insider for top news, trends & analysis

    Latest Articles