/****** Object:  Stored Procedure dbo.spExecuteDTSPackage    Script Date: 2/18/2003 5:27:58 AM ******/
CREATE PROCEDURE [dbo].[spExecuteDTSPackage]
@in_varPackageName Varchar(100),
@in_varServerName varchar(20),
@out_chrErrorType Char(3) = 'SYS' OUTPUT,
@out_intErrorCode Int =0 OUTPUT
AS
DECLARE @object int,
@hr int,
@src varchar(255),
@desc varchar(255),
@val as varchar(100)

-- LOAD A VARIABLE WITH A NEEDED PROCEDURE CALL..
SELECT @val = 'LoadFromSQLServer(' + @in_varServerName + ', "", "", 256, , , , ' + @in_varPackageName + ')';
-- CREATE THE OBJECT.
EXEC @hr = master..sp_OACreate 'DTS.package', @object OUT;
IF @hr <> 0
BEGIN
      -- Trapping error if object not created
        EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
        SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
        RETURN
END
-- GET THE DTS PACKAGE REFRENCE.
EXEC @hr = master..sp_OAMethod @object, @Val,  NULL;
IF @hr <> 0
BEGIN
       -- Trapping error if Execute failed
      EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
       SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END
-- EXECUTE THE DTS PACKAGE.
EXEC @hr = master..sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
       -- Trapping error if Execute failed
      EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
       SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END
GO