Here's two stored procedures that will allow you to run a DTS package-it's
the method I use. You can call the SP from either <CFQUERY> or <CFTOREDPROC>
tags. The spExecuteDTS is the only SP you use, but the second SP is used by
spExecuteDTS.
-Dan
/**********************************************************
Example:
<CFSTOREDPROC PROCEDURE="spExecuteDTS" DATASOURCE="OPLIN2" DEBUG="NO">
<CFPROCRESULT NAME="ImportData">
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="@Server"
VALUE="127.0.0.1" NULL="NO">
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="@PkgName"
VALUE="DTS__ImportOldPages" NULL="NO">
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR"
DBVARNAME="@ServerPWD"
VALUE="*******" NULL="NO">
</CFSTOREDPROC>
**********************************************************/
-- spExecuteDTS Stored Procedure
CREATE PROC spExecuteDTS
@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
-- spDisplayPKGErrors Stored Procedure
CREATE PROC spDisplayPKGErrors
@oPkg As integer
AS
SET NOCOUNT ON
DECLARE @StepCount int
DECLARE @Steps int
DECLARE @Step int
DECLARE @StepResult int
DECLARE @oPkgResult int
DECLARE @hr int
DECLARE @StepName varchar(255)
DECLARE @StepDescription varchar(255)
IF OBJECT_ID('tempdb..#PkgResult') IS NOT NULL
DROP TABLE #PkgResult
CREATE TABLE #PkgResult
(
StepName varchar(255) NOT NULL,
StepDescription varchar(255) NOT NULL,
Result bit NOT NULL
)
SELECT @oPkgResult = 0
EXEC @hr = sp_OAGetProperty @oPkg, 'Steps', @Steps OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get steps'
EXEC sp_displayoaerrorinfo @oPkg , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Steps, 'Count', @StepCount OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get number of steps'
EXEC sp_displayoaerrorinfo @Steps , @hr
RETURN 1
END
WHILE @StepCount > 0
BEGIN
EXEC @hr = sp_OAGetProperty @Steps, 'Item', @Step OUTPUT, @StepCount
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step'
EXEC sp_displayoaerrorinfo @Steps , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult
OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get ExecutionResult'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Step, 'Name', @StepName OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step Name'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Step, 'Description', @StepDescription
OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step Description'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END
INSERT #PkgResult VALUES(@StepName, @StepDescription, @StepResult)
PRINT 'Step ' + @StepName + ' (' + @StepDescription + ') ' + CASE WHEN
@StepResult = 0 THEN 'Succeeded' ELSE 'Failed' END
SELECT @StepCount = @StepCount - 1
SELECT @oPkgResult = @oPkgResult + @StepResult
END
SELECT * FROM #PkgResult
IF @oPkgResult > 0
BEGIN
PRINT 'Package had ' + CAST(@oPkgResult as varchar) + ' failed step(s)'
RETURN 9
END
ELSE
BEGIN
PRINT 'Packge Succeeded'
RETURN 0
END
-----Original Message-----
From: Percy E Perez [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 16, 2001 1:14 PM
To: CF-Talk
Subject: Running a MS SQL Server DTS Package from ColdFusion
Anyone has any suggestions on how to run a DTS package from a coldfusion
page?
I found article# Q252987 on technet on how to do this using ASP, but I
would rather implemented in ColdFusion. Thank you for any suggestions.
Percy E Perez
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
