Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54

    Unanswered: Execute DTS package from ASP failed

    I am trying to run DTS Package from ASP 3.0 with the following codes:

    Code:
        Set objDTSPackage = server.CreateObject("DTS.Package")
        objDTSPackage.LoadFromSQLServer "serverName", "", "", 256,,,,"pkgName" 
        objDTSPackage.FailOnError = true
        objDTSPackage.Execute
    It failed with this message, "Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed".

    I am using NT Authentication in both IIS and SQLServer, and both Web and SQLServer are in the same machine.

    Can someone help? Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The DTS package must be executed in the same security context as the web page. In ASP web pages are executed under the IIS account that begins with IUSR_. You can find this account name by right clicking My Computer and clicking Manage. Expand Local Users and Groups and look in the users folder.

    Then set up a SQL server account for this login like 'MyComputerName\IUSR_...'.

    Be careful to only give this account public access to the database the DTS package is accessing and I even go to the trouble of restricting permission to the specific tables.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    ps In the SQL account change the default database to the affected database (not master) and do not give it a server rol for security reason
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54
    Thanks for the prompt reply.

    I am using NT Authentication in IIS for all my web applications, does this matter?

    I've also tried creating SQL Server login ID with 'myMachineName\IUSR_....', but my company's system wouldn't allow me do this - for security reason.

    Any alternatives?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    No it should'nt matter.

    are you not allowed to create SQL logins?

    or is it giving an error?

    try 'yourdomain\youriusraccount'. It might work.

    The only other alternative using DTS I know creates a big ugly security hole for hackers to climb in. It invloves firing the DTS package from a shell command and going into the SQL agent properties and setting up a proxy account under the job system tab. The ramifications are ugly.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54
    I do have the rights to create login ID, but this is something to do with different domain in the system, and I guess not all users in certain domain can be created.

    Let me clarify a bit, when you say, create login ID for 'MyComputerName\IUSR_...', you do mean to say the computer that I am personally using now, correct? Not the computer name with IIS installed, right?

    I have to find a way to create login ID for this 'IUSR_...', so if this is created, I can use the ASP codes I showed to execute DTS?

    Thanks.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    MyComputerName is the name of the computer where your IIS install is.

    If you do this your code will work. I've incorporated DTS into 3 ASP projects in the last 4 years.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54
    Thank you.

    I'm still tryihg find "IUSR_" for my web server in SQL Server login creation. Is there any possible reason you can think of that I can't find the "IUSR_" on SQL Server, "New Login"?
    Last edited by rweide; 01-19-05 at 14:16.

  9. #9
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54
    Just want to update and close this:

    I've found the IUSR account for the new login to SQL Server. The domain of this IUSR account will be the name of the server (since IUSR is a local user). I've found it there. Thanks.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    did it work?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54
    Yes and No, Sorry for this terrible answer. Thank you for keep interests in this.

    I have used client-side VBscript to make it work - instead of server-side ASP code, I guess this bypasses the IIS issues (?). I am a bit concerned of the security, other than all the stuffs I am still learning. I think I will come back with ASP server-side code to make it work.

    HOWEVER, I am having another problem. After I executed successfully the DTS Package, my query and subsequent SQL operations to the destination table don't work anymore. It gives no error messages, but it is not doing anything. A simply query to check the row count, comes back with 0 rows, even though the DTS has transform records to the table.

    Help me, if you have any idea? I am not sure where the problem is coming from?!

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how do you know the DTS package executed successfully?

    does it work when you fire it from the EM (i.e. rows in the table)?

    what is the code you are using to execute the DTS?

    i am betting this is another permissions issue and has to do with this client side script you have.

    here is some vbscript that should tell you where the package is failing. Depending on how you are doing the package will not always throw errors.

    http://www.sqldts.com/default.aspx?t...&i=207&p=1&a=7

    I converted this script to VB 6 that I wrapped in a COM object which is BTW how I accomplish this. See below.

    Option Explicit

    Private m_sError As String

    Public Function Execute(ByVal sServer As String, ByVal sPackageName As String) As Boolean
    On Error GoTo Err_Handler
    Dim oPKG As DTS.Package, oStep As DTS.Step
    Set oPKG = New DTS.Package

    Dim lErr As Long, sSource As String, sDesc As String

    Execute = True

    ' Load Package
    oPKG.LoadFromSQLServer sServer, , , _
    DTSSQLStgFlag_UseTrustedConnection, "!J1LLYB3@N!", , , sPackageName

    ' Set Exec on Main Thread
    For Each oStep In oPKG.Steps
    oStep.ExecuteInMainThread = True
    Next

    ' Execute
    oPKG.Execute

    ' Get Status and Error Message
    For Each oStep In oPKG.Steps
    If oStep.ExecutionResult = DTSStepExecResult_Failure Then
    oStep.GetExecutionErrorInfo lErr, sSource, sDesc
    m_sError = m_sError & "Step """ & oStep.Name & _
    """ Failed" & vbCrLf & _
    "Error: " & lErr & vbCrLf & _
    "Source: " & sSource & vbCrLf & _
    "Description: " & sDesc & vbCrLf & vbCrLf
    Execute = False
    Else
    m_sError = m_sError & "Step """ & oStep.Name & _
    """ Succeeded" & vbCrLf & vbCrLf
    End If
    Next

    oPKG.UnInitialize

    Clean_Up:
    Set oStep = Nothing
    Set oPKG = Nothing

    Exit Function

    Err_Handler:
    Execute = False
    m_sError = "Error in Object Execution" & vbCrLf & _
    "Number: " & Err.Number & vbCrLf & _
    "Source: " & Err.Source & vbCrLf & _
    "Description: " & Err.Description
    GoTo Clean_Up
    End Function

    Public Function GetErrorDetails() As String
    GetErrorDetails = m_sError
    End Function
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54
    Before DTS starts, the destination table is cleared, then after the DTS, I went into EM to check, and they are rows in the tables. I assume that means DTS is working.

    And this is my client-side VB code:
    Code:
      Dim objDTSPackage, 
      On Error Resume Next
      Set objDTSPackage = CreateObject("DTS.Package")
      objDTSPackage.LoadFromSQLServer "server", "", "", 256,,,,"pkgName" 
      objDTSPackage.FailOnError = true
      objDTSPackage.Execute
      objDTSPackage.UnInitialize() 
      Set objDTSPackage = Nothing
    I will also study your codes. Thank for your help.

  14. #14
    Join Date
    Aug 2004
    Location
    USA
    Posts
    54
    Not sure if anyone would still read this long thread, but...

    I've made this to work by putting codes into a VB DLL.

    However, if I execute a simple SELECT statement against the destination table after the DTS execution is completed. It gives no error, but with no result either.

    But if I reload the ASP page immediately, in other words, DTS is executed the second time, the SELECT statement produces the results from the FIRST DTS execution, instead of coming from the 2nd DTS execution!?

    It seems to me the first DTS exectuion doesn't exist - as far as the SELECT statement is concerned.

    Anyone has idea? Please help.

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    rweide,
    I am still here. watching this forum keeps me from working on my current ptoject which requires no effort or creativity and is boring me to death. So this is how I pass my days.

    Might need to see some code here. Are you executing the DTS package and the select on the same post to the server?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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