Running SQL Server DTS package from ColdFusion

R_Hos is correct. Using his example for setting up the stored procedure, the coldfusion code to run it would be like:

<cfquery name="RunDTSPkg" datasource="YourDSN">
exec sp_AdRunDTSPackageOnServer
</cfquery>

Sbennett,

thanks for the CF side, too many things going on at once around the office today =)

~R

The ColdFusion Muse mused about this about a year ago.
http://mkruger.cfwebtools.com/index.cfm?mode=alias&alias=cf.dts.revised

He used createObject("COM","DTS.Package")
that runs the DTS package directly, and it actually works!

Whatdya think?

I found a cfdj article from 10/2003 at
http://pdf.sys-con.com/ColdFusion/born.pdf
and this guy
http://mandalapu.blog.com/1587529/
showed how to include parameters.

It will probably depend on what your DTS is trying to accomplish and what your requirements are.  I don't think I've ever set one up that would require parameters...

The biggest reason i run my DTS via a stored procedure is i try very hard to maintain a MVC architecture (i want all actions to the database to be run from the database so that it can stand by itself without the web application interface); should my apps ever have to be ported to another technology (ASP, java, etc) i want to make sure that the separate layers will still work independently.   Its a bit more work but its well worth it for the increased modularity and stability that it offers.

~R

R_Hos,

I found some code similar to yours at: http://techrepublic.com.com/5110-6313-5164098.html.
Your example only showed a SELECT stmt and didn't show all the calls to sp_OA.
I'll probably have trouble getting rights to the sp_OA system stored procedures.


CREATE  TABLE [dbo].[T_AdDTSPackageSetup] (
       [DTSID] [int] IDENTITY (1, 1) NOT NULL ,
       [DTSDescrip] [varchar] (50) NOT NULL ,
       [DTSPath] [varchar] (1000)  NOT NULL ,
       [DTSSpecialUser] [varchar] (50) NULL
)
 
ALTER TABLE [dbo].[T_AdDTSPackageSetup] WITH NOCHECK ADD
       CONSTRAINT [PK_T_AdDTSPackageSetup] PRIMARY KEY  CLUSTERED
       (
              [DTSID]
       )  ON [PRIMARY]
GO
 
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
 
 
/*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
--lightweight security and/or safety
IF NOT @sDTSSpecialUser  is NULL
BEGIN
       IF NOT @sDTSSpecialUser = CURRENT_USER
       BEGIN
              SELECT @ErrMsg = 'Error you cannot run this ID ' + CURRENT_USER
              RAISERROR (@ErrMsg,11,1)
              RETURN
       END
END
 
 
 
-- Create a DTS Package object
EXEC @HRESULT = sp_OACreate 'DTS.Package', @DTSPackageObject OUTPUT
IF @HRESULT <> 0
BEGIN
       EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
       RAISERROR (@ErrMsg,11,1)
       RETURN
END
 
 
-- Call the load filefromstorage method on the DTS Package object pass the path
EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'LoadFromStorageFile', NULL,@sDTSPackagePath, ''
IF @HRESULT <> 0
BEGIN
       EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT,@ErrDescrip OUTPUT
       RAISERROR ( @ErrMsg,11,1)
       RETURN
END
 
 
-- Set the FailOnError property to true
EXEC @HRESULT = sp_OASetProperty @DTSPackageObject, 'FailOnError', -1 --Set to true
IF @HRESULT <> 0
BEGIN
       EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
       RAISERROR ( @ErrMsg,11,1)
       RETURN
END
 
-- Call the EXECute method
EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'EXECute', NULL
IF @HRESULT <> 0
BEGIN
       EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
       RAISERROR ( @ErrMsg,11,1)
       RETURN
END
 
--remove the object from memory
EXEC sp_OADestroy @DTSPackageObject
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 

I decided not to use DTS.