Execute DTS package using OA procedures

/****** Object:  Stored Procedure dbo.usp_ExecDTSPackage    Script Date: 22/10/2002 11:20:24 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.usp_ExecDTSPackage') and sysstat & 0xf = 4)
	drop procedure dbo.usp_ExecDTSPackage
GO


CREATE PROC dbo.usp_ExecDTSPackage
	@vcrSrvr sysname -- the server to connect to
	, @bitIntSec bit = 0 -- 0 = SQL Server Security, 1 = Integrated Security
	, @vcrUID sysname = NULL -- Server userid if using SQL Security
	, @vcrPWD sysname = NULL -- Server Password if using SQL Security
	, @vcrPkgName sysname -- Package Name (Defaults to most recent version)
	, @vcrPkgPWD sysname = NULL	-- Package Password
AS
BEGIN -- procedure
	-- set connection defaults
	SET NOCOUNT ON
	SET DATEFORMAT dmy

	-- Declare local variables
	DECLARE @intErr int -- Execution result
	DECLARE @oPkgID int -- pointer to package object
	DECLARE @vcrCmd varchar(1000) -- 
	DECLARE @vcrMsg varchar(500) -- general purpose message string
	DECLARE @vcrProcName sysname -- name of executing procedure
	DECLARE @dtmRunDate datetime -- date/time of execution
	DECLARE @chrHrHex char(10) -- OA Hex Error result
	DECLARE @vcrSource varchar(255) -- OA error source
	DECLARE @vcrDesc varchar(255) -- OA error description

	-- Initialise local variables
	SET @intErr = 0
	SET @oPkgID = 0
	SET @vcrCmd = ''
	SET @vcrMsg = ''
	SET @vcrProcName = OBJECT_NAME(@@PROCID)
	SET @dtmRunDate = GetDate()
	SET @chrHrHex = ''
	SET @vcrSource = ''
	SET @vcrDesc = ''

	-- Create a Package Object, this creates the @oPkgID pointer
	EXEC @intErr = master.dbo.sp_OACreate 'DTS.Package', @oPkgID OUTPUT 
	IF @intErr = 0
	BEGIN -- Object created
		-- Evaluate Security and Build LoadFromSQLServer Statement
		IF @bitIntSec = 0 
		BEGIN -- build SQL security load statement
			SET @vcrCmd = 'LoadFromSQLServer("' + @vcrSrvr + '", "' + @vcrUID
			SET @vcrCmd = @vcrCmd  + '", "' + @vcrPWD  + '", 0,'
		END -- build SQL security load statement
		ELSE
		BEGIN -- build Integrated security load statment
			-- The value of 256 is from the DTSSQLServerStorageFlags constants 
			-- and indicates that Windows authentication is used
			SET @vcrCmd = 'LoadFromSQLServer("' + @vcrSrvr +'", , , 256,' 
		END -- build Integrated security load statment

		-- add package password and name to execution string
		IF @vcrPkgPWD IS NULL
			SET @vcrCmd = @vcrCmd + ' , , , "' + @vcrPkgName + '")'
		ELSE
			SET @vcrCmd = @vcrCmd + ' "' + @vcrPkgPWD + '", , , "' + @vcrPkgName + '")'

		-- load package definition
		EXEC @intErr = master.dbo.sp_OAMethod @oPkgID, @vcrCmd, NULL
		IF @intErr = 0
		BEGIN -- package loaded
			-- Execute the Package
			EXEC @intErr = master.dbo.sp_OAMethod @oPkgID, 'Execute'
			IF @intErr <> 0	-- set log message
				Set @vcrMsg = 'Package Execution Failed -'

			-- Unitialise the Package
			EXEC @intErr = master.dbo.sp_OAMethod @oPkgID, 'UnInitialize'
			IF @intErr <> 0	-- set log message
				SET @vcrMsg = 'Package Uninitialisation Failed -'
	
		END -- package loaded
		ELSE -- set log message
			SET @vcrMsg = 'LoadFromSQLServer Failed -'

		-- remove the object we created
		EXEC @intErr = master.dbo.sp_OADestroy @oPkgID
		IF @intErr <> 0		-- set log message
			SET @vcrMsg = 'Object Not Removed -'
	END -- Object created
	ELSE -- set log message
		SET @vcrMsg = 'Create Object failed -'

	-- If error occurred build error message and log it
	IF @intErr <> 0
	BEGIN -- Error > 0
		-- OA failure so determine error code and description
		SELECT @chrHrHex = dbo.udf_varBinary2Hex(@intErr)
		EXEC master.dbo.sp_OAGetErrorInfo @oPkgID, @vcrSource OUTPUT, @vcrDesc OUTPUT
		-- Append OA Error text to passed message text
		Set @vcrMsg = @vcrMsg + ' PkgID: ' + COALESCE ( CAST(@oPkgID as varchar(30)), '' )
		SET @vcrMsg = @vcrMsg + ' HResult:' + COALESCE ( @chrHrHex, '' )
		SET @vcrMsg = @vcrMsg + ' Source:' + COALESCE ( @vcrSource, '' )
		SET @vcrMsg = @vcrMsg + ' Description:' + COALESCE ( @vcrDesc, '' )
		RAISERROR (@vcrMsg, 16, 1) WITH LOG
	END -- Error > 0

	-- return completion code to caller
	RETURN ( @intErr )

END -- procedure

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_varBinary2Hex]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_varBinary2Hex]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE FUNCTION dbo.udf_varBinary2Hex (
/*************************************************************************
--FILENAME: 
--	archives\Datamart\Dev\Functions\dbo.udf_varBinary2Hex.sql
--CURRENT VERSION:
--	Revision:   1.0 
--	Date:   Oct 18 2002 10:01:38 
--SQL SERVER OBJECT NAME: 
--	dbo.udf_varBinary2Hex.sql
--PURPOSE:
--	Convert binary value into Hex string.
--	Used for reporting error codes from OA stored procedures.
--ACTIONS:
--	Loop through binary input string one bit at time. Convert each bit into equivalent
--	hex code. Concatenate hex codes together to provide final string
--INPUTS:
--	@binValue - varbinary - Binary value to convert
--OUTPUTS:
--	@vcrHexValue varchar(255) - Hex string to return
*************************************************************************/	@binValue varbinary(255)
)
RETURNS varchar(255)
AS

