CODEWITHSUNDEEP
asp.netvb.netssis
Webbo
Webbo

Reputation: 82

How to get package name (Source Name) from the event log for SSIS errors in ASP.NET

I’m successfully reading the server event log to display SSIS errors from the Application event log. The aim is to make it possible to do first line troubleshooting without having to log into the server as an Administrator.

I’m using the EventLogEntry in vb.net to show Errors where the source is SQLISPackage110. However what’s stumping me is that when viewing the errors in the event log there is a “Source Name” property that displays the package name. However the EventLogEntry doesn’t seem to have this property which makes it impossible to tell which package has returned the error.

Has anyone come across this and managed to find a way round it? A screenshot of the error and some of my vb.net code is below

Event log entry with source name

 Dim myEventLogEntryCollection As EventLogEntryCollection =   myEventLog1.Entries

        myEventLog1.Close()

        Dim x As Integer
        Dim entry As EventLogEntry

        If myEventLogEntryCollection.Count > 0 Then

            strTable += "<table class='table table-bordered'><tr><th>Time Written</th><th>Source</th><th>Event type</th><th>Message</th></tr>"

            For Each entry In myEventLogEntryCollection.Cast(Of EventLogEntry).Reverse  'cast and reverse to show newest first

                If entry.Source = strEventSource And (entry.EntryType.ToString = "Error" Or entry.EntryType.ToString = "Warning") Then
                    strTable += "<tr>"
                    strTable += "<td>" + entry.TimeWritten.ToShortDateString + " " + entry.TimeWritten.ToShortTimeString + "</td>"
                    strTable += "<td>" + entry.Source.ToString + "</td>"
                    strTable += "<td>" + entry.EntryType.ToString + "</td>"
                    strTable += "<td>" + entry.Message + "</td>"
                    strTable += "</tr>"
                    x += 1
                End If

                If x > 100 Then Exit For 'could be 1000s in the log
            Next

            strTable += "</table>"

        End If

Upvotes: 1

Views: 692

Answers (1)

Troy Witthoeft
Troy Witthoeft

Reputation: 2676

As you've noticed, "Source" is NOT one of the LogEntry properties. Instead the word "Source" is just text trapped inside of the larger Message property. So, to get the "Source" information you're going to have to parse it out of the Message. Split the message into lines. Then loop over the lines and do something when you find a line containing the word "Source" This code is NOT tested. But, just to give the idea..

Dim messageLines As String() = Nothing

messageLines = entry.Message.Split(Environment.NewLine)

Dim line As String

For Each line In messageLines
If line.Contains("Source") Then 
    'your code here
Next line

SSIS packages can log to many destinations. And yes, logging to the Windows Event log is an option, but I've seldom seen it used in practice. Windows Event Logs aren't permanent. By default, they are going to start trimming the history once they get over twenty megabytes in size. They are a bad place to store SSIS log information for troubleshooting.

Would you consider gently suggesting to your package authors that they use SSIS SQL logging instead? If they logged to SQL then you'd have the information you need arranged, and stored in a nice searchable manner that would allow for easier querying, aggregation, and analysis across your entire organization. You'd even have the source column you could use to get the exact info you need.

enter image description here

Upvotes: 1

Related Questions