[sqlserver][dts][存储过程]将dts包文件加载到服务器上_数据库技巧

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

Create procedure s_LoadPackageToServer
@PackageName varchar(128) ,
@FileName varchar(500) ,
@Username varchar(100) ,
@Password varchar(100)
as
/*
exec s_LoadPackageToServer
  @PackageName = mypackage ,
  @FileName = c:\dtspckgs\mypackage.dts ,
  @Username = sa ,
  @Password = pwd
*/
declare @objPackage int
declare @rc int

  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, LoadFromStorageFile , null,
   @UncFile = @FileName, @password = null
  if @rc <> 0
  begin
   raiserror(failed to load package rc = %d, package = %s, 16, -1, @rc, @PackageName)
   return
  end
 
  exec @rc = sp_OAMethod @objPackage, SaveToSQLServerAs , null,
   @NewName = @PackageName, @ServerName = @@ServerName, @ServerUserName = @Username, @ServerPassword = @Password
  if @rc <> 0
  begin
   raiserror(failed to load package rc = %d, package = %s, 16, -1, @rc, @PackageName)
   return
  end
go

exec s_LoadPackageToServer
   mypackage ,
  c:\DTS_AN2CP_DIC.dts ,
   sa ,
  sa

http://qwerttyy.cnblogs.com/archive/2006/05/26/409663.html