Executing a DTS package from an ASP page
-
I have a DTS package that needs to be run on demand from an asp page. The package is currently scheduled to run every four hours from SQL Server agent. It runs every four hours without issue. However, when I try to execute the package using asp, it fails. I know it sucessfully attached to the package because I can display global variable values. I have other applications where I have done the same thing without issue. Here is the code I am using:
dim objDTSPackage
dim objDTSStep
dim strResult
dim blnSucceeded
const DTSSQLStgFlag_Default = 0
const DTSSQLStgFlag_UseTrustedConnection = 256
const DTSStepExecResult_Failure = 1
set objDTSPackage = Server.CreateObject("DTS.Package")
blnSucceeded = true
objDTSPackage.LoadFromSQLServer "server", "id", "pw", DTSSQLStgFlag_Default,"", "", "", "DTS_Package_Name"
objDTSPackage.Execute
for each objDTSStep in objDTSPackage.Steps '
if objDTSStep.ExecutionResult = DTSStepExecResult_Failure then
strResult = strResult & "Package " & objDTSStep.Name & " failed.
"
blnSucceeded = false
else
strResult = strResult & "Package " & objDTSStep.Name & " succeeded.
"
end if
next
if blnSucceeded then
Response.Write "
Package Succeeded
"
else
Response.Write "
Package Failed
"
end if
I think that there is something not right with the package and not the code. I am at my wits end!
Any insight that anyone can provide is greatly appreciated.
Thanks,
Michelle
-
Firstly, what error is the package failing with?
Second, why not just trigger the scheduled job from your ASP page using sp_start_job?
--------------------
Colt 45 - the original point and click interface
-
I do not get an error. It just does not run.
I got it to work yesterday by creating a stored proc that runs the dts. But I am going to try your suggestion.
Thanks!
-
If your DTS is accessing external resources, like drives on other servers, perhaps the account that ASP uses to execute the DTS does not have sufficient permissions, but the account used by SQL Agent does.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply