Interaktion zwischen SQL Server Data Engine, Integration Services und Reporting Services
1. Abstract
Ein DWH Load wird normalerweise zeitgesteuert zu Randzeiten ausgeführt. Der Betrieb muss im Nachgang wissen, wie der Systemzustand ist. Folgende Fragestellungen ergeben sich:
- Welche Daten wurden geladen?
- Gibt es generelle Fehler oder Warnings?
- Wie sehen die Row Counts und die Durchlaufzeiten aus?
- Hat es Fehlvalidierungen gegeben?
- Konnten Daten oder Teilmengen davon nicht geladen werden?
Zudem ist die Frage zu klären, ob es (nach einer allfälligen Korrektur der Quelldaten) eine erneute Ausführung des Loads braucht oder ob es ausreicht, auf den nächsten regulären Importvorgang zu warten.
Um all die Bedürfnisse zu adressieren, haben wir in einem aktuellen DWH-Projekt entschieden, im Anschluss an jeden Load ein sog. Ladeprotokoll zu versenden. Dieses Protokoll wird gesteuert durch den SSIS Control Flow aufbereitet und per Mail versandt. Die Visualisierung der Auswertungen erfolgt mit Hilfe von SSRS.
Im nachfolgenden Blog-Eintrag wird Schritt für Schritt aufgezeigt, wie eine solche Lösung implementiert werden kann. Das Beispiel demonstriert, wie die Interaktion zwischen Data Engine, SSIS und SSRS erfolgt.
2. SSIS Control Flow
MasterPackage.dtsx
Der Ladeprozess wird durch ein übergeordnetes Master-Package (zeitgesteuert via SQL Agent) angestossen. Das Package besteht aus zwei Prozessschritten:
| 1. | DWHLoad.dtsx | Beliebig komplexe Aufbereitung der Daten. Tracking des Verlaufs in Audit-Tabellen. |
| 2. | Notification.dtsx | Auswertung des Verlaufs, Visualisierung der Daten, Versand des Protokolls |
In diesem Blog wird nicht auf die Struktur des Lade-Packages eingegangen, sie kann als beliebige Blackbox angesehen werden.
3. Notification
Im Notification.dtsx Package werden das Datenmuster des DHW-Loads ausgewertet und die relevanten Informationen per Mail versandt. Es besteht aus vier Tasks:
| 1. | Get configuration values | Lesen der Konfiguration, Übergabe der Werte an SSIS Variablen |
| 2. | Compose attachment | Aufruf des vorbereiteten SSRS Reports, Download des Exports auf das Filesystem |
| 3. | Log error | Allfällige Fehler aus der vorangehenden Sequenz werden geloggt. |
| 4. | Send mail | Versand des aufbereiteten Protokolls via SQL Mail |
Notification Control Flow (farblich hinterlegt: Kapitelreferenz)
3.1 Konfiguration
- Die nötigen Parameter für die die Aufbereitung und die Zwischenspeicherung des Reports werden in der Datenbank in Form einer Konfigurationstabelle hinterlegt.
Konfigurationstabelle etl.Configuration
- Per „Execute SQL“ Task werden diese ausgelesen und via Result Set in die entsprechenden SSIS Variablen geladen.
SQL Statement zur Abfrage der Konfigurationswert
Result Set
Mapping des Result Sets auf die SSIS Variablen
- Sobald der „Get configuration values“ Task erfolgreich ausgeführt ist, stehen die Informationen in der SSIS Umgebung zur Verfügung.
3.2 Visualisierung der Daten
- Die Aufbereitung der Protokoll-Informationen erfolgt unabhängig von jeglicher SSIS Logik in SSRS. Zu Design-Zeit, d. h. während der Entwicklung wird der Report gestaltet und die Visualisierung festgelegt.
- Nebst der Visualisierung werden die nötigen Datenabfragen formuliert und als sog. Datasets vorbereitet (darauf wird in diesem Blog nicht eingegangen).
Parameter und Datasets (Design-Zeit)
- Der Demo-Report besteht aus fünf Datasets mit folgenden Zwecken:
| 1. | LoadRuns | Enthält alle Ladeläufe; wird zur DropDown Auswahl benötigt |
| 2. | ImportFiles | Zeigt alle importierten Dateien |
| 3. | PackageRowCounts | Liefert die RowCounts der einzelnen Packages |
| 4. | LogMessages | Zeigt die applikatorischen Logs, deklariert mit den LogType Werten „DEBUG“, „INFORMATION“, „WARNING“ (Default), „ERROR“ |
| 5. | FailedColumns | Zeigt fehlvalidierte Spalten der einzelnen Files |
Parameter und Datasets
- Der Parameter @MasterPackage kommt in allen Datasets zur Anwendung und filtert die Datenmenge auf den gewünschten Ladelauf. Er ist zwingend anzuliefern.
- Mit @LogType kann zusätzlich die Granularität des Datensets „LogMessages“ gesteuert werden. Dieser Parameter ist optional.
SSRS Interaktion (Lauf-Zeit)
- Aus obigem Abschnitt geht hervor, dass der Parameter @MasterPackage für die ordnungsgemässe Ausführung des Reports benötigt wird und demzufolge beim Aufruf angeliefert werden muss.
- Weil SSRS seine Funktionen auch über ein Web Service Interface zur Verfügung stellt, kann ein Report auch ohne User Interaktion ausgeführt bzw. heruntergeladen werden. Voraussetzung ist die Anlieferung aller zwingenden Parameter. In vorliegendem Beispiel wird der SSRS Web Service über einen URL Request angesprochen.
Die URL stellt sich wie folgt zusammen:
- Konkret ergibt dies für vorliegendes Beispiel mit Zielformat EXCEL:
- Die Zusammenstellung dieser URL kann über den Expression Editor gelöst werden. Es wird auf die vorgängig aus der Konfiguration gelesenen Parameter (= URL Bausteine) zurückgegriffen.
Komposition der Variable ReportDownloadURL per Expression
URL zur Laufzeit
3.3 Generierung des Attachments
- Sobald die URL korrekt zusammengesetzt ist, kann der Aufruf derselben per Programmcode erfolgen. Zu diesem Zweck nutzen wir einerseits einen „HTTP Connection Manager“ und andererseits die Funktionalitäten eines SSIS Script Tasks bzw. des .NET Frameworks.
HTTP Connection Manager Settings
- Der HTTP Connection Manager wird mittels Expressions mit den Werten aus den SSIS Variablen initialisiert (siehe Abschnitt Konfiguration). Innerhalb des Script Tasks „Compose attachment“ wird diese Komponente dann für die Protokoll-Generierung genutzt.
- Programmcode „Compose attachment“ Script Task:
- Nachfolgende Tabelle dokumentiert den Programmcode des Script Tasks „Compose attachment“:
| Zeile | Dokumentation |
| 88/89 | Instanzieren einer HttpClientConnection mit Hilfe der vorkonfigurierten Komponente im SSIS Connection Manager |
| 92 – 102 | Initialisieren der lokalen Variablen mit den Werten aus den SSIS Variablen |
| 104 – 107 | Setzen der ReportServer URL und Herunterladen der Datei |
| 110 | Prüfung, ob (Download-)File existiert |
| 113 – 115 | Setzen der Werte für die out-Parameter |
| 126 – 128 | Setzen der Werte für die out-Parameter (Fehlerfall) |
- Nach erfolgreicher Ausführung des Script Tasks „Compose attachment“ ist das Ladeprotokoll als File auf dem Filesystem verfügbar
Generiertes Attachment
3.4 Mailversand
- Für den Mailversand wird die vorkonfigurierte SQL Mail Komponente des SQL Servers vorausgesetzt.
Database Mail
- Ist “Database Mail” richtig konfiguriert und getestet, kann über die Prozedur msdb.dbo.sp_send_dbmail ein Mailversand ausgelöst werden.
- Die Parameter der Prozedur lassen sich per T-SQL Programmcode mit den richtigen Werten speisen. Relevant für vorliegendes Beispiel sind dies der Empfänger, die CC-Adresse, das zu nutzende SQL Mail Profile sowie der Pfad der als Attachment anzuhängenden Datei.
- Um eine gewisse Flexibilität im Betrieb zu gewährleisten, sind diese Werte als konfigurierbare Informationen in der bekannten etl.Configuration Tabelle hinterlegt.
- Die Interaktion mit sp_send_dbmail erfolgt im vorliegenden Demosystem aus der Prozedur spSendMail (siehe nachfolgenden T-SQL Screenshot).
- Die Parameter für den Mailbody- und den Mailsubject-Text speisen wir als Konstante innerhalb des T-SQL Codes. Allenfalls ist hier auch die dynamische Aufbereitung von Text aus dem Data-Pattern des Ladelaufes denkbar.
Konfigurationen für den Mailversand (etl.Configuration)
- Die SSIS Variable „ReportDownloadFullFileName„ (siehe Script Task Zeile 127) enthält den Pfad zum heruntergeladenen SSRS File. Sie wird an die spSendMail resp. sp_send_dbmail Prozedur übergeben
Übergabe des Pfad-Parameters an die Stored Procedure
- Programmcode spSendMail Prozedur:
- Nachfolgende Tabelle dokumentiert den Programmcode der Prozedur spSendMailDemo:
| Zeile | Dokumentation |
| 16/17 | Übergabe der Parameter @AuditKey und @AttachmentFullPath (entspricht dem File-Pfad des SSRS Downloads im SSIS Script Task) |
| 27 – 29 | Lesen der benötigten Konfigurationen |
| 33 – 35 | Zusammensetzen von Body- und Subject-Text. Allenfalls können hier auch Datenmuster-abhängige Texte komponiert werden. |
| 39 – 46 | Nutzen der sp_send_dbmail Prozedur zum Versand des Protokolls @profile_name muss dem Namen eines auf dem SQL Server vorkonfigurierten Mail-Profils entsprechen @file_attachment gibt den Pfad des Attachments an, das dem Mail angehängt werden soll |
Email mit Load-Protokoll
4. Fazit
- SQL Server unterstützt mit seinen Modulen Data Engine, SSRS und SSIS den interdisziplinären Aufbau von Systemen.
- Zur Lösung der vorliegenden Aufgabenstellung leistet jedes Modul (im Bereich seiner Kernkompetenz) seinen Beitrag.
- In der Datenbank werden die Konfigurationen bereitgestellt sowie das Datenmuster des ETL Loads hinterlegt. Zudem erfolgt der Versand der Informationen via SQL Database Mail.
- Mit SSIS wird der gesamte Workflow abgebildet und das Fehlerhandling sichergestellt. Zudem bietet SSIS die Interaktion mit SSRS an.
- SSRS visualisiert die vom ETL Load produzierten Daten und stellt via Web Service Funktionalität die Möglichkeit zum Download von vorbereiteten Reports zur Verfügung.
5. Quellen und weiterführende Links
- https://msdn.microsoft.com/de-de/library/ms153586(v=sql.120).aspx
- https://msdn.microsoft.com/de-de/library/ms154040(v=sql.120).aspx
- https://sqlserverrider.wordpress.com/2013/02/15/generate-pdf-report-from-ssis/
- https://www.mssqltips.com/sqlservertip/3654/how-to-modify-sql-server-database-mail-accounts/
- https://msdn.microsoft.com/de-de/library/ms190307(v=sql.120).aspx