I have a Local DTS package which creates an extract file from a table in SQL
Server using a basic VBscript ActiveX script on SQL Server 7.0.

It runs fine from Enterprise Manager, and also if called via a SQL Agent
job, but not when I call it from an ASP webpage. See below for the web
script content, which is pretty much the same as a KB article I got it from.
The odd thing is that it definitely seems to access the DTS package (it gets
the description correctly for the Step), and when I put a SQL Server trace
on, I can see that it calls the DTS package. But it gets a failure result
back from the DTS package and the DTS package doesn't produce the output
file as it does when I call it from Enterprise Manager.

The SQL trace says that it calls the package from IIS with no user set so I
suspect that this failure to execute the content of the DTS package might be
to do with permissions.

I've got round this by calling the package via the SQL Agent job from ASP
instead, but I'd really like to know why the script below doesn't execute
the DTS script when it looks like it is doing in SQL trace.



<%@ Language=VBScript %>

<%
Dim objPkg, strError, iCount

Const DTSSQLStgFlag_Default = 0
Const DTSSQLStgFlag_UseTrustedConnection = 256
Const DTSStepExecResult_Failure = 1

'Create and Execute the package
Set objPkg = Server.CreateObject("DTS.Package")
objPkg.LoadFromSQLServer "myserver_name", "sa", "",
DTSSQLStgFlag_Default,"","","","MyDTSPackage"
objPkg.Execute
'Check For Errors
response.write "objPkg.Steps.Count = " & objPkg.Steps.Count & "<br>"
For iCount = 1 To objPkg.Steps.Count
response.write "Step Name = " & objPkg.Steps(iCount).Description & "<br>"
If objPkg.Steps(iCount).ExecutionResult = DTSStepExecResult_Failure Then
strError = strError + objPkg.Steps(iCount).Name + " failed." + chr(13)
End If
Next

If strError = "" Then
Response.Write "Success"
Else
Response.Write strError
End If
Set objPkg = Nothing
%>



When I run the above script, I get this out of it :

objPkg.Steps.Count = 1
Step Name = EXPORT
DTSStep_DTSActiveScriptTask_1 failed


There is only one step in my DTS package and it is called 'EXPORT'.
So it looks like it connected ok to SQL server and then didn't execute the
package for some reason.

Any ideas ?

In message <24601e255dcfb38b699586fea560a29d (AT) news (DOT) teranews.com>, Dave S
<dave_xxx70 (AT) hotmail (DOT) com> writes
DTS is a client-side component, so when calling from ASP it is executing
on the IIS server, under the IIS security context (IWAM_MACHINE name by
default).

Does IWAM have access to run the code in the package and create the file
in the specified location (local to the IIS server)?

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

IWAM has permissions to read/write to the directory where the DTS package on
SQL server is attempting to create a file in.

I'm not sure how to give IWAM permissions to run the code in the DTS package
on SQL Server though.
Isn't the DTS package is on SQL server and the create object in the ASP
script should be on the server side too ?




"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

In message <a63c5c1b0e0270c6c021af10ab840351 (AT) news (DOT) teranews.com>, Dave S
<dave_xxx70 (AT) hotmail (DOT) com> writes
use SQL Security.

ASP. It just happens that you can store this COM object with some
properties already set as a structured storage, either file based or in
a SQL Server database image column. This is not mean it executes on the
SQL Server at all since it is not T-SQL, or in process in any way.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org