• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Get errors from a DTS Package run in DOS Shell

I'm running the process like this:

Shell("...blah\...DTSExecutor.exe", AppWinStyle.NormalFocus, True)

This is most of the important code that does everything in my DTSExecutor program:

pkg = New DTS.Package
            'Begin - set up events sink
            Dim cpContainer As UCOMIConnectionPointContainer
            cpContainer = CType(pkg, UCOMIConnectionPointContainer)
            Dim cpPoint As UCOMIConnectionPoint
            Dim PES As PackageEventsSink = New PackageEventsSink
            Dim guid As Guid = _
                New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5")
            cpContainer.FindConnectionPoint(guid, cpPoint)
            Dim intCookie As Integer
            cpPoint.Advise(PES, intCookie)
            'End - set up events sink
            pkg.LoadFromSQLServer("OMABC2", "sa", "", _
            DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _
            "", "", "", "test dts", Nothing)

            Console.WriteLine("PACKAGE EXECUTION BEGINNING")
            Console.WriteLine("PACKAGE EXECUTION COMPLETED")
            Console.WriteLine("The package contained {0} steps.", _
            pkg = Nothing
            cpPoint = Nothing
            cpContainer = Nothing
            PES = Nothing

If something simple goes wrong, like it can't find the sheet in in the Excel workbook it's getting its data from, I want to throw the error and for the user to have a fairly accurate idea of where things've gone wrong.

This is being done in a major hurry and has to be finished soon.  I've only had 5 minutes to sit down and look at it and now I'm going home and it'd be nice to know what to do when I arrive tomorrow morning!

If somebody could point out how I get the error, that would be much appreciated!  Just catch and exception as normal?  I don't have time to try it!  Argh!
1 Solution
Catching the error of the pkg.execute will give you something basic, like: Package failed because step sss failed.
You can also check the steps object properties to know if other steps status.
maybe this can help you:

        Dim oStep As [Step]
        For Each oStep In oPkg.Steps()
            strResult = strResult & "<p> Step [" & oStep.Name & "] "
            If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
                strResult = strResult & " failed<br>"
                bSuccess = False
                strResult = strResult & " succeeded<br>"
            End If
            strResult = strResult & "Task """ & _
             oPkg.Tasks.Item(oStep.TaskName).Description & """</p>"

        If bSuccess Then
            strResult = "<p>Execution of package [" & oPkg.Name & "] succeeded</p>" & strResult
            strResult = "<p>Execution of package [" & oPkg.Name & "] failed</p>" & strResult
        End If
        Return strResult

If you need more precise infi, you have the log files.

Not an expert, but I try to help...

_Canidae_Author Commented:
Thanks for your help but I managed to sort this out earlier and forgot to delete the question.  You can have the points anyway, for being the only person who offered any help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now