BEGIN
	DECLARE @intLoop int -- loop counter
	DECLARE @intParmLen int -- length of passed value
	DECLARE @chrHexStr char(16) -- constant string of Hex characters

	DECLARE @intSingleByte int -- single byte from binary value
	DECLARE @intFirstBit int -- first bit of binary value
	DECLARE @intSecondBit int -- second bit of binary value
	DECLARE @vcrHexValue varchar(255) -- Hex string that is returned
	
	-- initialise variables
	SELECT @vcrHexValue = '0x'
	SELECT @intLoop = 1
	SELECT @intParmLen = DATALENGTH ( @binValue )
	SELECT @chrHexStr = '0123456789ABCDEF'

	WHILE ( @intLoop <= @intParmLen )
	BEGIN
		-- reinitialise pointers
		SET @intSingleByte = 0
		SET @intFirstBit = 0
		SET @intSecondBit = 0
		-- get bit pointers from binary value
		SET @intSingleByte = CONVERT ( int, SUBSTRING ( @binValue, @intLoop, 1 ) )
		-- get base16 number
		SET @intFirstBit = FLOOR ( @intSingleByte / 16 )
		SET @intSecondBit = @intSingleByte - ( @intFirstBit * 16 )
		-- concatenate Hex strings values based on bit value
		SET @vcrHexValue = @vcrHexValue + SUBSTRING ( @chrHexStr, @intFirstBit + 1, 1 ) 
		SET @vcrHexValue = @vcrHexValue + SUBSTRING ( @chrHexStr, @intSecondBit + 1, 1 )

		-- increment loop counter
		SET @intLoop = @intLoop + 1
	END
	
	RETURN ( @vcrHexValue )

END

GO