Post reply

DTS through ASP

  • February 24, 2005 at 2:24 am

    #66572

    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

    oPkg.Execute

    -------------------------------------------------------------

    But I am geting the error that package failed.

    Do any one tried this thorgh Aps ?

    Please help

    Thanks in advance !

    Yuvraj

  • February 24, 2005 at 4:07 am

    #543868

    You could try xp_cmdshell to execute the package?

  • February 24, 2005 at 12:06 pm

    #543986

    Here is a link to a very helpful site.

    http://www.sqldts.com/default.aspx?104

  • February 24, 2005 at 1:30 pm

    #544015

    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

    Ross

  • February 25, 2005 at 6:04 am

    #544129

    hi ross,

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

     

  • February 25, 2005 at 6:37 am

    #544136

    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.

  • February 25, 2005 at 6:53 am

    #544139

    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.

    Yuvraj

  • February 25, 2005 at 7:06 am

    #544143

    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?

     

    R

  • February 25, 2005 at 7:15 am

    #544148

    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.

    Yuvraj

  • February 25, 2005 at 7:31 am

    #544152

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

  • February 25, 2005 at 7:38 am

    #544153

    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?

    Ross

  • February 25, 2005 at 7:44 am

    #544157

    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?

    Yuvraj

  • February 25, 2005 at 8:40 am

    #544177

    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.

    Yuvraj

  • February 25, 2005 at 9:35 am

    #544194

    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

    Ross

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

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