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:
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
Dim oPkg, oStep, sMessage, bStatus, DtsPackageName
DtsPackageName = "MyDTSPackage"
Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "Server","login","pass",DTSSQLStgFlag_Default,""," ","",DtsPackageName
'--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>"
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?
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
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.
-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.