Bei der Verwendung von SSIS-Paketen kommt es sehr häufig vor, dass diese Pakete automatisch (z.B. nächtlich) ausgeführt werden.
Sollten bei der Ausführung der Pakete Fehler auftreten, werden diese standardmäßig im Windows Event-Log protokolliert. Leider ist diese Protokollierung ist nicht sehr detailliert, sondern zeigt nur an, welches Paket einen Fehler geworfen hat, nicht was für ein Fehler aufgetreten ist.
Ein weiterer Nachteil dieser Lösung ist, dass ein Fehler nicht sofort auffällt, sondern davon abhängt, ob der Administrator regelmäßig ins Event-Log schaut, oder ein entsprechendes Monitoring eingerichtet hat.
Aus diesem Grunde wünschte sich der Kunde eine E-Mail-Benachrichtigung im Fehlerfall.
Die ursprüngliche Idee bestand in der Verwendung des Tasks „Email senden“ der schon von SSIS mitgeliefert wird, nach einigem Probieren stellte ich jedoch fest, dass nie eine Email gesendet wurde.
Es stellte sich heraus, dass der Port 25 auf dem verwendeten System gesperrt war und im Email-Task der Port des SMTP-Servers nicht angepasst werden kann.
Ich habe das Problem dann mit zwei Skript-Tasks gelöst und stelle mein Vorgehen nachfolgend kurz vor:
Vorgehen
In meinem SSIS-Paket habe einen Sequenzcontainer „Daten importieren“, welcher einen Task „Fehler erzeugen“ enthält, der exemplarisch für meinen Datenimport steht:
Im Scope des Pakets werden folgende Variablen angelegt:
Die Variable „errorMessages“ beinhaltet später die Fehlermeldungen, die anderen Variablen dienen zur Einstellung des SMTP-Servers und der Mail-Adressen und -Inhalte.
Als nächstes muss nach einem Klick auf die Registerkarte „Ereignishandler“ der Ereignishandler „OnError“ des Pakets “Daten importieren” mit einem Skript-Task versehen werden. Dieser Skript-Task sammelt alle Fehlermeldungen die geworfen werden, und speichert diese in der Variablen „errorMessages“
Der Task bekommt als ReadOnly-Variable: „System:ErrorDescription“ und als ReadWrite-Variable die vorhin angelegte „User::errorMessages“ übergeben:
Nach einem Klick auf „Skript bearbeiten“ muss in den Task der folgende Code eingefügt werden:
(siehe Code “collect_errors.vb”)
Der Ereignishandler „OnPostExecute“ muss mit einem weiteren Skript-Task zum Versenden der Email versehen werden.
Dieser Task bekommt die Variablen errorMessages, mailFrom, mailTo, mailSubject, mailSMTPHost und mailSMTPPort übergeben.
In den Task muss der folgende Code eingefügt werden:
(siehe Code “send_mail.vb”)
Erklärung des VB-Codes
Collect_errors.vb
- Imports System
- Imports System.Data
- Imports System.Net
- Imports Microsoft.SqlServer.Dts.Runtime
- Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask
- Imports System.Text
- <System.AddIn.AddIn(„ScriptMain“, Version:=„1.0“, Publisher:=„“, Description:=„“)> _
- <System.CLSCompliantAttribute(False)> _
- Partial Public Class ScriptMain
- Inherits VSTARTScriptObjectModelBase
- Enum ScriptResults
- Success = DTSExecResult.Success
- Failure = DTSExecResult.Failure
- End Enum
- Public Sub Main()
- Dim messages As ArrayList
- Try
- messages = CType(Dts.Variables(„errorMessages“).Value, Collections.ArrayList)
- Catch ex As Exception
- messages = New ArrayList()
- End Try
- messages.Add(Dts.Variables(„ErrorDescription“).Value.ToString())
- Dts.Variables(„errorMessages“).Value = messages
- Dts.TaskResult = ScriptResults.Success
- End Sub
- End Class
Zeilen 19-24:
Die neu angelegte ArrayList „messages“ dient nur als Zwischenspeicher für eine jeweils eine Fehlermeldung.
„Messages“ bekommt den Wert der globalen Variablen „errorMessages“ zugewiesen, welcher für das Array entsprechend gecastet wird.
Beim Auftreten des ersten Fehlers würde eine Exception geworfen werden, in diesem Fall bekommt „messages“ im Catch-Block eine neue ArrayList zugewiesen.
Zeilen 25-27:
„Messages“ bekommt die Beschreibung des aktuellen Fehlers zugewiesen, und wird ihrerseits wieder in die globale Variable „errorMessages“ zurückgespeichert.
Send_Mail.vb
- Imports System
- Imports System.Data
- Imports System.Net
- Imports Microsoft.SqlServer.Dts.Runtime
- Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask
- Imports System.Text
- <System.AddIn.AddIn(„ScriptMain“, Version:=„1.0“, Publisher:=„“, Description:=„“)> _
- <System.CLSCompliantAttribute(False)> _
- Partial Public Class ScriptMain
- Inherits VSTARTScriptObjectModelBase
- Enum ScriptResults
- Success = DTSExecResult.Success
- Failure = DTSExecResult.Failure
- End Enum
- Public Sub Main()
- Dim errorDesc As String = String.Empty
- Dim emailText As String = String.Empty
- Dim messages As ArrayList
- Try
- messages = CType(Dts.Variables(„errorMessages“).Value, Collections.ArrayList)
- Catch ex As Exception
- ‚ no errors
- Return
- End Try
- For Each errorDesc In messages
- emailText = emailText + errorDesc + vbCrLf
- Next
- Dim htmlMessage As Mail.MailMessage
- Dim smtpClient As Mail.SmtpClient
- htmlMessage = New Mail.MailMessage( _
- Dts.Variables(„mailFrom“).Value, _
- Dts.Variables(„mailTo“).Value, _
- Dts.Variables(„mailSubject“).Value, _
- emailText _
- )
- smtpClient = New Mail.SmtpClient(Dts.Variables(„mailSMTPHost“).Value)
- smtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
- smtpClient.Port = CType(Dts.Variables(„mailSMTPPort“).Value, Integer)
- smtpClient.Send(htmlMessage)
- Dts.TaskResult = ScriptResults.Success
- End Sub
- End Class
Zeilen 22-27:
Analog zum Skript „Collect-Errors“ wird hier der Wert der globalen Variable „errorMessages“ der lokalen Varable „messages“ zugewiesen, die wieder als Zwischenspeicher dient.
Sollte die globale Variable keinen Wert enthalten, bedeutet dies, dass während der Paketausführung kein Fehler aufgetreten ist und des Skript dementsprechend mit „Return“ beendet werden kann.
Zeilen 29-31:
Die gesammelten Meldungen aus „messages“ werden jeweils durch einen Zeilenvorschub getrennt einer Variablen „emailText“ zugewiesen.
Zeilen 36-41:
Ein Objekt des Typs „Mail.MailMessage“ wird angelegt, das später versendet wird.
Zeilen 42-44:
Anlegen eines Objekts vom Typ Mail.SmtpClient, das zum Versenden des htmlMessage-Objekts dient.
Zeile 45:
Absenden der E-Mail.
Fazit und Ausblick
Der von mir hier gezeigte Weg, war in meinem Fall wegen der geänderten Portnummer unumgänglich, auch wenn die Benutzung eines simplen „Mail senden“-Tasks sicherlich mit weniger Aufwand verbunden gewesen wäre.
Ein weiterer Vorteil der Benutzung des Skript-Tasks im Vergleich zum Mail-Task sind die besseren Erweiterungsmöglichkeiten.
Sonstiges
Ein gutes Tool um den Mail-Versand zu testen ist übrigens smtp4dev (siehe unter Links). Es kommt ohne Installation aus und kann einen SMTP-Server auf dem localhost simulieren.
Links zum Thema
http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/
Pingback: SSIS: E-Mail-Versand mit geänderter Portnummer - SharePoint Blogs in German - Bamboo Nation