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

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

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


create proc sp_displayoaerrorinfo
   @object as int 
as
Declare @hr int
DECLARE @output varchar( 255 )
DECLARE @source varchar( 255 )
DECLARE @description varchar( 255 )

   PRINT 'OLE Automation Error Information'

   EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
   IF @hr =  0 
   BEGIN
       SELECT @output = '  Source: ' + @source
       PRINT @output
       SELECT @output = '  Description: ' + @description
       PRINT @output
   END

   ELSE
   BEGIN
       PRINT '  sp_OAGetErrorInfo failed.'
       RETURN
   END
GO


create proc sp_displaypkgerrors
   @pkg as int 
as
declare @numsteps int
declare @steps int
declare @step int
declare @stepresult int
declare @pkgresult int
declare @hr int

   select @pkgresult =  0 

   EXEC @hr = sp_OAGetProperty @pkg, 'Steps', @steps OUTPUT
   IF @hr <>  0 
   BEGIN
       print 'Unable to get steps'
       EXEC sp_displayoaerrorinfo @pkg  --, @hr
 
       RETURN
   END

   EXEC @hr = sp_OAGetProperty @steps, 'Count', @numsteps OUTPUT
   IF @hr <>  0 
   BEGIN
       print 'Unable to get number of steps'
       EXEC sp_displayoaerrorinfo @steps  --, @hr
 
       RETURN
   END
   
   while @numsteps >  0  
   Begin       
       EXEC @hr = sp_OAGetProperty @steps, 'Item', @step OUTPUT, @numsteps
       IF @hr <>  0 
       BEGIN
           print 'Unable to get step'
           EXEC sp_displayoaerrorinfo @steps  --, @hr
 
           RETURN
       END
   
       EXEC @hr = sp_OAGetProperty @step, 'ExecutionResult', @stepresult OUTPUT
       IF @hr <>  0 
       BEGIN
           print 'Unable to get ExecutionResult'
           EXEC sp_displayoaerrorinfo @step  --, @hr
 
           RETURN
       END

       select @numsteps = @numsteps -  1 
       select @pkgresult = @pkgresult + @stepresult
   end
   
   if @pkgresult >  0 
       print 'Package had ' + cast(@pkgresult as varchar) + ' failed step(s)'
   else 
       print 'Packge Succeeded'

GO


create proc sp_executepackage
   @packagename varchar( 255 ),   --package name, gets most recent version
 
   @userpwd varchar( 255 ) = '',    --login pwd
 
   @intsecurity bit =  1 ,        --use non-zero to indicate integrated security
 
   @pkgPwd varchar( 255 ) = '',    --package password
 
   @SQLServer varchar( 255 ) = 'MySQLserver',
   @SQLuser varchar( 255 ) = 'domain\login'
as
declare @hr int
declare @object int

    --create a package object 
 
   EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT 
   if @hr <>  0 
   Begin
       EXEC sp_displayoaerrorinfo @object  --, @hr
 
       RETURN
   end 

    --load the package (ADD integrated security support)
 
   declare @svr varchar( 15 )
   declare @login varchar( 15 )
 --   select @login = SUSER_NAME()
 
   select @login = @SQLuser
 --   select @svr = HOST_NAME()
 
   select @svr = @SQLServer
   declare @flag int
   select @flag =  0 

   if @intsecurity =  0  
       if @userpwd = Null
           EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, 
@ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, 
@Flags=@flag, @PackagePassword = @pkgPwd
            -- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
 
       else
           EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, 
@ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, 
@Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
   else
   begin
       select @flag =  256 
       EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, 
@ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = 
@pkgPwd
   end

   IF @hr <>  0 
   BEGIN
       print 'LoadFromSQLServer failed'
       EXEC sp_displayoaerrorinfo @object  --, @hr
 
       RETURN
   END

    --execute it
 
   EXEC @hr = sp_OAMethod @object, 'Execute'
   IF @hr <>  0 
   BEGIN
       print 'Execute failed'
       EXEC sp_displayoaerrorinfo @object  --, @hr
 
       RETURN
   END

    --return the step errors as a recordset
 
   exec sp_displaypkgerrors @object

    -- unitialize the package
 
   EXEC @hr = sp_OAMethod @object, 'UnInitialize'
   IF @hr <>  0 
   BEGIN
       print 'UnInitialize failed'
       EXEC sp_displayoaerrorinfo @object  --, @hr
 
       RETURN
   END

    -- release the package object
 
   EXEC @hr = sp_OADestroy @object
   if @hr <>  0 
   Begin
       EXEC sp_displayoaerrorinfo @object  --, @hr
 
       RETURN
   end 
GO