Run DTS from Vb login fails

[p27br]

Hi

I have the following code to run a DTS Package from VB

 Sub ExecutePackage()

    Dim oPKG As DTS.Package, oStep As DTS.Step
    Set oPKG = New DTS.Package
    
    Dim sServer As String, sUsername As String, sPassword As String
    Dim sPackageName As String, sMessage As String
    Dim lErr As Long, sSource As String, sDesc As String
    
    ' Set Parameter Values
    sServer = "Server"
    sUsername = "Login"
    sPassword = "password"
    sPackageName = "mypackage"
    
    ' Load Package
    oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
        DTSSQLStgFlag_Default, , , , 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
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Failed" & vbCrLf & _
                vbTab & "Error: " & lErr & vbCrLf & _
                vbTab & "Source: " & sSource & vbCrLf & _
                vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
        Else
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Succeeded" & vbCrLf & vbCrLf
        End If
    Next
    
    oPKG.UnInitialize
    
    Set oStep = Nothing
    Set oPKG = Nothing
    
    ' Display Results
    MsgBox sMessage
    
End Sub

I have sysadmin rights on the server and I am dbo for the database. I can connect and use the database via Enterprise Mgr and query analyser but the code fails :

Run time error -2147217843 (80040e4d)
Login Failed For User 'username'

any ideas ?

thanks

 

[philhege]

If the login is a trusted connection, try stipulating the domain in the user name field.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[p27br]

if you mean DOMAIN\UserName, I have but to no avail

thanks

 

[philhege]

Dunno. Some other threads recommend setting PersistSecurityInfo to true (1), but it seems to be an intermittent problem all the same.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[p27br]

and PersistSecurityInfo would be a property of which object ?

thanks

 

[p27br]

I found the solution : I replaced
DTSSQLStgFlag_Default with DTSSQLStgFlag_UseTrustedConnection and it works now.

 

[gradley]

p27br,

Just an FYI. If you need to use SQL Server security instead of windows security (UseTrustedConnection), it should work if you omit the "DTSSQLStgFlag_Default" value.

Ex:

oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
        , , , , sPackageName