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 JimPackages 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
Subscribe to:
Post Comments (Atom)
 
No comments:
Post a Comment