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 FROM ASP 1

Status
Not open for further replies.
rajkum

rajkum

Programmer
Jul 30, 2003
48
US
Hi,
When I try executing DTS package FROM ASP I received this error :


Executing DTS_PACKG_1 from server_name

1

Step [DTSStep_DTSDataDrivenQueryTask_1] failed
Task "Data Driven Query Task: UPDATE TABLE ..."

Package [DTS_PACKG_1] failed

Done


The code I am using is as below :


<%@Language=VBScript %>
<% ' 207
Option Explicit
Response.Buffer = False
%>
<html>
<head>
<title>DTS</title>
</head>
<body>

<%

Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sErr, bSuccess

Dim sServer, iSecurity, sUID, sPWD, sPkgName, sPkgPWD

' Get Form Variables
sServer = "server_name"
sPkgName = "DTS_PACKG_1"
sPkgPWD = ""
sUID = ""
sPWD = ""

' Validate Variables
'If Len(sServer) = 0 Then sErr = "<li>Invalid Server Name"
'If iSecurity = 0 And Len(sUID) = 0 Then _
'sErr = sErr & "<li>Invalid Username for SQL Security"

If Len(sPkgName) = 0 Then sErr = sErr & "<li>Invalid Package Name"


If Len(sErr) = 0 Then
Response.Write "<p>Executing " & sPkgName & " from " & _
sServer & "</p>"

' Load the Package
Set oPkg = Server.CreateObject("DTS.Package")


oPkg.LoadFromSQLServer sServer, "", "",256, , , , sPkgName


' Execute the Package
oPkg.Execute

bSuccess = True

' Report Step status
For Each oStep In oPkg.Steps

Response.Write "<br>" & oStep.ExecutionResult & "<br>"

sErr = sErr & "<p> Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sErr = sErr & " failed<br>"
bSuccess = False
Else
sErr = sErr & " succeeded<br>"
End If
sErr = sErr & "Task """ & _
oPkg.Tasks(oStep.TaskName).Description & """</p>"
Next

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

' Clean Up
oPkg.UnInitialize
Set oStep = Nothing
Set oPkg = Nothing

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

%>
</body>
</html>




I use the following Connection string to access data from sql server using ASP



Conn.Open "PROVIDER=SQLOLEDB;SERVER=server_name;INTEGRATED SECURITY=SSPI;DATABASE=DBNm;"
(this is just to give you an idea of the environment i am working)



Thanks in advance,

RajKum.

 
Sort by date Sort by votes
No expert on this but it looks to me like part of the DTS package failed - take a look at the DTS and look for the below part of it that the error message suggests failed.

Data Driven Query Task: UPDATE TABLE

Maybe try running this task in isolation to see if it fails again.

DBomrrsm
 
Upvote 0 Downvote
It works when i run from Enterprise Manager.
-Rajkum
 
Upvote 0 Downvote
When you run it from Enterprise Manager as you logged on to the server with a blank user name and password?
>oPkg.LoadFromSQLServer sServer, "", "",256, , , , sPkgName

You may also want to check securities on your table.

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!
 
Upvote 0 Downvote
I hope I am not cross posting a similar issue on the other forum...

But, what's the end of this thread? Problem solved? and how? I understand you can use
Code: 
oPkg.LoadFromSQLServer sServer, "", "",256, , , , sPkgName
-or-
Code: 
oPkg.LoadFromSQLServer sServer, "userName", "password",0, , , , sPkgName
depending on your IIS and DTS settings.

I am receiving the same error message, from what I've research, it's related to permissoin setting on DTS and IIS.

Anyone knows exactly what need to be set on IIS and/or DTS? If I have use NT authentication on IIS, what do I need to with DTS and ASP codes?
 
Upvote 0 Downvote
Hi,

The problem has not been resolved yet.

I have access to SQL server through the "Enterprise Manager".
I can run the DTS package from the Enterprise Manager.
The problem is when I try to access it throug ASP.

Does any body know whah permissions to set and how?

Thanks in advance.

 
Upvote 0 Downvote
For now, I use the client-side VBScript to execute the DTS package. I don't specify the username and password, since I use NT authentication. This is a very limited and restricted intranet applcation and it works - so far.

By doing this, I think I bypass the IIS issues that seem to related to IUSR account in IIS. The "IUSR_servername" account/user needs to be set up as a SQL Server login account to access the DTS package and related database/tables, and which I heard may have issues with securities.

My understanding is, you use your login/password to LoadFromSQLServer, but you use IIS's IUSR to execute DTS. So IUSR has to have the permission to do that.

If I understand this wrong, please correct me.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Fsoumia
  • Locked
  • Question
DTS Logging
Replies
2
Views
158
Fsoumia
Fsoumia
WordTechinc
  • Locked
  • Question
DTS disable Daily Run 1
Replies
1
Views
86
drlex
drlex
Aluminum
  • Locked
  • Question
DTS Raiserror messages suddenly stopped displaying
Replies
0
Views
73
Aluminum
Aluminum
Azzuri
  • Locked
  • Question
DTS To SQL Server 2008
Replies
1
Views
103
mrdenny
mrdenny
mutley1
  • Locked
  • Question
XLSX import to DTS (SQL 2000)
Replies
0
Views
72
mutley1
mutley1

Part and Inventory Search

Sponsor

Back
Top