Copy DTS Package 1

[ecobb]

How would I copy a DTS Package to another SQL Server? Is there some way I can script it or backup and restore it?

Thanks!




Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[dbomrrsm]

I use these to back up my DTS packages regularly - they can then be restored:

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

THEN

CREATE  PROCEDURE BACK_UP_DTS
AS 
DECLARE @strSQL nvarchar (500)
SET @strSQL = 'exec master.dbo.s_SavePackages '+'''M:\DTS_PACKAGES'+(SELECT CONVERT(VARCHAR(10), GETDATE(), 102))+''''
--PRINT @strSQL
EXEC sp_executesql @strSQL
GO

[blue]DBomrrsm[/blue]

 

[ecobb]

Cool, thanks! I'm assuming these would need to be created in the Master database. How would you go about restoring a DTS package backup? Sorry, I've never fooled with DTS much.

Thanks!




Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[dbomrrsm]

here is a thread that explains how to restore the DTS packages.

thread961-902113

hope it helps.

[blue]DBomrrsm[/blue]

 

[ecobb]

Thanks DBomrrsm, that's exactly what I needed!



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher