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.