DTS How to...Transfer Packages between Servers

September 24, 2000

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:

    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:

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








  • The Network for Technology Professionals

    Search:

    About Internet.com

    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers