Archiv der Kategorie: SQL Server Agent

Azure Cube-Verarbeitung von on premise

In einem Projekt, in dem die gesamte Datenhaltung und Datenverarbeitung on premise war (SQL + SSIS), haben wir einen Azure Analysis Services-Cube benutzt.

Für die Verarbeitung dieses Cubes haben wir nach einfachen Wegen gesucht, wie wir die Cube-Verarbeitung von SSIS / SQL Agent aus anstarten könnten.

Im Web findet man ja einiges zu Cube-Verarbeitung von Azure Analysis Services-Cubes (z.B. Process Azure Analysis Services Models with Azure Data Factory v2 oder Automating Azure Analysis Services processing with Azure Functions). Die erste Methode haben wir übrigens in einem anderen Projekt (das Azure Data Factory verwendete) verwendet. Allerdings hat dies den Nachteil, dass die ADF Managed Service Identity als Administrator des Analysis Services eingetragen werden muss – also mehr Rechte braucht als eigentlich nötig.

Aber für unseren Fall wollten wir eine möglichst einfache Lösung innerhalb SSIS.

Zunächst schauen wir uns an, wie wir einen On-Prem-Analysis-Services-Cube verarbeiten würden. Dazu reicht im SSIS eine Execute-SQL-Task mit folgenden Einstellungen:

  • Connection ist eine OLE DB-Connection, z.B. mit folgendem Connection-String: Data Source=<ServerName>;Initial Catalog=<Cube-Datenbank-Name>;Provider=MSOLAP.7;Integrated Security=SSPI;
Beispiel für eine OLE DB Connection auf den Cube
Connection auf SSAS
  • Im Feld „SQL-Statement“ trägt man den SSAS-Befehl ein (den man sich z.B. im SQL Server Management Studio skripten lassen kann):
{"refresh": 
{"type": "full",
"objects": [
{"database": "NetzwerkControlling"}
]
}
}

Für eine Verarbeitung eines Azure-Cubes können wir ganz genauso vorgehen. Nur der ConnectionString ist zu ändern:

  • Als Data Source ist der Azure Analysis Service einzutragen, also irgendwie so: asazure://westeurope.asazure.windows.net/<Server>
  • Wir müssen einen User angeben, da wir nicht den User verwenden können, unter dem der Prozess on prem läuft. Also sieht unser ConnectionString so aus:
    Data Source=asazure://westeurope.asazure.windows.net/<Server>;User ID=<Username>@<Azure Active Directory>;Initial Catalog=<Cube-Datenbank-Name>;Provider=MSOLAP.8;Persist Security Info=False;
  • Dann müssen wir in dem Attribut Password der Connection das Passwort dieses Users mitgeben.

Natürlich wollen wir das Passwort nicht im plain text irgendwo stehen haben. Deswegen reichen wir das Passwort über einen Paket-Parameter herein, den wir als sensitive (vertraulich) definieren. Dann können wir bei der Konfiguration im Katalog oder im SQL Server Agent das Passwort eintippen und es ist sicher in der SSISDB gespeichert:

Im Paket sieht das so aus:

Paket-Parameter „Passwort“ – als vertraulich definiert
Eigenschaften der SSAS-Connection in SSIS
Eigenschaften der SSAS-Connection in SSIS

In diesem Fall habe ich sogar den Connection-String auch noch von außen hereingereicht.

Bei der Konfiguration im SQL Agent stellt man dann das Passwort ein:

Konfiguration des Paket-Aufrufs im SQL Server Agent mit Passwort-Eingabe

Damit können wir mit dem gleichen Code lokale und Azure-Analysis-Services-Cubes verarbeiten – lediglich den Connectionstring mussten wir anpassen und im Azure-Analysis-Services-Fall das Passwort mit angeben.

Dies hat folgende Vorteile:

  • einheitliche Code-Basis – ein Code für beides
  • Passwort sicher gespeichert
  • im Gegensatz zu obiger Azure Data Factory-Variante
    • der User benötigt nur process-Rechte auf dem Cube
    • Der Aufruf ist automatisch synchron, so dass man Fehler einfach mitbekommt.

