Archiv für den Monat: April 2018

MS Integration Services

SSIS: Fehlerbehebung bei „Excel Destination schreibt keine Zeilen“

Schreibe einen Kommentar

In einem aktuellen Projekt leiten wir Daten in eine Excel-Datei aus, um sie Benutzern – wie bisher gewohnt – zur Verfügun gzu stellen. Dabei verwenden wir als Excel-Ziel (Excel destination) eine existierende Excel-Datei (Template), in die wir auf einen Reiter in eine Tabelle Daten einfüllen .

Leider zeigt dann die Excel Destination ein ziemlich seltsames Verhalten:

  • Im Visual Studio funktioniert das Schreiben der Datei einwandfrei.
  • Richtet man einen SQL Server Agent Job ein, der in die Datei schreibt, funktioniert es mal und mal auch nicht. (Kleinere Datenmengen funktionieren, größere Datenmengen funktionieren nicht).
    Dabei wird aber kein Fehler geschmissen. Das ETL läuft feherfrei durch. Zwar „sagt“ SSIS, es hätte Daten in die Datei geschrieben, aber es kommen keine Daten an.
  • Lasse ich den SQL Server Agent Job unter meinem Account laufen (indem ich einen Proxy anlege), so funktioniert es wieder.

Im Internet findet man etliche User, die ein ähnliches Fehlerbild haben. Bei einem Post war ein Hinweis, der mich zur Lösung brachte: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b8970522-45ac-481c-a900-14f57f37781b/excel-destination-blank-but-logging-says-it-wrote-14000-rows?forum=sqlintegrationservices

In einer Antwort wird auf diesen Artikel verwiesen: http://stackoverflow.com/questions/23523953/empty-excel-file-permissions-issue-ssis-excel-destination-buffers-large-record

Fehlerursache

Zwar entspricht dieser Post nicht meiner Situation, führte mich aber auf die richtige Fährte:

Wenn dtexec.exe unter einem nicht-angemeldeten User ausgeführt wird (wie z.B. beim Start durch den SQL Server Agent), so verwendet die Excel Destination – ab einer bestimmten Größe – eine temporäre Datei. So weit so gut. Nur leider versucht es, diese Datei unter

c:\users\default\AppData\Local\Microsoft\Windows\...

anzulegen. Darauf hat aber normalerweise der ausführende User keinen Zugriff.

Nachdem ich dem User Vollzugriff auf dieses Verzeichnis gegeben hatte, funtionierte es.

Vorgehen zum Finden des Fehlers

Wie im dem oben genannten Post beschrieben, habe ich auch den Process Explorer von sysinternals verwendet (Download von Microsoft-Seite).

Das Problem ist, dass man zunächst sehr viele Informationen erhält.

Filtert man dort auf den User, unter dem der ETL ausgeführt wird, ist das Bild schon üebrsichtlicher (wenn – wie bei uns – ein eigener Applikations-User verwendet wird).

Dann kann man noch die erfolgreichen Zugriffe ausblenden
(RESULT is SUCCESS –> Exclude) oder gar nur die ACCESS DENIED-Ergebnisse anzeigen
(RESULT is ACCESS DENIED –> Include)

Dann findet man es sehr schnell, wie folgender Screen Shot zeigt:

Ebenfalls interessant

Bei der Suche nach dem Fehler fand ich auch Posts, in denen die User meinten, die Excel-Datei sei leer, weil SSIS die Daten nicht oben eintrug, sondern erst nach etlichen 1000 Zeilen.

Wir hatten das gleiche Phänomen. Man muss aufpassen, dass keine Leerzeilen oben in der Datei sind. Dazu kann man einfach über Ctrl-End an das Ende springen und alle überzähligen Zeilen löschen. Somit fügt SSIS bereits die Daten oben – gut sichtbar – ein.

MS Analysis Services, MS Integration Services

Migration SSAS auf 2016: Übernahme der Display Folder

Schreibe einen Kommentar

