ornll
新新人类
帖子
42
精华
0
无忧币 160
积分 130
阅读权限 20
|
发表于:2007-12-2 23:01
标题：将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'
|
千里之外，传递你对震灾人民的关怀 |
|
| 诚征版主 | 版主堂 | 意见建议 | 大史记 | 论坛地图