[SQLServer][DTS][存储过程]保存所有的DTS包到指定的路径

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO

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',@rc,@PackageName)???return??end????-- delete old file??select @cmd = 'del ' + @FileName??exec master..xp_cmdshell @cmd,no_output????exec @rc = sp_OAMethod @objPackage,'SaveToStorageFile',@FileName??if @rc <> 0??begin???raiserror('failed to save package rc = %d,@PackageName)???return??end????exec @rc = sp_OADestroy @objPackage?endgoexec s_SavePackages 'c:/'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!