Ajas,
I would not really approach DTS tracking just from a COM package. SQL
Server stores all of the information about a DTS package, the execution,
steps and failures in the master DB.
Create a read-only user to connect to your SQL Server's master DB and then
just start exploring all of the metadata and process information is stored
for your database.
Everything is in there.
Teddy
On 2/13/07, Ajas Mohammed <[EMAIL PROTECTED]> wrote:
Hi,
I was looking around to find code for calling a DTS package from cfm
page. I found this code below. I am able to connect and everything but I
would like to see the status of DTS package execution not just individual
steps. I got the individual steps & there status codes by adding some code
the <cfscript> code below.
My question is 1) How do you track success/failure message from DTS using
this script? This is very important because I want to let you user know what
happened after the dts was run since the DTS inserts records into a table
from a holding table and I need to confirm that to the user. If DTS failed,
I would give error msg and if it succeeded, then I will take the user to
next step i.e. create an export text tab delimited file. For ex : Stored
Proc returns 0 for no error and 1 for error.
<cfscript>
*create dts package object*
pkg = createObject("COM","DTS.Package");
* // load package *
pkg.LoadfromSQLServer
("NameOfSQLServer",
"SQL_Username",
"sql_password" ,
0,
"",
"",
"",
"nameOfTheDtsPackage" ,
"");
* // execute*
pkg.Execute();
</cfscript>
// this will get individual steps execution
<cfset sErr = "">
<cfset bSuccess = "True">
<cfset test = StructNew()>
<cfset DTSStepExecResult_Failure = 1>
<cfloop item="oStep" collection="#pkg.Steps#" >
<cfset sErr = sErr & "<p> Step [" & oStep.Name & "] ">
<cfif oStep.ExecutionResult eq DTSStepExecResult_Failure>
<cfset sErr = sErr & " failed<br>" >
<cfset bSuccess = "False">
<cfelse>
<cfset sErr = sErr & " Succeeded <br>" >
</cfif>
<cfset sErr = sErr & "Task """ & ( oStep.TaskName) & """</p>">
</cfloop>
<cfif bSuccess >
<cfset sErr = sErr & "<p>Package [" & Pkg.Name & "] succeeded</p>">
<cfelse>
<cfset sErr = sErr & "<p>Package [" & Pkg.Name & "] failed</p>" >
</cfif>
<cfoutput>#sErr#</cfoutput>
Any suggestions are welcome.
Thanks in advance...
--
<Ajas Mohammed />
http://ajashadi.blogspot.com
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
--
Teddy R. Payne
G-Talk: [EMAIL PROTECTED]
Adobe Certified ColdFusion MX 7 Developer
Atlanta ColdFusion User Group (ACFUG)
Atlanta Flash & Flex User Group (AFFUG)
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
