SSIS: Fehlerbehebung bei „Excel Destination schreibt keine Zeilen“

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.

Migration SSAS auf 2016: Übernahme der Display Folder

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.

SSAS 2016: Tabular Cube verarbeiten in SSIS

Nachdem sich in SSAS 2016 im Hintergrund einiges geändert hat, wird die Verarbeitung eines Cubes nun mittels JSON-Syntax und nicht mehr XMLA ausgeführt, z.B.
{
"refresh": {
"type": "automatic",
"objects": [
{
"database": "AMO2016Test"
}
]
}
}

Damit wird der Cube AMO2016Test verarbeitet.
(Ein solches Skript kann man sich wie gehabt im Management Studio über den Skript-Button in den Dialogen erzeugen lassen)

Grundsätzlich verwenden wir SSIS, um solche automatisierten Tasks durchzuführen (damit wir Standard-Features wie Protokolierung, Fehlerhandling etc. nutzen können).
Die Standard-Verarbeitungs-Komponente kann man nicht verwenden, da sie XMLA-basiert ist.

Aber man kann ganz einfach obigen Code in einer Execute-SQL-Task ausführen lassen, wenn man als Connection eine OLE-DB-Connection auf den Cube angelegt hat.
Es darf einen also nicht wundern, dass man einen JSON-Code in der Execute-SQL-Task ausführt. Da aber der Code einfach durchgereicht wird, ist klar, dass es funktioniert.

SSAS 2016 Tabular: Spalten, Measures etc. via C# erstellen

In einem Projekt erstellen wir Measures dynamisch: Für Währungen, die wir in einer relationalen Tabelle eintragen, werden automatisch Währungsumrechnungen aller Umsätze durchgeführt und dann im Cube automatisch angezeigt.

In SSAS vor der Version 2016 war das sehr kompliziert, da AMO (also das Objekt-Modell, auf das man via C# zugreifen konnte) noch dem MOLAP-Modell entspricht. Dort gab es also das Konzept z.B. berechneter Spalten nicht nativ. Deswegen gab es unter CodePlex ein AMO2Tabular-Projekt, mit dem versucht wurde, den Zugriff gekapselt zu ermöglichen.

In SSAS 2016 ist alles nun viel einfacher.

Hier ein Beispiel-Code (den wir in Integration Services eingebunden haten, da wir unsere Automatisierung als Teil unserer täglichen ETLs entwickelten):

Zunächst müssen Referenzen definiert werden:

  • AnalysisServices.Server.Tabular.dll
  • AnalysisServices.Server.Core.dll

Diese DLLs habe ich aus dem GAC genommen:
C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.AnalysisServices.Tabular\v4.0_14.0…..
//using System.Data; - sonst ist DataColumn nicht mehr eindeutig
using Microsoft.AnalysisServices.Tabular;

//Diese Variablen entsprechend setzen
string serverName = @"<<meinServer>>";
string spaltenName = "Spalte";
string databaseName = "AMO2016TEST";
string tableName = "Tabelle";

string serverConnectionString = string.Format("Provider=MSOLAP;Data Source={0}", serverName);

//mit dem Server verbinden
Server server = new Server();
server.Connect(serverConnectionString);

//die Tabelle finden
Database db = server.Databases[databaseName];
Model model = db.Model;
Table table = model.Tables[tableName];

//physische Spalte hinzufügen
if (!(table.Columns.Contains(spaltenName)))
table.Columns.Add(
new DataColumn()
{
Name = spaltenName,
DataType = DataType.Int64,
SourceColumn = spaltenName,
Description = "test",
DisplayFolder = "neu",
IsHidden = false,
IsNullable = true,
FormatString = "0",
SortByColumn = table.Columns["SourceValue"],
//die neue Spalte wird nach der Spalte SourceValue sortiert
IsUnique = false,
}
);

//berechnete Spalte hinzufügen
if (!(table.Columns.Contains(spaltenName + "mal2")))
table.Columns.Add(
new CalculatedColumn()
{
Name = spaltenName + "mal2",
DataType = DataType.Int64,
Expression = "2*[" + spaltenName + "]",
//Expression enthält die DAX-Formel
Description = "Das doppelte der Spalte " + spaltenName,
DisplayFolder = "neu",
IsHidden = false,
IsNullable = true,
FormatString = "0",
SortByColumn = table.Columns["SourceValue"],
}
);

//Measure hinzufügen
if (!table.Measures.Contains("Sum_" + spaltenName))
table.Measures.Add(
new Measure()
{
Name = "Sum_" + spaltenName,
Expression = "SUM('” + tableName + '[" + spaltenName + "])",
Description = "Die Summe aller " + spaltenName,
DisplayFolder = "neu",
FormatString = "#,0.00",
IsHidden = false,
}
);

//Änderungen speichern
model.SaveChanges();

DAX: Uhrzeit-Measures

In meinem letzten Projekt hatten wir interessante Kennzahlen:

Es ging um Startzeiten von bestimmten Prozessen:

Gegeben war ein datetime-Feld “Beginn”.

Jetzt waren folgende Kennzahlen gewünscht:

  • Was ist der früheste Beginn?
    Zum Beispiel: In der KW9 um welche Uhrzeit haben folgende Maschinen jeweils begonnen?
    Maschine A: 8:00 Uhr
    Maschine B: 8:30 Uhr
    Maschine C: 7:30 Uhr
  • Wann war der durchschnittliche Beginn in einer Woche?
    Zum Beispiel:
    Mo 8:00 Uhr
    Di 9:00 Uhr
    Mi 8:30 Uhr
    Do 7:00 Uhr
    Fr 10:00 Uhr
    ergibt einen Durchschnitt von 8:30 Uhr

Wir haben es wie folgt implementiert:

Zunächst haben wir zwei berechnete Spalten definiert:

BeginnDatum als date(year([Beginn]); month([Beginn]); DAY([Beginn]))
BeginnUhrzeit als [Beginn]-[BeginnDatum]

Damit erhalten wir die Uhrzeit ohne Datum.

Damit ist die erste Kennzahl ganz einfach:

Erster Beginn:=MIN([BeginnUhrzeit])

Und der Durchschnitt ist auch nicht schwer:

Ø Erster Beginn:=Averagex(Values(‚Fakten_Operationen'[BeginnDatum]); [Erster Beginn])

Dabei ist der erste Parameter der Averagex-Funktion die Menge der Werte, nach denen die Kennzahl berechnet werden muss und worüber dann der Durchschnitt gebildet wird.

Deswegen haben wir hier die Datumswerte mit Values(‚Fakten_Operationen'[BeginnDatum]) verwendet.

Dies lässt sich natürlich einfach verallgemeinern.

XtractIS: Variablen verwenden

Die Theobald-Komponente XtractIS habe ich schon in vielen Projekten eingesetzt. Sie funktioniert wunderbar zum Zugriff auf SAP—Daten.

Bei XtractIS Table hat man einen WHERE-Bereich, in dem man eine Bedingung formulieren kann.

Hier zeige ich, wie man dafür Variablen verwendet, also zum Beispiel die Abfrage auf einen bestimmten (aber dynamisch festzulegenden) Buchungskreis oder ein anderes Kriterium einschränken kann.

Anders als sonst im SSIS funktioniert das nicht über Expressions, sondern sogar noch einfacher.

Man kann die SSIS-Variable einfach in die WHERE-Bedingung schreiben, wobei man die Syntax [@Namespace::Variablenname] verwendet.

Beispiel:

VariableInXtractISTable

Quarantäne-Teil 3: Anlegen der zusätzlichen Komponenten

Nachdem wir im letzten Blog-Kapitel alle Komponenten eines Data Flows durchlaufen konnten und dazu erkennen konnten, ob eine Fehlerbehandlung möglich und noch nicht vorhanden ist, müssen wir nun die Fehlerbehandlung in diesen Fällen einbauen.

Die Fehlerbehandlung soll wie folgt funktionieren:

  • Fehlerbehandlung auf Redirect Row einstellen
  • Den Error Output in eine neu zu erstellende Derived Column leiten.
  • In der Derived Column sollen einige zusätzliche Attribute wie Fehlermeldung, aber auch Primärschlüssel hinzugefügt werden
  • Das Ergebnis läuft dann in einen UNION ALL, der bereits im Paket existiert.

Damit ist die Quarantäne noch nicht ganz fertig. Das UNION ALL führt dann zu einem SQL-Server-Ziel. Dort werden die Spalten aus der Union All in die Datenbank geschrieben. Diesen Teil habe ich, da er nur einmal (je Data Flow) zu erstellen ist, aber manuell gelöst. Hier ist der Aufwand geringer als eine automatisierte Lösung zu implementieren.

Fehlerbehandlung auf Redirect Row umstellen

Interessanter Weise gibt es unterschiedliche Arten von Fehlerbehandlungen in SSIS. Bei manchen Komponenten wird die Fehlerbehandlung einzeln für jede Spalte (z.B. derived column) definiert, in anderen nur global für die gesamte Komponente, in manchen auch für beides.

Der Lookup hat beide Fehlerbehandlungen:

image

Hier ein Beispiel für die Fehlerbehandlung einer Derived Column:

image

Im Advanced Editor sieht das dann so aus:

image

Und die globale Komponenten-Fehlerbehandlung wird nicht verwendet:

image

Wichtig ist zu bemerken, dass diese Einstellungen natürlich nicht in dem Error Output, sondern in dem normalen (bzw. allen normalen) Output(s) und Input(s) gemacht werden müssen.

Deswegen durchläuft der Code alle Inputs und alle Spalten aller Inputs und das gleiche für die Outputs:

For Each outp As IDTSOutput100 In comp.OutputCollection
‚ globale Fehlerhandlung
If outp.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent Then
outp.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
If outp.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent Then
outp.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
‚Fehlerbehandlung je Spalte
For Each col As IDTSOutputColumn100 In outp.OutputColumnCollection
If col.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent Then
col.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
If col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent Then
col.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
Next col
Next outp

Und das gleiche für die Inputs:

For Each inp As IDTSInput100 In comp.InputCollection . . .

Am besten merkt man sich noch in einer boolschen Variablen, ob ein Redirect Row eingestellt wurde. Nur dann darf man nämlich den nächsten Schritt machen.

Dies ist wichtig, da die Fehlermeldungen bei der SSIS-API-Entwicklung meist kryptisch sind, da .NET-Wrapper von der API verwendet werden.  

Eine Derived Column-Komponente erstellen

Wir erstellen sie so:

’neue Derived Column
Dim compDerivedCol As IDTSComponentMetaData100 = pipe.ComponentMetaDataCollection.New()
compDerivedCol.ComponentClassID = „DTSTransform.DerivedColumn“
Dim DesignDerivedTransformColumns As CManagedComponentWrapper = compDerivedCol.Instantiate()
DesignDerivedTransformColumns.ProvideComponentProperties()
compDerivedCol.Name = „Fehler_“ & comp.Name
compDerivedCol.InputCollection(0).ExternalMetadataColumnCollection.IsUsed = False
compDerivedCol.InputCollection(0).HasSideEffects = False

Man beachte das Initiate(). Dadurch wird die derived Column wie im Visual Studio auch instantiiert. Das heißt, man kann auf alle Voreinstellungen zugreifen.

Den Fehler-Output und die Derived Column verbinden

Hierzu legt man einen neuen Pfad an, der den Error Output (den wir im letzten Blog gefunden und gemerkt hatten) mit der Derived Column verbindet:

Dim path As IDTSPath100 = pipe.PathCollection.New()
path.AttachPathAndPropagateNotifications(comp.OutputCollection(nrOfErrorOutput), compDerivedCol.InputCollection(0))

Zusätzliche Spalten in der Derived Column anlegen

Da man das immer wieder braucht, habe ich dazu eine Methode erstellt.

Diese erhält als Parameter:

  • die Komponente (compDerivedCol)
  • den Namen der Spalte
  • den Datentyp, also zum Beispiel Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4
    Hier kann man wunderbar den vorhandenen enum verwenden.
  • die Länge des Datentyps – nur bei Strings (o.ä.) nötig. Bei Integer kann man 0 angeben. Das System macht es automatisch richtig.
  • Die Formel, also z.B. „(DT_WSTR,50)@[System::PackageName]“ für den Paketnamen – oder zum Auswerten eigener Variablen. Die Formel darf keine Anführungszeichen (“) enthalten.
  • Optional die LineageID – dazu komme ich im Anschluss

Hier der Code:

Private Sub addNewColumn2DerivedComponent(derivedColumnComponent As IDTSComponentMetaData100, name As String, dataType As Wrapper.DataType, dataTypeLaenge As Integer, expression As String, Optional lineageIDKeyColumn As Integer = 0)
Dim neueSpalte As IDTSOutputColumn100 = derivedColumnComponent.OutputCollection(0).OutputColumnCollection.New()
neueSpalte.Name = name
neueSpalte.SetDataTypeProperties(dataType, dataTypeLaenge, 0, 0, 0)
neueSpalte.ExternalMetadataColumnID = 0
neueSpalte.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure
neueSpalte.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure

Dim neueSpalteProp As IDTSCustomProperty100 = neueSpalte.CustomPropertyCollection.New()
neueSpalteProp.Name = „Expression“
If lineageIDKeyColumn = 0 Then
neueSpalteProp.Value = expression
Else
neueSpalteProp.Value = „(DT_WSTR,“ & dataTypeLaenge & „)#“ + lineageIDKeyColumn.ToString()
End If
neueSpalteProp = neueSpalte.CustomPropertyCollection.New()
neueSpalteProp.Name = „FriendlyExpression“
neueSpalteProp.Value = expression

End Sub

Hier einige Erklärungen dazu:

  • Am Anfang wird die Spalte angelegt mit dem entsprechenden Namen und Datentyp
  • Die derived column hat als Fehlertyp “Ignore Failure”, weil wir ja sonst die Idee der Quarantäne ad absurdum führen würden.
  • Dann werden zwei Properties angelegt, die notwendig sind – die Expression und die “friendly expression”. Mit der “Expression” rechnet SSIS, die “friendly expression” wird angezeigt.

Und jetzt die Geschichte mit der lineage-ID, wie versprochen:

Man kann auch in den Formeln (expression) auf bestehende Spalten zugreifen. Das funktioniert über die lineage-Id der Spalte, also beispielsweise so “(DT_WSTR,50)#17”. Die “friendly expression” muss nicht unbedingt angegeben werden.

Die lineage-ID kann man sich ermitteln, indem man alle Spalten des Inputs durchläuft und den Namen der Spalte kennt. Allerdings kommt dabei der Wrapper für managed code zum Einsatz, den ich vorher bereits angedeutet habe. Wir benötigen nämlich hier Designer-Funktionalität, wie sie im Visual Studio auch vorhanden ist. Deswegen sieht der Code etwas hässlich aus:

Private Function findeSpalte(comp As IDTSComponentMetaData100, nameSpalte As String, ByRef nameSpalteGefunden As String) As Integer
Dim inp As IDTSInput100 = comp.InputCollection(0)
Dim virtualInp As IDTSVirtualInput100 = inp.GetVirtualInput()
Dim virtualInpCols As IDTSVirtualInputColumnCollection100 = virtualInp.VirtualInputColumnCollection
Dim designer As CManagedComponentWrapper = comp.Instantiate()

nameSpalteGefunden = „“

For Each virtualCol As IDTSVirtualInputColumn100 In virtualInpCols
If virtualCol.Name = nameSpalte Then
designer.SetUsageType(inp.ID, virtualInp, virtualCol.LineageID, DTSUsageType.UT_READONLY)
nameSpalteGefunden = nameSpalte
Return virtualCol.LineageID
End If
Next

Return 0
End Function

Diese Funktion stellt auch den UsageType der Spalte auf Readonly. Dies erscheint zunächst unnötig, ohne dies funktioniert es  aber nicht. Dies spiegelt wider, was der Advanced Editor für die Spalten anzeigt, die in den Formeln verwendet werden:

image

In diesem Beispiel wurde die EinrichtungID-Spalte in einer Formel verwendet.

So habe ich die Primärschlüssel, die ich in der Quarantäne-Tabelle mit protokollieren wollte, gefunden.

Die Derived Column mit der UNION ALL verbinden

Das funktioniert genauso wie der Pfad zur derived column, muss also nicht im Detail beschrieben werden.

Damit sind wir nun fast fertig. Es sind lediglich ein paar Besonderheiten zu beachten – s. nächster Blog-Eintrag.

Quarantäne-Teil 2: Schleife über alle Komponenten eines Data Flows und Untersuchung bestehender Fehlerbehandlung

In dem letzten Blog-Eintrag hatten wir eine Schleife um alle Data Flows erstellt und eine Data Flow identifizieren können.

Heute wollen wir eine Schleife über alle Komponenten bauen und die Komponenten etwas genauer analysieren, insbesondere auf vorhandene Fehlerbehandlung.

Im letzten Kapitel hatte wir eine Methode processAllExecutables geschrieben. Diese enthielt in einer Schleife ein Executable mit Variablennamen e.

Über

If TypeOf e Is TaskHost Then

hatten wir ermittelt, dass es sich um einen Data Flow handelt.

Schleife über alle Komponenten

Nun erstellen wir uns zunächst ein paar Variablen:

Dim th As TaskHost
th = CType(e, TaskHost)
Dim name As String
name = CType(e, TaskHost).Name

If TypeOf th.InnerObject Is
Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe Then
Dim pipe As MainPipe = CType(th.InnerObject, MainPipe)

End If

Diese bedeuten:

  • den Namen des Data Flows in name
  • In th steht der TaskHost, also Data Flow
  • Der th hat ein inneres Objekt, die sogenannte Main Pipe. Diese beinhaltet weiterhin alle Data Flow-Komponente. An ihr sind wir also im besonderen interessiert. Wir haben sie in der Variablen pipe gespeichert.

Nun ist es ganz einfach alle Komponenten eines Data Flows zu durchlaufen, da sie in der Collection ComponentMetaDataCollection des MainPipe-Objekts enthalten sind:

For Each comp As IDTSComponentMetaData100 In pipe.ComponentMetaDataCollection

Next comp

Identifikation des bestehenden Error Handlings

Als erste wollen wir wissen, ob diese Komponente überhaupt grundsätzlich über ein Error Handling verfügen kann. Überraschender Weise gibt es nämlich Komponenten, an die man kein Error Handling anschließen kann, z.B. MultiCast, Union all, aber auch eine Skript-Komponente, die ja definitiv einen Fehler erzeugen kann.

Wie erkennen wir nun, ob eine Komponente ein Error Handling zulässt?

Jede Komponente hat eine Input- und eine OutputCollection. Diese sieht man auch in SSIS im erweiterten Editor, so z.B. bei einer Derived Column:

image

Auf der linken Seite sehen wir 3 Ports dieser Komponente. Davon finden sich im Objekt-Modell der erste in der InputCollection und die anderen beiden in der OutputCollection. Der Fehler-Output unterscheidet sich nun von anderen Outputs dadurch, dass die Property IsErrorOut auf true gesetzt ist.

Wenn wir nun die OutputCollection durchlaufen, können wir den Error Output ermitteln bzw. erkennen, dass diese Komponente gar keinen Error Output ermöglicht.

Wichtig! Wie in der SSIS-Oberfläche auch, sind diese Outputs sichtbar unabhängig davon, ob sie nachher mit Pfaden zu einer anderen Komponente verknüpft sind. Wenn also ein Error Output nicht im Objektmodell vorhanden ist, heißt das, dass keine Fehlerbehandlung für dieses Komponente möglich ist, nicht etwa, dass nur noch keine definiert worden ist.

Was uns jetzt noch interessiert ist, ob für diese Komponente, wenn sie einen Error Output hat, dieser bereits verwendet wird. Das ist dann der Fall, wenn es einen Pfad gibt, der den Error Output als Beginn des Pfades hat. Programmatisch bedeutet das, dass wir die PathCollection durchlaufen müssen und alle Anfangspunkte des Pfades mit dem IdentificationString des Error Outputs vergleichen müssen.

Ich habe das so implementiert:

‚ermittle Error Output
Dim hatErrorOutput As Boolean = False
Dim nrOfErrorOutput As Byte = 0
Dim ErrorOutputSchonVerwendet As Boolean = False

For outpNr As Integer = 0 To comp.OutputCollection.Count – 1
‚Ist das ein Error Output?
If comp.OutputCollection(outpNr).IsErrorOut Then
‚ja!
hatErrorOutput = True
nrOfErrorOutput = outpNr
‚wird dieser Error Output schon in einem Pfad benutzt?
’schaue nach in pipe.PathCollection
Dim id As String
id = comp.OutputCollection.Item(outpNr).IdentificationString
For p As Integer = 0 To pipe.PathCollection.Count – 1
If pipe.PathCollection(p).StartPoint.IdentificationString = id Then
ErrorOutputSchonVerwendet = True
End If
Next p
End If
Next outpNr

Somit wissen wir

  • ob die Komponente ein Error Handling erlaubt (hatErrorOutput)
  • ob für diese Komponente ein Error Handling bereits definiert ist (ErrorOutputSchonVerwendet). Diesen Fall soll unser Quarantäne-Algorithmus nämlich ignorieren (Da hat sich der Entwickler des Pakets ja was dabei gedacht)

SSRS: Top 10 und Rest zum Auf- und Zuklappen

Die Aufgabe in diesem Blog-Eintrag ist es, bei einem gegebenen Dataset nur die ersten 10 Einträge anzuzeigen und den Rest über +/- auf- und zuklappen zu können.

Dazu gibt es einen guten Artikel: http://www.bidn.com/blogs/mikedavis/ssis/172/top-n-bottom-n-grouping-in-ssrs-2008

Allerdings verwendet dieser die Filter-Funktion “Top n” der Gruppe. (Und für den Rest Bottom (Anzahl-n)) Diese hat folgende Nachteile:

  • Wenn der 10. und der 11. Eintrag gleich sind, so zeigen die Top 10 in Wirklichkeit 11 Einträge. Das wäre noch nicht so schlimm, aber:
  • Damit sind natürlich auch der 10.-letzte und der 11.-letzte Eintrag gleich, somit kommen beide in dem Rest noch einmal, so dass beide Einträge doppelt erscheinen.

Das sieht dann so aus:

image

Der Autor hat zwar noch einen weiteren Artikel veröffentlich, in dem er das Problem versucht zu beheben – aber dabei verwendet er SQL-Funktionen. Ich möchte das Problem nur mit SSRS-Features lösen, um eine allgemein gültige Lösung zu haben, zumal in meinem Kundenprojekt MDX zum Einsatz kam.

Mit folgender Anleitung kann man dieses Problem beheben:

Stellen wir uns vor, wir haben ein Dataset wie:

image

Dieses Dataset zeigen wir in einer Tabelle an – ohne Gruppierung. Das sieht so aus – mit der “Details”-Gruppe:

image

Für die Details-Gruppe setzen wir folgende Eigenschaften:

  • Sortierung: Wert absteigend, danach Person aufsteigend. Letzteres ist notwendig, um die Reihenfolge deterministisch zu machen (falls der Wert gleich)
  • Sichtbarkeit: über Expression: =RowNumber(Nothing)>5
    Damit werden nur die ersten 5 Zeilen angezeigt.

Diese Details sollen die Top 5-Werte anzeigen.

Für die Gruppe, die die restlichen Werte anzeigen soll, legen wir angrenzend daran eine neue Detailgruppe an:

image

Diese Gruppe stellen wir so ein:

  • Details: image
  • Name: Details_Rest
  • Sortierung: Wert absteigend, danach Person aufsteigend.
  • Sichtbarkeit: über Expression: =RowNumber(Nothing)<=5
    Damit werden die ersten 5 Zeilen nicht angezeigt.
  • Die Spalten werden wieder mit Person und Wert besetzt.

Nun legen wir jeweils eine übergeordnete Gruppe an:

image

Die erste Gruppe stellen wir so ein:

  • Name: Person_Top
  • Gruppieren nach Person, das entspricht also unserer Detailzeile (Allgemein verwenden wir hier den Primärschlüssel)
  • Sortierung: wie die Detailgruppe
  • Sichtbarkeit: Anzeigen
  • Die neu angelegte Spalte Person löschen wir (Wichtig! Nur die Spalte löschen nicht die Gruppe löschen)
    image

Das gleiche machen wir für die zweite Detailzeile und richten auch hier eine übergeordnete Gruppe ein:

  • Name: Person_Rest
  • Gruppieren nach Person, das entspricht also unserer Detailzeile (Allgemein verwenden wir hier den Primärschlüssel)
  • Sortierung: wie die Detailgruppe
  • Sichtbarkeit: Anzeigen

Somit sieht unser Bericht so aus:

image

Ganz links fügen wir eine neue Spalte (außerhalb der Gruppe) hinzu:

image

Dann löschen wir wieder die unnötige Person-Spalte (ohne die Gruppe zu löschen):

image

Die Zellen ganz links setzen wir:

  • Name = txt_Top
  • Inhalt = Top 5

bzw.

  • Name = txt_Rest
  • Inhalt = Rest

Unser Bericht sieht dann so aus:

image

Jetzt wollen wir noch das Auf- und Zuklappen implementieren:

  • Gruppe Person_Top
    • Sichtbarkeit umschalten nach Textbox “txt_Top”
  • Gruppe Person_Rest
    • Sichtbarkeit: Ausblenden
    • Sichtbarkeit umschalten nach Textbox “txt_Rest”
      image

Jetzt muss noch etwas Kosmetik gemacht werden:

  • Die txt_Top-Textbox sollte ein – zu Beginn haben und kein +. Also müssen wir bei dieser Textbox den InitialToggleState auf true setzen
  • Wenn die Gruppe zusammengeklappt ist, sieht man die erste Person. Deswegen schreiben wir in die jeweiligen Spalten
    =iif(count(Fields!Person.Value)>1, Nothing, Fields!Person.Value) bzw.
    =iif(count(Fields!Person.Value)>1, Nothing, Fields!Wert.Value)

Geschafft!

image

und

image

Der Beispielbericht ist hier als Download verfügbar.

Zum Abschluss noch ein paar Worte zur Motivation, warum wir jeweils zwei Gruppen verwenden:

  • Die äußere Gruppe wird verwendet, um den “Toggle” zu ermöglichen (also das Ein- und Ausblenden), die inenre Gruppe, um zu definieren, welche Zeilen in der Gruppe sichtbar sind. Da beides über “Sichtbarkeit” geregelt wird, benötigen wir 2 Gruppen.
  • Die Alternative wäre, mit Filtern in der Gruppe zu arbeiten. Dann bräuchte man nur eine Gruppe. Leider darf aber RowNumber(…) nicht in Filtern verwendet werden, so dass dieser Weg ausscheidet.

SSAS Tabular DAX: DATEDIFF-Ersatz

In (tabular) Cubes möchte man möglicherweise Berechnungen durchführen, bei denen die Anzahl der Tage zwischen zwei Datumswerten ermittelt werden sollen.

Überraschender Weise gibt es keine DateDiff-Funktion in DAX.

Zwar kursieren im Web einige Beispiele für einen Ersatz, allerdings gefallen sie mir nicht.

Hintergrundwissen

Deswegen schauen wir uns zunächst näher an, wie in (tabular) Cubes Datumswerte gespeichert werden:

Datumswerte werden intern als Zahlen gespeichert, wobei der ganzzahlige Anteil die Anzahl der Tage seit 30.12.1899 angibt und der Nachkommaanteil die Uhrzeit.

Wir können das ganz leicht im Cube sehen, indem wir ein Datum mit 1.0 (deutsch 1,0) multiplizieren (im DAX reicht *1. bzw. *1, – je nachdem welche Lokalisierung eingestellt ist ) – oder über value(…) in eine Zahl konvertieren.

Ich habe einen kleinen Cube erstellt – mit folgender Datenquelle:

select convert(datetime, ‚17.8.2011 17:53:12‘, 104) as DatumUhrzeit, convert(date, ‚17.8.2011‘, 104) as Datum
UNION ALL
select convert(datetime, ‚1.3.2000 06:13:27‘, 104), convert(date, ‚1.3.2000‘, 104)
UNION ALL
select convert(datetime, ‚5.3.2000 01:13:27‘, 104), convert(date, ‚5.3.2000‘, 104)
UNION ALL
select convert(datetime, ‚1.1.1900 5:30:00‘, 104), convert(date, ‚1.1.1900‘, 104)
UNION ALL
select convert(datetime, ‚31.12.1899 17:30:00‘, 104), convert(date, ‚31.12.1899‘, 104)
UNION ALL
select convert(datetime, ‚27.12.2014 20:22:55‘, 104), convert(date, ‚27.12.2014‘, 104)
UNION ALL
select getdate(), convert(date, getdate())

Dann habe ich jeweils eine berechnete Spalte – wie oben beschrieben – hinzugefügt. Das Ergebnis:

image

DateDiff von Dates

Um nun die Differenz zwischen 2 Date-Spalten zu ermitteln, muss man sie nur voneinander abziehen. Das Ergebnis hat allerdings den Datentyp DateTime, weswegen man das Ergebnis noch in ein int verwandeln muss:

([Datum2] –[Datum1]) * 1

oder

int([Datum2]-[Datum1])

Um das zu demonstrieren, habe ich meine Quelle angepasst und das Ergebnis sieht so aus:

image

DateDiff von DateTimes

Wenn wir aber 2 DateTimes haben, dürfen wir sie nicht einfach voneinander abziehen (also int([DatumUhrzeit2]-[DatumUhrzeit1])), da die Uhrzeiten keine rolle spielen sollen. Stattdessen müssen wir die Datumswerte zunächst nach int konvertieren (dabei rundet DAX immer ab) und dann subtrahieren:

int([DatumUhrzeit2])-int([DatumUhrzeit1])

image

Wir sehen, dass die falsche Methode immer dann einen zu niedrigen Wert ausweist, wenn das abgezogene Datum eine größere Uhrzeit als der Minuend aufweist.

Erweiterungen: Alter

Falls mein ein Alter bis heute berechnen will, kann man das mit der gleichen Logik machen. Die DAX-Funktionen sind:

  • now() liefert das heutige Datum inkl. aktueller Uhrzeit
  • today() liefert nur das heutige Datum

Diese Funktionen werden im übrigen beim Process aufgerufen und nicht beim Auswerten in einem Frontend-Programm

Erweiterungen: DateDiff in Sekunden

Ganz analog kann man natürlich vorgehen, um die Differenz in anderen Einheiten auszurechen:

Man multipliziert einfach die DateTime-Spalte mit dem entsprechenden Wert:

  • * 24 für Stunden
  • * 24 * 60 für Minuten
  • * 24 * 60 * 60 für Sekunden

und konvertiert dann die Zahl in ein int.

Allerdings kann so eine Differenz in Monaten bzw. Jahren nicht berechnet werden. Das müsste man zunächst wohl definieren (Was bedeutet 17.1.2015 18:33 – 18.3.2012 17:55 in Monaten?) und dann ggf. über if-Statements selbst berechnen.

Meine Erfahrungen in der Business Intelligence Welt