Archiv der Kategorie: MS Integration Services

Microsoft Integration Services 2005, 2008, 2008 R2, 2012, 2014

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)

Quarantäne–Teil 1: Schleife über alle Data Flows

In einem vorangegangenen Eintrag hatte ich die Idee der Quarantäne vorgestellt, für die wir ein VB-Programm geschrieben haben, das in einem SSIS-Paket das Fehlerhandling umstellt.

DLLs einbinden

Als erstes müssen wir die folgenden DLLs einbinden:

image

also:

  • Microsoft.SqlServer.DTSPipelineWrap
  • Microsoft.SqlServer.DTSRuntimeWrap
  • Microsoft.SqlServer.ManagedDTS

Diese müssen dann mit Imports (VB) bzw. using (c#) eingebunden werden – hier immer die VB-Beispiele:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Runtime.InteropServices

Paket laden

Als erstes wollen wir das SSIS-Paket laden und wieder speichern:

Dim ssisApp As New Microsoft.SqlServer.Dts.Runtime.Application
Dim pName As String = “C:\meineProjekte\Paket.dtsx”
Dim package As New Package
package = ssisApp.LoadPackage(pName, Nothing)
ssisApp.SaveToXml(“neuerName.dtsx”, package, Nothing)

Alle Tasks ermitteln

Nun wollen wir in dem SSIS-Paket alle Tasks durchlaufen, um sie verändern zu können. Dazu enthält das SSIS-Paket eine Property “executables”, diese enthält alle Tasks der ersten Ebene.

Mit einer Schleife können wir diese durchlaufen:

For Each e As Executable In package.executables
Next

Allerdings erreichen wir dabei nur die Tasks der ersten Ebene.

In SSIS gibt es aber Tasks, die selber wieder Tasks beinhalten:

  • Sequence
  • For Each Loop
  • For Loop

Diese interne Struktur kann man einfach in der SSIS-Umgebung anhand des Package Explorers sehen. In meinem Beispiel habe ich eine Sequence mit 2 Data Flows  und nach der Sequence noch einen Data Flow (namens “skript”):

image

Um alle Tasks durchlaufen zu können, müssen wir also rekursiv vorgehen:

im Hauptprogramm:


processAllExecutables(package.Executables, 1)

und dann als eigene Methode:

Private Sub processAllExecutables(
ByVal executables As Executables,
ByVal depth As Integer)

For Each e As Executable In executables

Dim d1 As Integer = depth + 1
If TypeOf e Is ForLoop Then
processAllExecutables(CType(e, ForLoop).Executables, d1)
End If
If TypeOf e Is Sequence Then
processAllExecutables(CType(e, Sequence).Executables, d1)
End If
If TypeOf e Is ForEachLoop Then
processAllExecutables(CType(e, ForEachLoop).Executables, d1)
End If
Next
End Sub

Wie man sieht, kann man über TypeOf Is <<Typ>> abfragen, ob die Task e einen bestimmten Typ hat. Bei den oben genannten Typen rufen wir dann die selbe Methode rekursiv auf – ich habe der Übersichtlichkeit halber die Tiefe als zusätzlichen Parameter übergeben, auch wenn das nicht unbedingt notwendig ist.

Somit haben wir das Ziel des heutigen Blogs fast erreicht: Wir durchlaufen alle Tasks. Als nächstes müssen wir Data Flows erkennen und verarbeiten.

DataFlows werden in der SSIS-API als TaskHost bezeichnet. Deswegen können wir das so abprüfen:

If TypeOf e Is TaskHost Then

End If

Damit haben wir eine Schleife über alle Data Flows programmiert.

NULL-Werte bei Lookups

Lookups kommen bei SSIS-Data Flows ziemlich häufig vor. Zum Beispiel müssen bei der Verarbeitung von Fakten-Sätzen enthaltene Dimensions-Referenzen umgesetzt werden. Zum Beispiel könnte es sein, dass Deutschland im Quellsystem ‘D’ ist, im DWH aber mit 49 abgebildet wird.

Das ist mit Lookups natürlich ganz einfach.

Nun kommt es aber vor, dass nicht alle Fakten eine Referenz auf ein Land haben müssen, also NULL sind. Diese sollen dann auch NULL bleiben.

Wie kann eine solche Aufgabe gelöst werden?

Die Standard-Lösung wird so aussehen:

Vor dem Lookup wird über einen Conditional Split überprüft, ob das Land leer ist:

  • Wenn ja, dann wird der Zielwert auch auf NULL gesetzt
    (Dafür muss nichts gemacht werden, da dies beim folgenden UNION ALL automatisch erfolgt)
  • Wenn nein, dann wird der Lookup durchgeführt

Danach werden beide Pfade über einen UNION wieder zusammengeführt:

image

In meinem Beispiel sieht die Lookup-Quelle so aus:

SELECT N’D‘ as Country, 49 as nummer
UNION ALL
SELECT N’A‘ as Country, 43 as nummer
UNION ALL
SELECT N’CH‘ as Country, 41 as nummer

Diese Vorgehensweise hat mehrere Nachteile:

  • Über das UNION ALL wird immer ein neuer Buffer angelegt, was insbesondere bei großen Data Flows unnötig Speicher belegt (UNION ALL ist semi-blocking)
  • Die Lösung ist einigermaßen komplex
  • Falls eine Sortierung vorliegt, geht die Sortierung durch das UNION ALL verloren. Dies sollte nicht durch den Einsatz eines MERGE verhindert werden, da dies die Performance sehr negativ beeinflussen kann (vielleicht schreibe ich dazu mal einen eigenen Blog-Eintrag)

Deswegen hatten wir nach einer besseren Lösung gesucht:

Der erste Ansatz war folgender: Wir können beim Lookup Nicht-Treffer ignorieren und danach überprüfen, ob wir ein Mapping übersehen haben und dann selbst einen Fehler schmeißen:

imageDer Conditional Split enthält folgende Bedingung:

!(ISNULL(Country)) && ISNULL(nummer)

In der Skriptkompopnente wird ein Fehler mit folgendem Code erzeugt:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    bool p=false;
    ComponentMetaData.FireError(10, „Testkomponente“, „Country was not null, but could not be mapped to a country number“, „“, 0, out p);
}

