SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 /*
Процедура загружает\выгружает 
DTSPackage с\на сервера\сервер в\из
указанную\ой дирректорию\ии
-- у сервера должен быть сброшен флаг use NT fibers
*/ 
ALTER          PROCEDURE sp_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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO