Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Copying DTS Packages to new server

    Is there any way/an easy way to copy dts packages from one server to another? I cant seem to find a way to script them, is there any other way other than recreating them on the new server?
    Regards Jim

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Packages are saved in msdb..sysdtspackages - you just need to copy the relevant rows from that table.
    Note that sysdtspackages is undocumented so this may not work in the future.

    If you prefer this is an SP to load all packages and save them to files - just change the save to save to a server. This will lose the graphics as there is no context for it and no way at present to load the stream.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[s_SavePackages]
    GO

    Create procedure s_SavePackages
    @Path varchar(128)
    as
    /*

    */

    set nocount on

    declare @objPackage int
    declare @PackageName varchar(128)
    declare @rc int
    declare @ServerName varchar(128)
    declare @FileName varchar(128)
    declare @FilePath varchar(128)
    declare @cmd varchar(2000)

    select @ServerName = @@ServerName ,
    @FilePath = @Path

    if right(@Path,1) <> '\'
    begin
    select @Path = @Path + '\'
    end

    -- create output directory - will fail if already exists but ...
    select @cmd = 'mkdir ' + @FilePath
    exec master..xp_cmdshell @cmd


    create table #packages (PackageName varchar(128))
    insert #packages
    (PackageName)
    select distinct name
    from msdb..sysdtspackages

    select @PackageName = ''
    while @PackageName < (select max(PackageName) from #packages)
    begin
    select @PackageName = min(PackageName) from #packages where PackageName > @PackageName

    select @FileName = @FilePath + @PackageName + '.dts'

    exec @rc = sp_OACreate 'DTS.Package', @objPackage output
    if @rc <> 0
    begin
    raiserror('failed to create package rc = %d', 16, -1, @rc)
    return
    end

    exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
    @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
    if @rc <> 0
    begin
    raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)
    return
    end

    -- delete old file
    select @cmd = 'del ' + @FileName
    exec master..xp_cmdshell @cmd, no_output

    exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName
    if @rc <> 0
    begin
    raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)
    return
    end

    exec @rc = sp_OADestroy @objPackage
    end
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •