/****************************************************************************************/
/* Filename: 		spExecutePKG.sql						*/
/* Description:		Execute DTS Packages via sp_OAxxxx				*/
/* Created:		Darren Green 200006013						*/
/****************************************************************************************/

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

CREATE PROC spExecutePKG
	@Server varchar(255),
	@PkgName varchar(255), 			-- Package Name (Defaults to most recent version)
	@ServerPWD varchar(255) = Null,		-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
	@IntSecurity bit = 0,			-- 0 = SQL Server Security, 1 = Integrated Security
	@PkgPWD varchar(255) = ''		-- Package Password
AS
SET NOCOUNT ON
/*
  Return Values
  - 0 Successfull execution of Package
  - 1 OLE Error
  - 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)

	-- Create a Pkg Object
	EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT 
	IF @hr <> 0
	BEGIN
		PRINT '***  Create Package object failed'
		EXEC sp_displayoaerrorinfo @oPKG, @hr
		RETURN 1
	END	

	-- Evaluate Security and Build LoadFromSQLServer Statement
	IF @IntSecurity = 0 
		SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
	ELSE
		SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
	
	EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

	IF @hr <> 0
	BEGIN
	    PRINT '***  LoadFromSQLServer failed'
	    EXEC sp_displayoaerrorinfo @oPKG , @hr
	    RETURN 1
	END

	-- Execute Pkg
	EXEC @hr = sp_OAMethod @oPKG, 'Execute'
	IF @hr <> 0
	BEGIN
	    PRINT '***  Execute failed'
	    EXEC sp_displayoaerrorinfo @oPKG , @hr
	    RETURN 1
	END

	-- Check Pkg Errors
	EXEC @ret=spDisplayPkgErrors @oPKG

	-- Unitialize the Pkg
	EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
	IF @hr <> 0
	BEGIN
	    PRINT '***  UnInitialize failed'
	    EXEC sp_displayoaerrorinfo @oPKG , @hr
	    RETURN 1
	END

	-- Clean Up
	EXEC @hr = sp_OADestroy @oPKG
	IF @hr <> 0
	BEGIN
		EXEC sp_displayoaerrorinfo @oPKG , @hr
		RETURN 1
	END

	RETURN @ret
GO