I have an ASP page executing a DTS package. (this is not Asp.net)

The DTS package does the following:
1) Check to see if File Exists
2) Import File into Sql Server table (SQl Server 2000)

I can execute the DTS with the login/pass within Enterprise Manager and it completely succeeds, but when I execute it within ASP it always fails on step (2)

Here is my code:
'--Set
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
Dim oPkg, oStep, sMessage, bStatus, DtsPackageName
DtsPackageName = "MyDTSPackage"
Set oPkg = Server.CreateObject("DTS.Package")
'--Load it
oPkg.LoadFromSQLServer "Server","login","pass",DTSSQLStgFlag_Default,""," ","",DtsPackageName
'--execute
oPkg.Execute()
'--Step into each DTS step
dim strError, i
For i = 1 To oPkg.Steps.Count
If oPkg.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
strError = strError + oPkg.Steps(i).Description + "failed. " + "<br>"
End If
Next

The first step always succeeds, the second one always fails.

Any ideas on why it is failing on step(2) or how I can track the exact error message/status?

Thanks.

~Le

apparantly the problem comes from the execution of the DTS package itself.

although it is logging in as login/pass, which is a user defined in Sql Server, it uses NT Authentication of some sort to execute the DTS package.

It uses the IIS default bame IWAS or IWAP or something....

I will post more information as I find it...

`Le

Hi,
I am having same kind of problem, I am calling the DTS package from ASP it gives me the

Step Error Description:Cannot generate SSPI context

Some time it gives me the login problem, I think its problem with the userid and password, I don;t kow exactly how to set the userid and password on the sql server so that I can access the DTS package from the browser. If any one can tell me the step by step procedure for that I will appreciate that

Thanks

San312

I resolved my problem.

When running a DTS package from ASP, you must first load the package with the user/login.

This user is simply used to GET the DTS from the server, not actually execute it.

When you Execute, it actually executes using the IIS login. But that login does not have rights to the databases and tables, so it fails.

To resolve this, I went into the DTS package. For every Database Connection, I specified the user/password.

So now the IIS still executes the DTS package, but each step in the DTS has a specific user/pass to use.

~Le

you need to specify the following to trap errors in DTS

subPkg.failOnError = true


Function ExecutePkg (pkgName)

Dim lserverName

Dim oP
Dim oConn
set oP = DTSGlobalVariables.parent
set oConn = oP.connections("Destination")
lserverName = oConn.datasource
set oP = nothing
set oConn = nothing

Set subPkg = CreateObject("DTS.Package")

' load package
subPkg.LoadFromSQLServer lserverName, "", "",256,,,, pkgName

'Stop when Error
subPkg.failOnError = true

subPkg.Execute

' release subPkg object
subPkg.Uninitialize()

End Function

ack, here we go again... when using authentication such as NT in a site it adds a header into the page, that header then is resolved by saying ok this user is authenticated, when the DTS package is downloaded it gets it, since you don't need to be authenticated it returns all active x objects to the defaults which is not what you'd expect them to be. So the DTS runs as you'd expect but the active x object is running as it's default which is set up in DCOM. You can fix this by downloading the DTS package before you get authenticated (which is i believe how you set it up) or cahnge the structure of your site. Headers are inherited so if moved to a level equal but outside the current directory, or behind, the header is not passed on.

No you cannot remove a header once it's added, it's browser level and is interpreted through before asp code is even touched.

This has been resolved as follows:

-The ASP page loads the DTS package into itself and executes it there.

-For DTS Steps that connect to databases/tables, it uses the dts' connection objects, so user/passwords need to be set for each specific connection in the DTS itself (before it is every brougth over through ASP).

-For the DTS stes that use ActiveX file object commands (file.exist, move, delete, etc), it does those commands as IWAN. Since IWAN does not have rights to those folders, it fails.

So, for all folders that the DTS is accessing, our nt admin gave IWAN rights to those folders.

Everthing works now as it should.

~Le