Dieser Ansatz hat folgenden Vorteil:

  • Alle Komponenten sind synchron, benötigen also keine zusätzlichen Buffer.

– aber auch folgende Nachteile:

  • Die Lösung ist noch komplexer
  • Die Lösung ist unübersichtlich und schlechter wartbar, da ohne die Skriptkomponente der Lookup falsch programmiert wäre. Das muss aber der wartende Mitarbeiter wissen. Außerdem ist ein Error Handling “Fehler ignorieren” irreführend.
  • Skript-Komponenten haben selbst keinen Fehler-Output. Damit kann eine Skript-Komponente leider nicht mit der vorhin vorgestellten Quarantäne-Idee automatisch korrigiert werden.

Die beste Lösung ist somit eine ganz einfache:

Wir  stellen den Lookup wieder auf Fehler bei einem Nicht-Treffer. Wir ergänzen die Quelle des Lookups um NULL-Werte, also in meinem Beispiel

SELECT N’D‘ as Country, 49 as nummer
UNION ALL
SELECT N’A‘ as Country, 43 as nummer
UNION ALL
SELECT N’CH‘ as Country, 41 as nummer
UNION ALL
SELECT NULL, NULL

bzw. allgemein

SELECT * FROM <lookup-tabelle>
UNION ALL
SELECT NULL, NULL

Dann sieht der Data Flow ganz einfach aus:

image

Dies hat natürlich etliche Vorteile:

  • Die Lösung ist super-einfach.
  • Die Lösung ist natürlich synchron und somit sehr schnell.

Allerdings funktioniert diese Lösung nur, wenn Full Cache eingestellt ist:

image

Dies ist aber eh die meistens verwendete (da performanteste) Variante.

Die Ursache, warum die anderen Cache-Einstellungen nicht funktionieren, liegt darin, dass im SQL NULLs anders verwendet werden (sie können nicht mit = abgeprüft werden). In den anderen Cache-Einstellungen würden SQLs wie SELECT * FROM <lookuptabelle> where Country = NULL ausgeführt.

Error Handling im Data Flow eines SSIS-Pakets

In den meisten Komponenten innerhalb eines Data Flows in einem SSIS-Paket können als Error Handling folgende Einstellungen getroffen werden:

