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