I have a DTS package which imports data from a legacy app to SQLServer 2000.  Right now I have a page in classic ASP which executes a SP which in turn calls the DTS package.  After the user hits the "Update" button on the ASP page they are redirected to a page that says "This process takes several minutes, check back later."  The current setup is causing problems because Joe may hit the "Update" button to refresh the data and get an error because the DTS package is already running from when Mary just ran the same process.

I want to rewrite this in .NET (2.0) to accomplish the following:

1. Eliminate the SP
2. Check the status of the DTS package and alert the user if it is already running.
3. Show a "Please wait while we process your request" message to the person who started the update while the DTS is running.
4. Automatically show the report when the DTS is done.

I think I can find code in the forum to execute the DTS, but how do check the current status first?  I've read about SQLDMO but the Web server and SQLServer are on different machines.  Would this still work?

Thanks in advance.

I'm having trouble getting the DTS execute to run asynchronously.  All the examples I've found show executing queries asynchronously, but nothing on DTS (there all in C# as well).  Here's what I have in my class.

Imports System.IO
Imports System.Net Imports System.Net.Mail
Imports System.Threading Imports System.Runtime.Remoting.Messaging
Imports DTS
Imports DTS.DTSSQLServerStorageFlags

Partial Class Reporting_AmSherReports_rptColStats
    Inherits System.Web.UI.Page
    '----------------------------------------------------------------------------------------------
    'Remember, if you change the package, these numbers will need to be modified too.
    '----------------------------------------------------------------------------------------------

    Private sPkgID As String = "{4CFD14F0-12D8-445C-AD98-E0DD784B166A}"
    Private sPkgVersion As String = "{F0CAFA3B-9A52-4780-B0C6-5B86C38BCFC5}"
    Protected sMessage As String = ""

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack Then

            'Show processing message....

            Processing.Visible = True
            Report.Visible = False
            Refresh.Visible = False

            Dim state As New Object()
            Dim beginHandler As New BeginEventHandler(AddressOf ExecuteDTS)
            Dim endHandler As New EndEventHandler(AddressOf DTSComplete)
            Dim timeoutHandler As New EndEventHandler(AddressOf DTSTimeout)
            Dim task As New PageAsyncTask(beginHandler, endHandler, timeoutHandler, state)
            RegisterAsyncTask(task)
        End If
    End Sub

    Private Function ExecuteDTS(ByVal sender As Object, ByVal e As EventArgs, ByVal cb As AsyncCallback, ByVal state As Object) As IAsyncResult
        Try
            Dim sSource As String = ""
            Dim sDesc As String = ""
            Dim lErr As Long
            Dim oPKG As DTS.Package = New DTS.Package
            Dim oStep As DTS.Step

            'Load Package

            oPKG.LoadFromSQLServer("server", "sa", "", DTSSQLStgFlag_UseTrustedConnection, , sPkgID, sPkgVersion, "Refresh Stats")

            'Execute

            oPKG.Execute()

            'Get Status and Error Message

            For Each oStep In oPKG.Steps
                If oStep.ExecutionResult = DTSStepExecResult.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
                End If
            Next
        Catch ex As Exception
        Finally
            Processing.Visible = False
            Report.Visible = True
            Refresh.Visible = False
        End Try
    End Function

    Private Sub DTSComplete(ByVal ar As IAsyncResult)
        Processing.Visible = False
        Report.Visible = True
        Refresh.Visible = True
    End Sub

    Private Sub DTSTimeout(ByVal ar As IAsyncResult)
        'No implementation -- needed for PageAsyncTask

    End Sub
End Class