SSIS 2016: „Access Denied“ bei „Execute Package“-Task mit „OutOfProcess“ = true

In Integration Services gibt es die Möglichkeit, aus einem Paket heraus ein Kind-Paket zu starten.

Ein Kind-Paket aufrufen

Mit Project Deployment und SQL Server 2016 geht das wunderbar.

Bei einem Kunden habe ich dieses Feature dazu genutzt, als Kind-Paket ein Paket aufzurufen, das beliebige SharePoint-Listen in die Datenbank abzieht (gesteuert durch Paketparameter, die man beim Aufruf des Kind-Pakets setzt) oder parallel aus mehreren SAP-Datenbanken Daten zusammenzusammeln.

Das Feature ist an sich selbst erklärend. Ich möchte hier aber auf ein Problem eingehen, das man in einer bestimmten Konstellation hat – wenn man nämlich Execute Out Of Process auf true setzt:

Parameter „ExecuteOutOfProcess“

Dieser Parameter bewirkt, dass für die Paketausführung ein eigener Prozess gestartet wird. Dies hat den Vorteil, dass dann MultiThreading durch das Betriebssystem und nicht mehr durch SSIS gesteuert wird. (Ggf. ist auch das Verhalten bei einem Absturz besser.)

Bei meinen Tests funktionierte das wunderbar,

  • sowohl innerhalb von Visual Studio
  • als auch nach dem Deployment in die SSISDB über den SQL Server Agent

Sobald ich aber den User umstellte auf einen User, der kein Administrator war, erhielt ich folgende Fehlermeldung:

Ausschnitt aus dem Ausführungs-Bericht mit allen Meldungen

Oder genauer ein Access-Denied:

Fehlermeldungen

Da ich im Web dazu nichts gefunden habe, habe ich mich entschlossen, diesen Blog-Eintrag zu schreiben, nachdem ich die Lösung herausgefunden hatte.

Der erste Hinweis, woher der Fehler kommt, war die Fehlermeldung bzgl. DCOM. Deswegen habe ich versucht, DCOM-Fehlermeldung im Eventlog zu finden. Aber da waren keine – man muss das erst aktivieren, und das geht so:

Ich habe mich an die Anleitung in diesem Artikel gehalten. Ich zitiere hier, falls dieser Eintrag nicht mehr auffindbar ist:

  1. Open the Windows Registry (regedit.exe)
  2. Browse to this registry key:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Ole
  3. Create a new DWORD called ActivationFailureLoggingLevel with a value of ‚1‘
  4. Create a new DWORD called CallFailureLoggingLevel with a value of ‚1‘

Damit war das Logging im Eventlog eingeschaltet und beim nächsten Start des SSIS-Pakets fand ich – zu der passenden Uhrzeit – im Eventlog (System) folgende Fehlermeldung:

Eventlog
Fehlermledung im Eventlog

Man sieht also zwei GUIDs, den betroffenen User und „Local Activation“ fehlt.

Um diesen Fehler zu beheben, müssen wir erst einmal herausfinden, wozu die CLSID gehört. Dazu suchen wir in der Registry (regedit.exe) nach
979F7D05-1E4F-4EE4-AF90-EDDC1098839D und finden:

Ergebnis der Suche nach
979F7D05-1E4F-4EE4-AF90-EDDC1098839D in der Registry

Wir sehen also „Package Remote Class (64-bit)“ – „Package“ erinnert einen an SSIS-Paket. Wir sind also auf der richtigen Fährte.

Nebenbemerkung: Falls man das Paket in 32-bit ausführt, würde natürlich hier (32-bit) stehen.

Als nächstes startete ich die Component Services und klappte „DCOM Config“ auf:

Component Services (aus Administrative Tools heraus) starten
DCOM Config aufklappen

