drop proc testDTSRun
go
create proc testDTSRun
as
DECLARE @intErr int 
DECLARE @oPkgID int
Declare @oGVID int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
EXEC @intErr = master.dbo.sp_OACreate 'DTS.Package', @oPkgID OUTPUT 
if @intErr <> 0 Goto ErrorHandler
EXEC @intErr = master.dbo.sp_OAMethod @oPkgID, 'LoadFromSQLServer', NULL, '(local)',NULL,Null, 256,Null,NULL,NULL, 'TestPackage'
if @intErr <> 0 Goto ErrorHandler
exec @interr = master.dbo.sp_OASetProperty oPkgID, 'DTSGlobalVariables("testGV")', '(local)'
if @intErr <> 0 Goto ErrorHandler
EXEC @intErr = master.dbo.sp_OAMethod @oPkgID, 'Execute'
if @intErr <> 0 Goto ErrorHandler
EXEC @intErr = master.dbo.sp_OAMethod @oPkgID, 'UnInitialize'
if @intErr <> 0 Goto ErrorHandler
exec @interr = master.dbo.sp_OADestroy @oPkgID
if @intErr <> 0 Goto ErrorHandler
exec @interr = master.dbo.sp_OAStop
return
ErrorHandler:
PRINT 'OLE Automation Error Information'
EXEC sp_OAGetErrorInfo @oPkgId, @source OUT, @description OUT
print '  Source: ' + @source
print '  Description: ' + @description