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