Опубликовано: 24 дек 02
Рейтинг:
Рейтинг:
Автор: 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
Способ 2
Сохранить DTS пакеты, перенести их на другой сервер можно при помощи утилиты TEXTCOPY.
@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
Комментарии
https://mult.tv