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

概述[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_SavePa [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:/'

总结

以上是内存溢出为你收集整理的[SQLServer][DTS][存储过程]保存所有的DTS包到指定的路径全部内容，希望文章能够帮你解决[SQLServer][DTS][存储过程]保存所有的DTS包到指定的路径所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。