Выгрузка/загрузка DTS пакетов

Автор: Snake
Прислал:

Способ 1
Процедура на TSQL загружает\выгружает DTSPackage с\на сервера\сервер в\из
указанную\ой дирректорию\ии

CREATE         PROCEDURE dbo.usp_LoadSaveDTSPackage
	@dirname varchar(256) = null,
	@isin bit 
as
	set nocount on
	declare @SQL varchar(256),
			@result int,
			@oPKG int,
			@dtsname varchar(256),
			@LoadStr varchar(256),
			@src varchar(255), 
			@desc varchar(255)

	if @dirname  is not null begin 
		create table #dts (dtsname varchar(256))
		if @isin = 1 begin
			set @SQL = 'dir '+@dirname+'\*.dts'

			insert into #dts exec @result = master..xp_cmdshell @SQL
			if (@result != 0) begin
				raiserror ('Не могу прочесть файлы!', 16, 1)
				return(-1)
			end
			delete from #dts where isnumeric(substring(dtsname,1,1)) = 0 
			update #dts set dtsname = reverse(left(reverse(dtsname),charindex(' ',reverse(dtsname))-1))

		end
		else begin
			insert into #dts select distinct name from msdb..sysdtspackages
		end
		declare dts cursor for
			select dtsname from #dts 
		open dts
		fetch next from dts
		into @dtsname
		while @@fetch_status = 0 begin

			exec @result = sp_OACreate 'DTS.Package', @oPKG OUT
			if @result <> 0	begin
			   exec sp_OAGetErrorInfo @oPKG, @src OUT, @desc OUT 
			   select hr=convert(varbinary(4),@result), Source=@src, description=@desc
			   return (-1)
			end

			if @isin != 1 begin
				set @LoadStr = 'LoadFromSQLServer ("'+@@servername+'",,,256,,,,"'+@dtsname+'")'
				exec @result = sp_OAMethod @oPKG,@LoadStr
				if @result <> 0	begin
				   exec sp_OAGetErrorInfo @oPKG, @src OUT, @desc OUT 
				   select hr=convert(varbinary(4),@result), Source=@src, description=@desc
				   return (-1)
				end
				set @LoadStr = 'SaveToStorageFile ("'+@dirname+'\'+@dtsname+'.dts")'
				exec @result = sp_OAMethod @oPKG,@LoadStr
				if @result <> 0	begin
				   exec sp_OAGetErrorInfo @oPKG, @src OUT, @desc OUT 
				   select hr=convert(varbinary(4),@result), Source=@src, description=@desc
				   return (-1)
				end
			end
			else begin
				set @LoadStr = 'LoadFromStorageFile ("'+@dirname+'\'+@dtsname+'","")'
				exec @result = sp_OAMethod @oPKG,@LoadStr
				if @result <> 0	begin
				   exec sp_OAGetErrorInfo @oPKG, @src OUT, @desc OUT 
				   select hr=convert(varbinary(4),@result), Source=@src, description=@desc
				   return (-1)
				end
				set @LoadStr = 'SaveToSQLServer ("'+@@servername+'",,,256)'
				exec @result = sp_OAMethod @oPKG,@LoadStr
				if @result <> 0	begin
				   exec sp_OAGetErrorInfo @oPKG, @src OUT, @desc OUT 
				   select hr=convert(varbinary(4),@result), Source=@src, description=@desc
				   return (-1)
				end
			end
			exec @result = sp_OADestroy @oPKG
			if @result <> 0	begin
			   exec sp_OAGetErrorInfo @oPKG
			   return (-1)
			end

			fetch next from dts
			into @dtsname
		end
		close dts
		deallocate dts
	end
	else begin 
		return (-1)
	end
	set nocount off
GO

@echo off

if "%DEBUG%"=="1" @echo on

setlocal

SET SVR
SET UID
SET PWD
SET DB
SET TABLE
SET COLUMN
SET PACKAGENAME
SET FILENAME

textcopy /S %SVR% /U %UID% /P %PWD% /D %DB% /T %TABLE% /C %COLUMN% 
/W "where name = '%PACKAGENAME%' and createdate = (select max(createdate) from sysdtspackages where name = '%PACKAGENAME%')" 
/O /F %FILENAME%
endlocal