run dts without EM installed?

[patrichek]

hi,
I've been looking at ways to allow my MS Access users to run a dts package by clicking a button. I've used some vb code i got from

http://www.sqldts.com.

I like it because it shows the progress of the package for the end user, like a confirmation i guess.

the code works great on a system with Enterprise manager installed but won't run on machines without it.
I'm looking for a way around that problem.

So far i've tried referencing dtspgk.dll in vb on the client(activeX can't create object error), i've tried mapping a shared folder to the client with dtsrun.exe and a bat file (got dtsrun.rll error).
I'm absolutely a beginner when it comes to Stored procedures, so i'd really like to get this approach to work.

Any help is appreciated.

here's my vb code

Private 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 = "MyServer"
    sUsername = "MyUser"
    sPassword = "MyPassword"
    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

 

[unclerico]

I'm not sure if you have seen this article or not but the people who run this site definitely know the in's and outs of DTS:

http://www.sqldts.com/default.aspx?225