Dort habe ich dann versucht, „Package Remote Class“ zu finden. Leider gibt es das aber nicht. Aber ich habe
„DTS-Package Host (64-bit)“ gefunden – allerdings 3x auf diesem Server.

Deswegen habe ich über rechte Maustaste die Eigenschaften aufgerufen. Durch die Kontrolle der APPId (die sowohl im Eventlog als auch in der Registry steht) war ich mir sicher, die richtige Klasse gefunden zu haben:

Die Eigenschaften vom DTS Package Host auswählen …
… und die Application ID mit der APP ID vergleichen.

Da es die richtige Komponente ist, wechseln wir auf den Reiter Security und stellen für den gewünschten User (der das Paket bzw. den SQL Server Agent Job ausführt) „Local Activation“ ein:

Local Activation-Rechte vergeben

Danach lief der Job bzw. das Paket ohne Fehler durch. 🙂

Unerwartetes Nicht-Logging SSIS

Dieser Artikel widmet sich nicht dem Logging, das man selbst in einem Paket aktivieren kann und über Log-Provider detailliert steuern kann.

Gemeint ist mehr das Logging, das SSIS automatisch ohne Zutun des Entwicklers oder Administrators durchführt, s. dazu auch „Understanding Events Logged by an Integration Services Package„.

Standardmäßig loggt jedes SSIS-Paket den Start, das erfolgreiche Ende oder das Fehlschlagen oder Abbrechen eines SSIS-Pakets:

EventLog Start und Failure eines SSIS Pakets

(Zum Vergößern auf das Bild klicken)

Details des Eventlogs beim Scheitern eines Pakets

(Randbemerkung: Das Logging von Start und erfolgreichem Ende lässt sich abschalten – in der Registry HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server100SSIS, Wert von „LogPackageExecutionToEventLog“ auf 1 setzen – die anderen Einträge lassen sich nicht verhindern)

In dem Beispiel hatte ich ein SSIS-Paket erstellt, das nur aus einer Script-Task bestand, die immer einen Fehler schmeißt:

Script-Task zum Fehler schmeißen

Dort ist folgender Code enthalten:

public void Main()
{
Dts.Events.FireError(999, „SubKomponente A“, „Fehler – Test fehler“, „“, 0);
Dts.TaskResult = (int)ScriptResults.Success;
}

Das Ergebnis ist oben zu bewundern.

Nun hatten wir die Idee, dieses Eintrag im Event Log zu überwachen (mit NetIQ oder einem anderen Werkzeug zur Überwachung von Servern, Diensten, etc.) und so zu erfahren, wenn ein Paket gescheitert ist.

Das funktioniert aber leider nicht.

Der Grund ist, dass ein solcher Eintrag im Eventlog nur erzeugt wird, wenn das Paket bereits angelaufen ist. Wenn aber schon bei der Validierung ein Fehler auftritt, wird kein Eintrag im Eventlog erzeugt.

Dazu habe ich das Paket erweitert um einen Data Flow Task, bei dem ich in der Quelle „SELECT 1 as Zahl“ eingetragen habe. SSIS Paket mit Fehler brerits beim Validieren

Dieses Paket habe ich dann im SQL Server Agent als Job eingerichtet. Dabei habe ich eingestellt, dass im Fehlerfall der SQL Server Agent das Ergebnis im Eventlog festhalten soll:

Properties des SQL Server Agent Jobs zum Protokollieren im Event Log

(Zum Vergößern auf das Bild klicken)

Solange die Quelle des SSIS-Pakets bekannt ist, ist alles wie erwartet. Im Eventlog werden folgende 3 Events festgehalten (da ja mein Script-Task dazu führt, dass das Paket auf einen Fehler läuft):

Die Events, die durch ein Paket mit vorhandener Connection erzeugt werden

Die Meldungen sind:

  1. Starten des SSIS Pakets (Information)
  2. Fehler des SSIS Pakets (Fehler)
  3. Fehler des SQL Server Agent Jobs (Warnung)

