Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post Unanswered: ASP calling DTS Package -- help!

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108
    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

  3. #3
    Join Date
    Jul 2003
    Posts
    1

    Exclamation ASP Calling DTS Package

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post resolution

    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

  5. #5
    Join Date
    Aug 2003
    Location
    England
    Posts
    2

    Cool

    Originally posted by thele
    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

  6. #6
    Join Date
    Aug 2003
    Location
    England
    Posts
    2
    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

  7. #7
    Join Date
    Jul 2003
    Location
    Ohio/Chicago
    Posts
    75
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post Resolved

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •