Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DTS package runs on Server but not on ASP page

Status
Not open for further replies.
JezEling

JezEling

IS-IT--Management
Mar 22, 2004
127
GB
Hi all,

I have a DTS package called from a webpage which runs a simple query and outputs the results as a txt file.

When I run the DTS via Enterprise manager it works great and creates the txt file however when I run it via the ASP page it fails. I'm pretty sure this is to do with permissions but no matter what permissions I set on the folders it still will not do what it needs to do.

Has anyone experienced this before or does anyone have any suggestions as to how I might solve the problem

Thanks in advance

Jez
 
Sort by date Sort by votes
How do you cause it to run and where does it create the file? Are you executing the EXE directly in it's folder?

We just had to deal with something similar here and our solution was to use a batch file to trigger running the DTS package and we triggered the batch file from an ASP page.

I believe the security lies in the DB server. The rights in the DB to execute the package are probably what are holding you up, not just rights to the files on the PC.


Paranoid? ME?? WHO WANTS TO KNOW????
 
Upvote 0 Downvote
The file is triggered at the moment just by loading the page, eventually it will be a hyper link that triggers it, code is as follows (lifted from the Microsoft KB):-

<%@Language=VBScript %>
<% Option Explicit %>
<html>
<head>
<title>Test DTS Run</title>
</head>
<body>

<%
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sMessage, bStatus

Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "{server}","{user}","{password}",DTSSQLStgFlag_Default,"{password}","","","Verify"
oPkg.Execute()

bStatus = True

For Each oStep In oPkg.Steps
sMessage = sMessage & "<p> Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sMessage = sMessage & " failed<br>"
bStatus = False
Else
sMessage = sMessage & " succeeded<br>"
End If
sMessage = sMessage & "Task """ & oPkg.Tasks(oStep.TaskName).Description & """</p>"
Next

If bStatus Then
sMessage = sMessage & "<p>Package [" & oPkg.Name & "] succeeded</p>"
Else
sMessage = sMessage & "<p>Package [" & oPkg.Name & "] failed</p>"
End If

Response.Write sMessage
Response.Write "<p>Done</p>"

%>
</body>
</html>

The DTS is set to create the file on in a folder called stock on C: drive.
 
Upvote 0 Downvote
Make certain you have SQL Server Authentication set within your DTS package as the authentication method and that the ID/Password you pass in has rights in the DB to execute the package.

Microsoft has another article describing how to determine what security context the package executes under at this link: It is important to know who the assigned owner of the package is as well.


Paranoid? ME?? WHO WANTS TO KNOW????
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
201
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
270
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
252
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
READ TEXT FILE BASED ON VARIABLE 1
Replies
2
Views
188
guitarzan
guitarzan
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
180
JScannell
JScannell

Part and Inventory Search

Sponsor

Back
Top