Letzte Meldung sieht im Detail so aus:

Ereignis-Details der SQL Server Agent-Meldung, dass der Job nicht erfolgreich war

Wenn ich jetzt aber die Connection des Statements „SELECT 1“ ändere, so dass der dort angegebene Computer nicht existiert, passiert folgendes, wenn das Paket im SQL Server Agent gestartet wird:

Ereignisanzeige, wenn die Connection des SSIS Pakets nicht validiert werden kann

Man sieht, dass die SSIS-Eventlog-Einträge nicht vorhanden sind. Dies liegt daran, dass der Fehler beim Validieren auftritt und deshalb das Paket noch gar nicht angelaufen ist.

Deswegen macht es keinen Sinn, die SSIS-Eventlog-Einträge für eine Überwachung der Jobs zu verwenden.

Stattdessen können die SQL Server Agent-Einträge überwacht werden. Dabei ist aber zu beachten, dass dies bei jedem SQL Server Agent Job – wie oben gezeigt – eingestellt werden muss.

Automatisierung von Analysis Services-Aufgaben über XMLA

Alle Aufgaben, die man im SQL Server Management Studio für den Betrieb eines SQL Server Analysis Services vornehmen kann, können auch über XMLA gesteuert werden.

Das gilt zum Beispiel für das Aufbereiten (Verarbeiten, process) von Cubes oder dem Backup von Datenbanken. Ich werde mich heute mit dem Backup einer Analysis Services – Datenbank beschäftigen.

Das Schöne ist, dass wir gar nicht die Syntax der XMLA nachschlagen müssen. Das SQL Server Management Studio erstellt uns nämlich den notwendigen XMLA-Code automatisch (und das gilt für beide Versionen 2005 und 2008). Dazu wählen wir zunächst im Kontextmenü die gewünschte Aktion aus:

BackUp in SQL Server Management Studio

Dann startet sich ein Popup-Fenster, in dem wir die gewünschten Einstellungen durchführen, aber nicht auf OK klicken.

Script Button oben

Im oberen Bereich befindet sich eine Script-Button. Wenn man auf diesen klickt, wird das zugehörige XMLA erstellt:

Backup-XMLA

Dies kann man jetzt sogar direkt im SQL Server Management Studio ausführen (Execute!).

Bei Erfolg liefert die Ausführung folgendes Ergebnis:

<return xmlns=“urn:schemas-microsoft-com:xml-analysis“>
<root xmlns=“urn:schemas-microsoft-com:xml-analysis:empty“ />
</return>

Das Problem (auf das wir später noch zurückkommen werden) ist, dass auch bei einem Fehler in der Regel kein Fehler geschmissen wird (bei Analysis Services 2008 kommen manchmal Fehler vor), sondern ein XML zurückgegeben wird, in der die XML-Knoten Exception oder Error oder ähnliches auftauchen. Wie gesagt, dazu später mehr.

Wie können wir nun ein solches XMLA automatisiert (zeitgesteuert) aufrufen?

Die einfachste Möglichkeit ist über den SQL Server Agent. Wir legen dazu einen neuen Job an. Der erste Schritt des neuen Jobs sieht so aus:

Step zum Backup via XMLA

Wie man in obigem Screen Shot sieht, muss als Typ „SQL Server Analysis Services Command“ ausgewählt, als Server dergewünschte SQL Server Analysis Services-Server (hier im Beispiel ssasentsql2008) eingetragen und das XMLA in die große Textbox Command kopiert werden.

Damit kann dieses XMLA im Rahmen eines SQL Server Agent Jobs ausgeführt werden.

Im Log File Viewer kann man nach der Ausführung das ERgebnis der Ausführung wie folgt erkennen:

Log File Ergebnis der Ausführung eines XMLA Befehles

Was ich markiert habe, ist genau das Ergebnis, das wir vorhin auch als Ergebnis bei der Ausführung im SQL Server Management Studio gesehen hatten. Hier erkennt man, dass die Ausführung erfolgreich war, weil das Ergebnis „empty“ ist. Natürlich wird der Job als erfolgreich beendet angezeigt.

