DTS through ASP

  • Hi ,

    I am executing one DTS package thorugh ASP page.I am using following code to execute the DTS from ASP.


    Set oPkg = Server.CreateObject("DTS.Package")

    oPkg.LoadFromSQLServer sServer, sUID, sPWD,   DTSSQLStgFlag_Default , "", "", "", sPkgName

    ' Execute the Package



    But I am geting the error that package failed.

    Do any one tried this thorgh Aps ?

    Please help

    Thanks in advance !


  • You could try xp_cmdshell to execute the package?

  • Here is a link to a very helpful site.


  • hi

    I have done this a number of times.

    What i tend to do is wrap it in a stored proc that uses XP_CMDSHELL

    I then call the stored procedure from ASP and this seems to work fine

    hope this helps


  • hi ross,

    I don't have execute rights on xp_cmdshell. is there any other way to execute DTs thro ASP.?


  • Yuvraj,

    The link I told you about has a code sample of executing DTS from ASP without using XP_CMDSHELL. If you don't have execute rights to XP_CMDSHELL this may be your best option.

  • hi,

    I tried with  the code given in site mentioned by you but unfortunately that code also not working properly with me. I have two stpes in my DTS.First step is to delete the records from table and second step is upload the excel data into table . When I used the code that is given on site I got following problem.


    Step [DTSStep_DTSDataPumpTask_1] failed --this step fails here DtS access --the excel file and uploads into table

    Task "Transform Data Task: Excel upload to databse"

    --this step succeeded it deletes rcords from table.

    Step [DTSStep_DTSExecuteSQLTask_1] succeeded

    Task "Execute SQL Task: undefined"

    Package DTSName failed


    From above problem I got only that I can access and executes DTS , but still problem is there may be with excel file.I am not able to see the logs of DTS when I execute it from code..

    I am not able to get what is the problem? So I tried with XP_CMDSHELL.

    Please help.


  • Hi Yuvraj

    Can you confirm that you have built the package in DTS Designer and successfully executed if from there and that the problem only occurs when you run it through asp?

    How are you accessing SQL?  are you using enterprise manager to access it from your pc or using remote desktop and accessing it directly?



  • Hi Ross,

    Yes DTS executed successfully from DTS designer and I am using enterprise manager from my pc to access SQL. I have SQLserver2000 client installed on my pc.


  • Does the server that the DTS is runing on have access (permission) to the drive that the Excel file is on?

  • Yuvraj

    I may have got a little confused here....

    If you are able to execute successfully through DTS Designer, but are using a local version of Enterprise Manager to do it, it is going to execute from local machine, not server.  Therefore this is not the same as being able to execute from the server itself.  (A path on server and local machine are different)

    Have i misunderstood, or is your whole environment running from your development PC? i.e. is your asp program accessing the database server on your development pc or on a remote server?


  • Hi,

    I think yes, because When I try to execute same DTS in Enterprise manager then it works.

    When I am trying to execute the same DTS from ASP it faills,  how can I identify that server is  not able to access excel file?


  • Hi Ross,

    I am working on one machine , my web server is on other machin and SQLServer  is on different machine . I am putting excel file on webserver.


  • ok yuvraj, i suspect that is your problem

    If you setup your package to work from your local machine, it runs from that machine.

    I suspect that if you can get onto your sql server - either at console or by remote desktop, it will not work.

    check that you have used UNC path to access the excel spreadsheet and that the account that SQL server runs as has access rights to this share.  Remember when running from your machine will use your credentials NOT the sql servers.

    hope this helps


Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply