Richard:
I have recently tackled the same task for a project that I was working
on. Mind you, my usage was with SQL Server 2000, so any
incompatibilities (of which I believe there are none) with 7.0 are not
accounted for here.
First of all, DTS packages can only be called via command prompt using
the DTSRun utility that comes with SQL Server. To execute this through
CF, you can leverage an Extended Stored Procedure that comes with SQL
Server, called xp_cmdshell. Since it's a stored procedure, it can be
invoked via CFSTOREDPROC or even through CFQUERY. Keep in mind that
only SQL Server logins with the sysadmin roll can execute Extended
Stored Procs.
My need was to use DTS to import data from an Excel file uploaded by the
user into SQL Server, and perform some manipulation to that data. I
wrote my own stored procedure to invoke the extended stored procedure,
which then ran the DTS Run Command:
Stored Proc --invokes---> xp_cmdShell --invokes---> DTSRun
I then pass in the necessary parameters for the DTSRun utility into my
stored proc, as shown in the below example:
-----------------------------------------------------------
BEGIN MY STORED PROC
-----------------------------------------------------------
CREATE PROC sp_Survey_import
@ImportFilePath nvarchar(100),
@DTSName nvarchar(20),
@server nvarchar(30),
@username nvarchar(15),
@password nvarchar(15)
AS
DECLARE @dtsScript nvarchar(100);
SET @dtsScript = "dtsrun /n " + @DTSName + " /s " + @server + "
/u " + @username + " /p " + @password
PRINT @DTSName
EXEC master.dbo.xp_cmdshell @dtsScript
GO
-----------------------------------------------------------
END MY STORED PROC
-----------------------------------------------------------
The import path must be publicly accessible to the Database server,
since that is where the procedure is invoked.
If you need CF to handle DTS, this is the best method that I have found.
--Paul
Paul W. Wille [EMAIL PROTECTED]
--------------------------------------------------
Certified Advanced ColdFusion 5.0 Developer
--------------------------------------------------
ISITE Design, Inc. - Solutions Architect
www.isitedesign.com
503.221.9860 x110
888.269.9103
503.221.9865 fax
-----Original Message-----
From: Richard Colman [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 16, 2001 11:32 AM
To: CF-Talk
Subject: SQL-Server DTS through CF
I would like to be able to import an ACCESS data table into SQL-Server
2000 (using DTS?), and do a reverse export, under CF control. This needs
to happen every month. Does anyone know of any documentation or sample
code for this kind of task? Any pointers would be much appreciated.
Thank you.
Rick Colman
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