Das Problem hierbei ist, dass bei Fehlern bei der Ausführung der Job ebenfalls als erfolgreich abgeschlossen angezeigt wird und die Fehlermeldung nur an dieser Stelle im Log sichtbar ist.

Ausgabe, wenn das XMLA einen Fehler geliefert hat

Man erkennt deutlich, dass der Step als erfolgreich markiert ist, aber offensichtlich nicht erfolgreich durchgeführt wurde. Die Fehlermeldung habe ich markiert. (In dem Beispiel handelte es sich um das Aufbereiten einer nicht existenten Datenbank)

Dies ist natürlich sehr ungünstig, da Administratoren auf das Fehlschlagen eines Jobs reagieren können, aber nicht auf irgendwo enthaltene Fehlermeldungen. Deswegen empfehle ich, in produktiven Umgebungen das XMLA nicht direkt im SQL Serevr Agent auszuführen, sondern, wie gleich beschrieben im SSIS. Im SQL Server Agent 2008 scheint dieses Problem behoben zu sein. Wenn man obigen fehlerhaften Job im SQL Server Agent 2008 anlegt (sogar auch wenn man als Ziel des XMLA sogar einen 2005er Analysis Services wählt), wird der Fehler im SQL Server Agent erkannt und sinnvoll protokolliert, wie man in folgendem Screen Shot sehen kann:

Fehler in XMLA korrekt erkannt

Nun aber zu einer anderen Möglichkeit, das XMLA auszuführen, als Integration Services Package im SSIS:

Im Control Flow gibt es dort eine Task mit Titel Analysis Services Execute DDL Task. Diese benötigt eine Cube-Connection und das zu automatisierende XMLA. Das XMLA kann dabei direkt eingegeben oder aus einer Variable oder aus einem File ausgelesen werden. Letzters wird bei großen XMLAs benötigt, da sonst die Größenbeschränkung auf ca. 4000 Zeichen besteht.

Bild

Unter diesem Link habe ich ein einfaches Paket zum Download bereit gestellt, dass nach Eingabe einiger Variablen das XMLA automatisch erstellt (über Expressions der Execute SSAS DDL Task) und dann ausführt. Die Variablen sind im beigefügten Config-File enthalten, so dass Sie das Paket auch einfach über die Anpassungen an dieser Config-Datei steuern können.

Als Variablen werden verwendet:

  • CubeDatenbank: Die SSAS-Datenbank, die gesichert werden soll.
  • CubeServer: Der Name des SSAS-Servers, auf dem sich die zu sichernde Datenbank befindet.
  • Dateiname: Name der zu erstellenden Datei
  • MitKompression: Soll die Datei komprimiert werden? (im Config-File 0 für nein, 1 für ja eintragen)
  • mitUeberschreiben: Soll evtl. eine bereiots existierende Datei überschrieben werden? (im Config-File 0 für nein, 1 für ja eintragen)
  • BackupPasswort: Geben Sie das an, wenn Sie Ihr Backup verschlüsseln wollen, sonst leer lassen (dann wird nicht mit leerem Passwort verschlüsselt 🙂 )

Variablen fürs SSIS Backup

Ganz analog können alle XMLAs mit SSIS ausgeführt werden.

Verschlüsselungs-Fehlermeldung beim Ausführen von SSIS-Paketen

Ein gängiges Szenario für das produktive Ausführen von SSIS-Paketen ist die Ausführung im SQL Server Agent.

Für Verwirrung sorgt dabei häufig eine Fehlermeldung, dass der Knoten aufgrund eines Verschlüsselungsfehlers nicht geöffnet werden kann. Falls ein Paket nicht ausgeführt werden konnte, führt das leicht auf eine falsche Fährte.

kryptographischer Fehler

