Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Question Unanswered: Executable or Way for User to Launch an SQL Package?

    I have a sitation where I need a dts package or similar to run at a user initiated time. I do not want to give the user access to the server. Any ideas on how one goes about something like this?

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy


    If its through a web or VB type app, let the web app execute a stored procedure called by the app.


    Cheers,

    SG.

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Hey,
    from a vb app or vb script you can do this function:

    Public Sub ExecuteEDIPackage(FileName As Variant)

    Dim sServer As String
    Dim sUsername As String
    Dim sPassword As String
    Dim sPackageName As String
    Dim lErr As Long
    Dim sSource As String
    Dim sDesc As String


    Set oPKG = New DTS.Package


    ' Set Parameter Values
    sPackageName = "EDIPackage"

    ' Load Package
    oPKG.LoadFromSQLServer DataSource, UserName, Password, _
    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

    End Sub

Posting Permissions

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