Englisch Deutsch Bemerkung
Fail Component Fehler bei Komponente Dies ist die Standard-Einstellung. Sie führt dazu, dass bei einem – wie auch immer gearteten Fehler – der gesamte Data Flow fehlschlägt. Noch nicht verarbeitete Daten werden nicht weiter verarbeitet.
Ignore Failure Fehler ignorieren Diese Einstellung würde bedeuten, dass trotz des Fehlers normal weitergearbeitet wird. Dies ist in der Regel nicht sinnvoll.
Redirect Row Zeile umleiten Die fehlerhafte Zeile wird umgeleitet und der aufgetretene Fehler kann durch einen speziellen Ablauf behandelt werden.

In der Regel ist es sinnvoll, während der Entwicklung “Fail Component” einzusetzen. Während der Entwicklung und der Tests möchte man ja schließlich wissen, ob und wenn ja, welche Fehler auftreten, damit man sie programmatisch beheben kann.

Auch für den produktiven Einsatz kann diese Einstellung sinnvoll sein, da sie dafür sorgt, dass keine fehlerhaften Daten ins DWH übernommen werden.

Auf der anderen Seite ist die Einstellung problematisch, da dann ein fehlerhafter Satz die ETL-Strecke zum Erliegen bringt. In einem meiner Projekte laden wir alle 10 Minuten aus einem Quellsystem, das sehr großzügig mit Eingaben vorgeht.  Für dieses Projekt haben wir eine Quarantäne definiert:

Alle Komponenten sollen auf “Redirect Row” gestellt werden und diese fehlerhaften Sätze werden in eine Quarantäne-Tabelle geschrieben (unter Angabe des Primärschlüssels und des aufgetretenen Fehlers).

Danach müssen noch “halbe” Datensätze entfernt werden – es kann ja sein, dass in einem Vater-Satz ein Fehler auftritt und dieser in die Quarantäne läuft, aber alle Kinder-Sätze (der 1:n-Beziehung) richtig durchlaufen würden. Diese sollen dann aber natürlich nicht im DWH landen.

Die Umstellung der Komponenten auf “Redirect Row” wollten wir dabei nicht manuell durchführen, da wir Data Flows mit ca. 150 Komponenten haben. Deshalb haben wir ein (VB).NET-Programm erstellt, das via SSIS API diese Komponenten umstellt. Dieses Programm werde ich in den nächsten Blog-Einträgen vorstellen.

SSIS: Tabellen-Variablen als Datenquelle

In SSIS kann man als Datenquelle ein SQL-Statement eintragen:

Eingabefeld für SQL als Datenquelle

Manchmal möchte man in dieser Maske kompliziertere Berechnungen verwenden. Dafür kann man T-SQL (wie bei der Programmierung von Stored Procedures) verwenden. Allerdings funktioniert es nicht ganz wie gedacht:

