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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_LoadPackageToServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 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