Joel,
Here is some code from an article written by Simon Horwith. I found it
awhile back and the code works great, it calls a DTS package from CF code.
I have tried it out and it works great.
<p>This article shows how to execute SQL Server DTS packages from
ColdFusion. Thanks goes out to Simon Horwith at Fig Leaf! </p>
<p>Default Syntax to Create DTS Object:</p>
<p><CFOBJECT TYPE="COM" NAME="objDTS" CLASS="DTS.Package" ACTION
="CREATE"></p>
<p>There are 3 primary locations DTS packages are generally loaded
from:</p>
<p>From SQL Server, from Storage File, or from repository. There are 3
different primary
methods that can be executed, one for each of these DTS locations.</p>
<p>LoadFromSQLServer Syntax:</p>
<p>ObjectName.LoadFromSQLServer("ServerName",
"ServerUserName",
"ServerPassword",
"Flags",
"PackagePassword",
"PackageGuid",
"PackageVersionGuid",
"PackageName",
"pVarPersistStgOfHost")</p>
<p>NOTE: If you want to retrieve the PackageGuid, PackageVersionGuid,
PackageName from the
database, run the "sp_enum_dtspackages" system stored procedure. It returns
the following
variables:</p>
<p>name, id, versionid, description, createdate, owner, size,
packagedata, isowner </p>
<p>LoadFromStorageFile Syntax:</p>
<p>ObjectName.LoadFromStorageFile("UNCFile",
"Password",
"PackageID",
"VersionID",
"Name",
"pVarPersistStgOfHost")</p>
<p>LoadFromRepository Syntax:</p>
<p>ObjectName.LoadFromRepository("RepositoryServerName",
"RepositoryDatabaseName",
"RepositoryUserName",
"RepositoryUserPassword",
"PackageID",
"VersionID",
"PackageName",
"Flags",
"pVarPersistStgOfHost")</p>
<p>This is sample code to execute a (SQLServer) DTS from ColdFusion
("objDTS" is the
Object I created, server is "shorwith", username is "sa" with no password,
"test" is
the name of the DTS):
</p>
<CFTRY>
<CFOBJECT TYPE="COM" NAME="objDTS" CLASS="DTS.Package" ACTION="CREATE">
<CFCATCH TYPE = "Object">
<CFSET error_message = "The DTS Package Object Could Not Be
Created.">
</CFCATCH>
</CFTRY>
<CFTRY>
<CFSET r = objDTS.LoadfromSQLServer
("wtg","sa","",0,"","","","homes4sale_wtg","")>
<CFCATCH>
<CFSET error_message = "The DTS Package Could Not Be Loaded From
the SQL Server at this time.">
</CFCATCH>
</CFTRY>
<CFIF IsDefined("error_message")>
<CFOUTPUT>#error_message#</CFOUTPUT>
</CFIF>
<CFSET p = objDTS.Execute()>
SQL-List@houseof
fusion.com (SQL) To: <[EMAIL PROTECTED]>
cc:
04/01/2002 04:53 Subject: SQL-List V1 #141
PM
Please respond
to SQL-List
SQL-List Mon, 1 Apr 2002 Volume 1 : Number
141
In this issue:
RE: calling a DTS package
Re: calling a DTS package
----------------------------------------------------------------------
Date: Mon, 1 Apr 2002 13:00:14 -0700
From: "Correa, Orlando (ITSC)" <[EMAIL PROTECTED]>
Subject: RE: calling a DTS package
Message-ID: <E7A13F08405DD21193EE00A0C9C763310287F988@HQW_S_MAIL2>
http://www.sqldts.com/article.asp?nav=1,6,207,7
-----Original Message-----
From: Joel Firestone [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 01, 2002 12:44 PM
To: SQL
Subject: calling a DTS package
Hey all:
I'm new to DTS, and I'm wondering how I can call one from my Cold Fusion
template/SQL 7 server? Do I call it from within a SP? If so, an example
would be great.
TIA.
Joel F.
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
------------------------------
Date: Mon, 1 Apr 2002 15:07:19 -0500
From: "Joel Firestone" <[EMAIL PROTECTED]>
Subject: Re: calling a DTS package
Message-ID: <01dd01c1d9b8$d6a768f0$c80511ac@joelf>
Thanks.
----- Original Message -----
From: "Correa, Orlando (ITSC)" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Monday, April 01, 2002 3:00 PM
Subject: RE: calling a DTS package
> http://www.sqldts.com/article.asp?nav=1,6,207,7
>
> -----Original Message-----
> From: Joel Firestone [mailto:[EMAIL PROTECTED]]
> Sent: Monday, April 01, 2002 12:44 PM
> To: SQL
> Subject: calling a DTS package
>
>
> Hey all:
>
> I'm new to DTS, and I'm wondering how I can call one from my Cold Fusion
> template/SQL 7 server? Do I call it from within a SP? If so, an example
> would be great.
>
> TIA.
>
> Joel F.
>
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The
place for dependable ColdFusion Hosting.
------------------------------
End of SQL-List V1 #141
***********************
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
