Running A DTS from CF

I just got asked how to run a SQL Server DTS from Coldfusion and here is the answer:

You will need to:

1. Write and test your DTS
2. Write a stored procedure which will run the DTS
3. Write the code in CF to call the SP, this code can then be called directly by the user or set up a scheduled task

Something to remember, DTS packages can A LONG TIME to run and your code should account for that fact by setting your timeouts high enough or preventing the user from resubmitting the form and setting off another DTS

Below is an example of a SP to call the DTS

/*Run DTS Package stored procedure*/
CREATE PROCEDURE [dbo].[sp_AdRunDTSPackageOnServer]
@nID int

AS

DECLARE @DTSPackageObject int
DECLARE @HRESULT int
DECLARE @property varchar(255)
DECLARE @return int
DECLARE @ErrDescrip varchar(255)
DECLARE @ErrSource varchar(30)
DECLARE @ErrHelpId int
DECLARE @ErrHFile varchar(255)
DECLARE @ErrMsg varchar(255)
DECLARE @sDTSPackagePath varchar(1000)
DECLARE @sDTSSpecialUser varchar(50)

SELECT @ErrMsg = 'Error running DTS package'

--NOTE: remember to give the account running this procedure access to the sp_OA system stored procedures

SELECT @sDTSPackagePath = DTSPath , @sDTSSpecialUser = DTSSpecialUser
FROM T_AdDTSPackageSetup
WHERE
DTSID = @nID

IF @sDTSPackagePath is NULL

BEGIN

SELECT @ErrMsg = 'Error DTS package not found'
RAISERROR (@ErrMsg,11,1)
RETURN

END