Dieser Fehler ist nämlich nicht Ursache dafür, dass das Paket auf einen Fehler gelaufen ist. Dieser Fehler tritt in folgender Situation auf:

  • Das Paket wird durch einen User geöffnet, der das Paket nicht gespeichert hat.
  • Das Paket enthält eine Verbindung, die ein Passwort gesetzt hat – auch wenn diese Verbindung gar nicht verwendet wird (weil die Verbindungsinformationen z.B. durch eine Konfigurationsdatei o.ä. überschrieben werden.)
  • Die Einstellungen des Pakets (ProtectionLevel) stehen auf „encrypt sensitive with user key“

Dann versucht Integration Services nämlich alle sensitiven Daten – das ist vor allem das Password in einer Verbindung – mit dem aktuellen User zu entschlüsseln – und scheitert und protokolliert den Fehler. Integration Services bricht dann aber nicht die Verarbeitung ab, sondern verwendet dann ein leeres Passwort. In meinem Beispiel-Screenshot war der tatsächliche Fehler eine (bewusst herbeigeführte) Division durch Null. Diesen Fehler sieht man ganz am Ende.

Im produktiven Umfeld ist das im Normalfall kein Problem, da normalerweise Integrierte Sicherheit (also Anmeldung mit dem Windows-User) verwendet wird oder die Verbindungsinformationen (Server, Datenbank und eben auch User und Passwort) extern verwaltet werden (also in einem Config-File oder im SQL Server Agent etc.). Letzteres hat den Grund, dass man Pakete auf dem Produktivsystem ohne Veränderung (d.h. ohne Öffnen im Visual Studio) aus dem Testsystem installieren will.

Somit kann man einfach die Einstellung auf „Do not save sensitive“ stellen. Dann tritt der oben beschriebene Fehler nicht auf und die verwirrende Fehlermeldung in der Task History des SQL Server Agent taucht nicht auf. In meinem Beispiel erkennt man die Division durch 0 jetzt viel besser:

Fehlermeldung Division durch 0 viel leichter erkennbar

 

Um allerdings eine gute Grundlage für die Fehlersuche zu haben, reicht auch die Anzeige im SQL Server Agent nicht aus. Man sollte besser Logging aktivieren – (dazu vielleicht später mehr 🙂 )

SSIS Pakete im SQL Server Agent unter einem eigenen User starten

Zur zeitzgesteuerten Verarbeitung von SSIS-Paketen bietet sich der beim SQL Server 2005 mitgelieferte SQL Server Agent an. Wie man im Screen Shot sieht, bietet der SQL Server Agent an, die SSIS-Pakete unter einem bestimmten User auszuführen. Diese Kombobox („Run as“) ist im Standard allerdings auf den Benutzer-Konto des SQL Server Agent-Dienstes beschränkt:

Als RunAs steht nur das Benutzerkonto des SQL Server Agents zur Verfügung

Hier möchte ich zeigen, wie man diese Kombobox um einen beliebigen User erweitern kann und somit auch ein Paket unter diesem User ausführen kann.

Dazu muss zunächst ein Credential (ein Windows-Konto samt Kennwort) angelegt werden und dieser dann für die Ausführung von SSIS-Paketen freigegeben werden.

Das Credential gibt man im SQL Server Management Studio (wenn man auf den relationalen Datenbank-Server verbunden ist) unter Security > Credentials ein, also z.B.:

Ein Credential wird angelegt

Unter SQL Server Agent > Proxies – also hier:

Eingabe Proxies– muss dieser Credential nun für die Ausführung von SSIS-Paketen definiert werden. Dazu erstellt man einen neuen Proxy unter einem beliebigen Namen, der das neu eingegebene Credential verwendet. Bei den erlaubten Subsystemen setzt man den Haken bei den SSIS-Paketen (und allen weiteren gewünschten Systemen):

Erstellen eines Proxies mit Erlaubnis für SSIS-Pakete

Und schon kann das Paket im SQL Server Agent unter diesem Proxy ausgeführt werden:

Ausführung des Jobs unter dem neu angelegten Proxy