Post reply

Executing a DTS package from an ASP page

  • June 10, 2004 at 8:35 am

    #76170

    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

  • June 10, 2004 at 5:10 pm

    #509984

    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

  • June 11, 2004 at 6:40 am

    #510033

    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!

  • June 11, 2004 at 7:18 am

    #510039

    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