[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:dtspckgsmypackage.dts' , @Username = 'sa' , @Password = 'pwd'*/declare @objPackage intdeclare @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 endgo

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

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