DTS package (MS SQL server 2000) reads data from a text
file, then populates a table, then from that table
populates five tables, depending of the values in each
record.
The package executes OK on developers PC from SQL
Enterprise Manager, as well as from ASP page running on
local host.
At web site it executes from SQL Enterprise Manager
without problems. But when trying to execute it from ASP
page, it failed. I cannot get error messages, so my only
guess that it because of user permissions?

I used in ASP pages two methods of running DTS:
1. Calling stored procedure, which contains single line:

EXEC
master..xp_cmdshell 'dtsrun /Smyserver /NImportData /Uus
er /Ppwd '

2. Creating package object and executing it.

Set oPkg = CreateObject("DTS.Package")

oPkg.LoadFromSQLServer

msDTSServer,msDTSUser, msPass,
DTSSQLStgFlag_Default,"","","", msDTSPkg

oPkg.Execute()

' Displaying results of execution
For Each oStep In oPkg.Steps
response.Write(" Pkg Step " & oStep.Name
& " " )

If oStep.ExecutionResult =
DTSStepExecResult_Failure Then
response.Write(" failed<br>" )
bStatus = False
Else
response.Write( " succeeded<br>" )
End If

response.Write( "<br>Task """ & oPkg.Tasks
(oStep.TaskName).Description & """<br>" )
Next

Both methods are working on developers PC, but failed at
the web server.
Calling stored procedure manually succeeded at developers
database, but failed at web server.
Interestingly, the very first step was succeeded and that
step did not required SQL server authentication.

I would appreciate any suggestions. Thanks.

What errors do you get ?

Execute a package from Active Server Pages (ASP)
(http://www.sqldts.com/default.aspx?207)

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Bona" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks for your reply, Allan.
I downloaded a sample from
http://www.sqldts.com/default.aspx?207. Here is the exact
output of the execution:
' output start:

Executing ImportData_FCF from MYServer

Step [DTSStep_DTSExecuteSQLTask_1] succeeded
Task "Create test_stem"

Step [DTSStep_DTSDataPumpTask_1] failed
Task "Transform Data Task: undefined"

Step [DTSStep_DTSExecuteSQLTask_2] failed
Task "Dev HStemID"

Step [DTSStep_DTSExecuteSQLTask_3] failed
Task "Pop ao53"

Step [DTSStep_DTSExecuteSQLTask_4] failed
Task "Pop AO54"

Step [DTSStep_DTSExecuteSQLTask_5] failed
Task "Pop AO52"

Step [DTSStep_DTSExecuteSQLTask_6] failed
Task "Pop A056"

Package [ImportData_FCF] failed

Done

' -- output end

The very first task succeeded: it is creating temporary
table. The next steps requires connection, using SQL
server authentication. It seems that it failed. Although I
can run the package from Enterprise Manager, logging with
the same user name/password as I supplied from ASP page.

It would definitiely appear that the problem is in your authentication and
permissions then. I would go back and retrace the steps. Also look at
Profiler and monitor Logon Failed events. This will tell you who is getting
bounced

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Bona" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks, Allan. I will do that.