/*
Процедура загружает\выгружает
DTSPackage с\на сервера\сервер в\из
указанную\ой дирректорию\ии
*/
CREATE PROCEDURE dbo.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 |