Stored Proc To Run A DTS Package...???

Good day all! :cool:

I have a DTS package that consists of 2 tasks, one empties a table(1) to receive data from a transformation task(2)...

I found 'code' for a stored procedure to run a DTS package.. problem is that it is only running the first task... I need it to run the whole she-bang!

Anyone know how to do this?

Here is the stored proc:
/************************************************** **************************************/
/* 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