In SSAS 2016 werden endlich Display Folder nativ unterstützt. Bisher war das nur durch die Verwendung des BIDS Helper-Projekts (s. https://bidshelper.codeplex.com/ bzw. https://bideveloperextensions.github.io/) möglich.

Nun stellt sich aber die Frage, wie man bei einer Migration von einer früheren SSAS-Version auf SSAS 2016 die dortigen Display Folder überträgt, da sie leider bei einer „normalen“ Migration via Visual Studio verloren gehen, da Visual Studio die BIDS-Helper-spezifischen Einstellungen (BIDS Helper speichert diese Informationen als Annotations in einem eigenen Format) nicht kennt.

Bei der Suche nach einer Antwort fand ich die Seite http://www.kapacity.dk/migrating-ssas-tabular-models-to-sql-server-2016-translations-and-display-folders/.
Dort wird – kurz zusammengefasst – vorgeschlagen, das neue Objekt-Modell zu verwenden, um die Display Folder zu schreiben. Das (jetzt in SSAS 2016 sehr einfache) Objekt-Modell habe ich bereits in meinem letzten Blog-Eintrag beschrieben. Der Artikel schlägt vor, die Annotations vom BIDS Helper zu parsen und daraus die zu verwendenden Display Folder zu ermitteln.
Diese Display Folder werden dann in den – bereits bereitgestellten – Cube geschrieben. Um daraus wieder eine Solution zu erhalten, muss man dann nur noch eine neue Solution aus diesem Cube erstellen.

Ich halte den Weg sehr elegant, das Objektmodell zu verwenden. Allerdings halte ich das Parsen der Annotations für unelegant und fehleranfällig. Deswegen gehe ich einen anderen Weg:
Nachdem wir den migrierten Cube deployt haben, haben wir ja zwei Cubes in unserer Umgebung:
A: der alte Cube (z.B. SSAS 2012 / 2014) mit Display Folders (BIDS)
B: der neue Cube SSAS 2016 ohne Display Folder

Nun habe ich ein einfaches SSIS-Paket erstellt, das über DMVs (Dynamic Management Views) auf den alten Cube A zugreift und für alle Measures und (echte oder berechnete) Columns die Display Folder ausliest und sie dann über das Objektmodell, wie im vorletzten Beitrag beschrieben, in den neuen Cube schreibt.

Dabei verwende ich folgende DMVs:

  • $SYSTEM.MDSCHEMA_MEASURES für Measures: Dort interessieren mich alle Measures (MEASURE_NAME) und ihre Display Folder (MEASURE_DISPLAY_FOLDER), sowie der MEASUREGROUP_NAME. Im Tabular Model entspricht der MEASUREGROUP_NAME dem Tabellennamen. Als Cube filtern wir auf „Model“.
  • $System.MDSCHEMA_HIERARCHIES für Columns: Hier interessieren mich HIERARCHY_CAPTION, HIERARCHY_DISPLAY_FOLDER. Als Cube filtern wir auf alles außer „Model“. Dies funktioniert nur in den Versionen 2012 und 2014 (aber das ist hier ja genau der Fall). Dann tauchen nämlich alle Tabellen als CUBE_NAME auf – mit vorangestellten $ (also $Currency für Tabelle Currency). Jede Spalte steht dann in HIERARCHY_CAPTION.

Zu beachten ist, dass teilweise in deutschen Versionen der Cube nicht „Model“ sondern „Modell“ heißt. Dann müssen die Abfragen natürlich entsprechend angepasst werden.

Der Code zum Aktualisieren ist dann sehr einfach:
Der neue Cube (und Server) sind Variablen im SSIS-Paket.
Der Code verbindet sich darauf und holt dann das Model in die Variable _model.
Letzlich ist der Code dann nur:
Table table = _model.Tables[tableName];
if (table.Measures.Contains(measureName))
{
Measure m = table.Measures[measureName];
m.DisplayFolder = displayFolder;
}

Und zum Abschluss (also im PostExecute) werden die Änderungen auf den Server gespielt:
_model.SaveChanges();
Für Columns funktioniert es analog.

Für Details zu den einzubindenden DLLs, s. mein früherer Blog-Eintrag.

Im Übrigen habe ich auf einen Fehler verzichtet, falls ein Measure oder eine Column nicht im neuen Cube B vorhanden ist. Bei uns hatte das den Hintergrund, dass wir etliche Spalten / Measures für Fremdwährungen hatten, die wir per Code erstellten, so dass sie in A vorhanden, in B aber (noch) nicht vorhanden waren. Diese nicht in B gefundenen Spalten/Measures werden von dem jeweiligen Skript ausgegeben.

Zusammenfassend gehe ich also wie folgt vor:
1. Migration der Solution auf SSAS 2016 – dadurch gehen die Display Folders verloren
2. Deploy auf einen Entwicklungs-SSAS
3. Kopieren der Display Folder von der alten Cube-Instanz auf die neue
4. Erstellen einer neuen Solution aus dem nun fertigen Cube.

Das beschriebene ETL-Paket habe ich hier als zip inkl. Solution oder hier nur als dtsx-File hochgeladen. Um es zu verwenden, muss man die beiden Variablen mit den Infos zum 2016er Cube und die Connection auf den 2012er Cube anpassen.