Here's a sample stored procedure. In this one I'm actually passing a global
variable (called @ImportFileName ) into the DTS package:
CREATE PROC RunDTSPackage
@ImportFileName varchar(5000)
AS
DECLARE @sSQL varchar(7000)
SET @sSQL = 'DTSRUN /S myServer /E /N myPackage /A @ImportFileName:8='
SET @sSQL = @sSQL + @ImportFileName
Exec xp_cmdshell @sSQL
Where myServer is the name of the server and myPackage is the name of the
package. The /E flag tells the server to use a trusted connection - that
way I don't need to include a userid and password in the file (or pass one
in).
The only gotcha is setting your server up so it will allow ColdFusion to
call a stored procedure, which then calls xp_cmdshell. I had to do a bit of
tinkering to get it to work. I cannot actually remember exactly what I did,
but if you need that info I can probably find it.
Hope that helps,
Bob
-----Original Message-----
From: Marcus [mailto:[EMAIL PROTECTED]]
Sent: April 24, 2001 12:06 PM
To: Fusebox
Subject: RE: Executing DTS Packages Via CF
Can you expound on this a bit? I'm having troubles getting an import dts to
run correctly.
Marcus
> You can also use a Stored Procedure that calls DTSRUN using xp_cmdshell.
> This works well when CF and SQL are on separate machines.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