Als erstes kann man keine temporären Tabellen (beginnend mit #) verwenden. Als Beispiel betrachten wir das SQL-Statement:

select 1 as zahl ,2 as doppelte into #t
select * from #t

Dieses einfache Statement liefert im SQL Server Management Studio – wie erwartet:

(1 Zeile(n) betroffen)
zahl        doppelte
———– ———–
1           2

(1 Zeile(n) betroffen)

Fügt man dieses Statement aber in der SSIS-Datenquelle hinzu, erhält man nach OK folgenden Fehler:

Fehler beim Vorbereiten: #t unbekannt

bzw. als Text:

Fehler bei Datenflusstask [T-SQL Programm [1]]: SSIS-Fehlercode ‚DTS_E_OLEDBERROR‘. OLE DB-Fehler. Fehlercode: 0x80040E14.
Ein OLE DB-Datensatz ist verfügbar. Quelle: ‚Microsoft SQL Server Native Client 10.0‘ HRESULT: 0x80040E14 Beschreibung: ‚Anweisung(en) konnte(n) nicht vorbereitet werden.‘.
Ein OLE DB-Datensatz ist verfügbar. Quelle: ‚Microsoft SQL Server Native Client 10.0‘ HRESULT: 0x80040E14 Beschreibung: ‚Ungültiger Objektname ‚#t‘.‘.

Auch eine verspätete Überprüfung des SQLs bringt keine Besserung.

Allerdings funktioniert es, Tabellen-Variablen zu verwenden. Dort heißt obiger Code entsprechend:

Declare @t as TABLE (zahl integer, doppelte integer)
insert into @t select 1 ,2
select * from @t

Dieser Code lässt sich als Datenquelle eintragen und SSIS erkennt korrekt die beiden Spalten:

Spalten korrekt erkannt

Startet man das SSIS-Paket (damit man etwas sieht, habe ich ein UNION ALL dahinter gehängt), scheint es zu funktionieren:

Datenquelle zu UNION ALL

Alles ist grün – wunderbar.

Aber nein! Es wurde keine Zeile übertragen.

Das ist ein bekanntes Problem, dass in dem INSERT-Statement bereits ein Dataset erzeugt wird – dem entspricht in obiger Ausführung der erste Text “(1 Zeile(n) betroffen)”. Die Daten des zweiten Befehls werden dann gar nicht mehr im SSIS weiterverwendet!

Also muss man das SQL so abwandeln:

set nocount on
Declare @t as TABLE (zahl integer, doppelte integer)
insert into @t select 1 ,2
select * from @t

Durch das “set nocount on” wird das erste Dataset unterdrückt, so dass es jetzt funktioniert:

image

Wir sehen, es wurde tatsächlich eine Zeile übertragen – und die Daten stimmen auch, wie der Data Viewer zeigt.

SSIS: Laufzeitprotokollierung je Task

Wenn man größere SSIS-Jobs schreibt, sollten diese Jobs später auf ihre Laufzeiten kontrolliert werden. Läuft die Laufzeit eines SSIS-Jobs aus dem Ruder, ist es sinnvoll, protokolliert zu haben, wie lange welcher Task des SSIS-Jobs läuft, um schnell den Schuldigen ausfindig zu machen. Alternative Techniken wie Analyse des Log-Files sind sehr mühselig.

In dem Code-Beispiel gehen wir davon aus, dass es eine SQL-Tabelle gibt, in der für jeden SSIS-Job ein Eintrag erzeugt wird, so dass es eine eindeutige Lauf-ID gibt. Diese Lauf-ID wird im ersten Schritt des SSIS-Jobs erzeugt. Dies ist bei uns “Best Practice”

Zur Protokollierung legen wir eine Tabelle wie folgt an:

CREATE TABLE [dbo].[META_ImportLaufTaskDauer](
    [ImportLauf_ID] [int] NOT NULL,
    [TaskName] [nvarchar](200) NOT NULL,
    [Startzeitpunkt] [datetime] NOT NULL,
    [Endezeitpunkt] [datetime] NULL,
CONSTRAINT [PK_META_ImportLaufTaskDauer] PRIMARY KEY CLUSTERED
(
    [ImportLauf_ID] ASC,
    [TaskName] ASC
)
)

Die Spaltennamen sollten selbst erklärend sein.

In diese Tabelle wird nun über Events im SSIS-Paket geschrieben. Dazu werden auf der obersten Ebene des Pakets (und nur dort – also nicht etwa für jede Task) die Events für OnPreExecute und OnPostExecute angelegt:

In dem OnPreExecute wird nun der Startzeitpunkt der Task in die Tabelle geschrieben… :

OnPreExecute

Dazu wird ein Execute SQL-Task angelegt. Dieser beinhaltet folgendes SQL:

declare @Laufid int
set @Laufid = ?

if @LaufId > 0
  INSERT INTO META_ImportLaufTaskDauer (ImportLauf_ID, TaskName, Startzeitpunkt)
VALUES
  (@laufid, left(?, 200), getdate())

Das Mapping der Variablen sieht so aus:

Parameter bei OnPreExecute

Die ID des Importlaufs findet sich hier in der Benutzer-Variablen LaufID. Die andere Variable ist eine System-Variable, die uns den verantwortlichen Task nennt.

… und im OnPostExecute der Endzeitpunkt der Task mit folgendem SQL:

declare @Laufid int
set @Laufid = ?

if @LaufId > 0
UPDATE META_ImportLaufTaskDauer
Set Endezeitpunkt = getdate()
where ImportLauf_ID = @Laufid and TaskName= left(?, 200)

Das ParameterMapping ist wie im OnPreExecute.

Performanceprobleme bei SSAS-Dimensionsabfragen aus SSRS

Nachdem wir in einem Projekt unseren Test-Datenbestand (12 Mio Fakten) auf den Produktiv-Bestand (73 Mio Fakten) erweitert hatten, zeigten die Berichte (SSRS 2008 R2) massiv schlechte Antwortszeiten in bestimmten Berichten auf unseren Cube (SSAS 2008 R2) – jeweils nach der Cubeaufbereitung. Somit war klar, dass Ursache war, dass einige der im Bericht verwendeten Abfragen nicht im Cache waren, da dieser ja durch die inkrementelle Dimensionsaufbereitung und Aufbereitung einiger Cubepartitionen gelöscht wird.

Naheliegende Strategien waren:

  • Aufteilung in mehr Partitionen unter Angabe der Slice-Property
  • Verbesserte Aggregationen über Usage based Aggregation Design
  • Cache Warming – Strategien

All das brachte uns aber nicht wirklich weiter.

Letztendlich stellte sich aber heraus, dass die Ursache gar nicht in den (tlw. komplexen) Abfragen zur Ermittlung der Fakten zu suchen war, sondern in „Dimensions-Abfragen“. Damit meine ich folgendes: Unsere Berichte werden (aus einer eigenen Web-Applikation heraus) mit Parametern aufgerufen, die IDs sind (eigentlich die Member Unique Names, also z.B. Datum.Jahr.&[2010]). Dabei haben die Parameter (aus Performancegründen) keine Datasets, die alle verfügbaren Werte enthalten. Nun wollen wir aber in dem Bericht natürlich auch den übergebenen Wert im Klartext anzeigen, also in obigem Beispiel das Jahr 2010. Deswegen mussten also einfache Abfragen her, die aus dem Member Unique Name den Klartext (Member Caption) ermitteln.

In unserem Projekt hatten wir uns darauf geeinigt, wenn möglich den SSRS-Designer für SSAS-Abfragen zu verwenden, um eine gute Wartbarkeit (ohne tiefere MDX-Kenntnisse) zu erreichen.

Damit gibt es zwei einfache Arten, dieses Problem zu lösen:

Die erste Art, hat eine Spalte – das Jahr – und zusätzlich den (MultiSelect) Report Parameter als Parameter für die Abfrage:

Wizard für die langsame Abfrage
(Bild zum Vergößern anklicken!)

Als MDX ergibt sich:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Man kann auch die Abfrage etwas erweitern:

Man fügt ein berechnets Measure (in diesem Fall namens „UniqueName“) mit dem MDX

[Datum].[Jahr].CurrentMember.UniqueName

hinzu:

Wizard der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Damit ergibt sich folgendes MDX:

WITH MEMBER [Measures].[UniqueName] AS [Datum].[Jahr].CurrentMember.UniqueName SELECT NON EMPTY { [Measures].[UniqueName] } ON COLUMNS, NON EMPTY { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Anmerkung: Das dabei enstehende versteckte Dataset zum Befüllen der „verfügbaren Werte“ des Parameters löschen wir, da wir ja keine „verfügbaren Werte“ anzeigen wollen (unsere Dimension hatte zu viele Einträge).

Beide Abfragen scheinen dasselbe zu tun und mit der selben Performance. Dies ist aber ein Trugschluss!

Leeren wir zunächst (und vor Ausführung eines neuen Test-Statements) den SSAS-Cache mit

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine“>
<Object>
<DatabaseID>SimpleCube</DatabaseID>
<CubeID>Verkaeufe</CubeID>
</Object>
</ClearCache>

(siehe dazu auch: http://www.ssas-info.com/analysis-services-faq/27-mdx/133-mdx-how-do-i-clear-analysis-services-ssas-database-cache)

Dann betrachten wir die Darstellung im SQL Server Profiler (mit den Standard-Einstellungen für den SSAS):

Die erste Abfrage ergibt folgendes Bild:

SQL Profiler Ergebnis der langsamen Abfrage
(Bild zum Vergößern anklicken!)

Schauen wir uns die einzelnen Zeilen an:

  • Session Initialize – spricht für sich
  • Query Begin: Hier fängt die Abfrage an – im unteren Bereich sieht man das MDX und die Parameterwerte für die @Parameter im MDX
  • Progress Report Begin: Daten aus einer Partition werden gelesen (!), d.h. von der Festplatte in den Speicher übernommen.
  • Progress Report End: das dazugehörige Ende
  • Query SubCube: die gelesenen Daten werden verwendet, um die Abfrage zu beantworten (in diesem Fall Non-Cache)
  • Query End: Das Ende der Abfrage

Die zweite Abfrage ergibt ein anderes Bild:

SQL Profiler Ergebnis der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Hier sehen wir, dass kein Zugriff auf eine Partition erfolgt!

Der Zugriff auf die Partition ist natürlich schädlich, da er bei großen Datenmengen lang dauern kann, zumal er gar nicht benötigt wird, da wir ja nur Dimensions-Element-Bezeichnungen abfragen wollen.
Es ist nicht erklärbar, warum SSAS hier dennoch auf die Fakten zugreift.
Deswegen ist die zweite Abfrage auf jeden Fall vorzuziehen!

Noch ein interessante Anmerkung zum Schluss: Wenn man die erste Abfrage im SQL Server Management Studio ausführt, ist sie auch nicht langsam. Da im Management Studio keine parametrisierten Abfragen möglich sind, muss man dazu die Parameter durch die entsprechenden Strings ersetzen, und erhält somit folgendes MDX:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(„{ [Datum].[Jahr].&[2001],[Datum].[Jahr].&[2008] }“, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Führt man dieses MDX aus (nachdem man den Cache geleert hat), so erhält man folgendes Bild:

Ergebnis des Profilers der langsamen Abfrage ausgeführt im Management Studio
(Bild zum Vergößern anklicken!)

Man sieht hier also auch nur ein Query Begin und ein Query End, also auch keinen Zugriff auf die Cube-(bzw. Partitions-)Daten.

Dies hatte uns die Fehlersuche erschwert, da wir natürlich zunächst auf der Suche nach dem verantwortlichen Statement alle Statements im SQL Server Profiler mitgeschnitten und dann einzeln im Management Studio ausgeführt hatten, was – wie eben gesehen – das problematische Statement leider nicht offenbart.

begin try in SQL-Batch-Statements

Es kommt öfter vor, dass man im Batch mehrere SQL-Statements ausführen möchte, sei es in einer Stored Procedure oder im Execute SQL-Task von SSIS.

Meistens hat man folgende Anforderung:

Läuft ein Statement auf einen Fehler, soll ein Rollback der Statements gemacht werden. Außerdem soll natürlich dem aufrufenden System der Fehler gemeldet werden.

Lässt man einen Batch einfach so laufen, wird dieses Ziel nicht erreicht, da im Fehlerfall auch die Statements nach dem Statement, das den Fehler verursacht, ausgeführt werden.

Beispiel:

set nocount on
select 1
select 1/0
select 2

liefert:

———–
1

———–
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

———–
2

In Versionen vor SQL Server 2005 musste man die Error-Variable auslesen, etwa so:

set nocount on
declare @fehler as int
set @fehler = 0
select 1
set @fehler = @fehler + @@error
select 1/0
set @fehler = @fehler + @@error
select 2
set @fehler = @fehler + @@error
if @fehler>0 begin
print ‚Ein Fehler ist aufgetreten‘
end

was folgendes Ergebnis liefert:

———–
1

———–
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.

———–
2

Ein Fehler ist aufgetreten

Das Problem ist, man muss die Zeile „set @fehler = @fehler + @@error“ nach jedem Statement schreiben, da sie nach jedem (!) Statement zurückgesetzt wird.

Leichter geht das in SQL 2005 mit begin try … end try – angelehnt an Konstrukte aus Programmiersprachen wie C#:

set nocount on
begin try

select 1
select 1/0
select 2

end try
begin catch
print ‚Ein Fehler aufgetreten‘
end catch

Am Ergebnis

———–
1

———–

Ein Fehler aufgetreten

sieht man, dass nach dem fehlerhaften Statement die Bearbeitung beendet wird.

Nun fehlen nur noch 2 Anforderungen:

Dass keine Datenmanipulation statt findet, erreicht man über eine Transaktion, die im catch-Block zurückgerollt (rollback) wird.

Dass der Aufruf dennoch den Fehler mitbekommt, erreicht man über einen raiserror.

Das fertige Skript sieht dann so aus:

set nocount on

begin tran
begin try

/* hier die eigentlichen SQL-Statements schreiben */
select 1
select 1/0
select 2

end try
begin catch
if @@trancount > 0 begin
rollback tran
end
declare @fehler_text nvarchar(4000)
set @fehler_text = ERROR_MESSAGE()
declare @fehler_severity int
set @fehler_severity = ERROR_SEVERITY()
declare @fehler_state int
set @fehler_state = ERROR_STATE()
RAISERROR (@fehler_text, — Message text.
@fehler_severity, — Severity.
@fehler_state — State.
)

end catch
if @@trancount > 0 begin
commit tran
end