MS Integration Services, Projekte, VB.NET

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

14. Juni 2015 Martin Cremer Schreibe einen Kommentar

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:

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)

MS Reporting Services

SSRS: Top 10 und Rest zum Auf- und Zuklappen

15. April 2015 Martin Cremer Schreibe einen Kommentar

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:

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:

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

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:

Diese Gruppe stellen wir so ein:

Details:

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:

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)

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:

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

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

Die Zellen ganz links setzen wir:

Name = txt_Top
Inhalt = Top 5

bzw.

Name = txt_Rest
Inhalt = Rest

Unser Bericht sieht dann so aus:

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”

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!

und

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.

MS Analysis Services

SSAS Tabular DAX: DATEDIFF-Ersatz

24. Januar 2015 Martin Cremer Schreibe einen Kommentar

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:

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:

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])

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.

MS Integration Services, VB.NET

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

4. Januar 2015 Martin Cremer Schreibe einen Kommentar

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:

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”):

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.

MS Integration Services

NULL-Werte bei Lookups

31. Dezember 2014 Martin Cremer Schreibe einen Kommentar

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:

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:

Der 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:

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:

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.

MS Integration Services

Error Handling im Data Flow eines SSIS-Pakets

31. Dezember 2014 Martin Cremer Schreibe einen Kommentar

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.

MS SQL Server

Sichere Logging-Tabellen trotz @@identity

5. Oktober 2013 Martin Cremer Schreibe einen Kommentar

In meinem letzten Blog-Eintrag hatte ich geschrieben, man solle möglichst scope_identity() statt @@identity verwenden.

Ich hatte bei einem Kunden genau den Fall, dass wir nachträglich in einer bestehenden Datenbank-Applikation über Trigger Protokollierungen erstellen wollten, um genauere Informationen über ein Fehlverhalten zu bekommen.

Deshalb legten wir eine Logging-Tabelle an, die die entsprechenden Informationen aufnehmen sollte und einen Trigger auf bestimmte Tabellen, um diese Informationen zu sammeln und wegzuschreiben.

Leider hatte der damalige Entwickler @@identity und nicht scope_identity() verwendet, so dass eine Identity-Column in unserer Logging-Tabelle zu Fehlern führte: Beim INSERT erhält das aufrufende Programm eine falsche ID. Hoffentlich ist das Programm sonst sauber programmiert, dann wird man dies wahrscheinlich über krachende Foreign Key-Beziehungen erfahren.

Wir kann man so ein Problem aber lösen, wenn man nichts von dem zugrunde liegenden Programm weiß?

Man könnte die Identity-Column in der Logging-Tabelle entfernen und stattdessen Unique Identifiers verwenden. Ich hatte ja schon einiges über die Nachteile von Unique Identifiers geschrieben.

In diesem Fall scheint es mir aber gerechtfertigt. Natürlich muss man die Spalte mit newSequentialId() initialisieren, also etwa so:

CREATE TABLE [dbo].[Logging](
    [LogID] [uniqueidentifier] NOT NULL default(newSequentialId()),
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Dann ist die Änderung unschädlich für den uns unbekannten Code, der möglicherweise @@identity verwendet.

MS SQL Server

Scope_Identity statt @@Identity

5. Oktober 2013 Martin Cremer Schreibe einen Kommentar

Beides sind Funktionen, um den Wert der Identity-Column nach dem Insert zu erhalten. Als ich SQL Server gelernt hatte (1997), hatte ich nur @@identity gekannt.

@@Identity hat aber einen entscheidenden Nachteil, aber dazu später mehr.

Nehmen wir an, wir haben eine Kunden-Tabelle mit Identity-Column KundeID:

CREATE TABLE [dbo].[Kunden](
    [KundeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Kunden] PRIMARY KEY CLUSTERED
(
    [KundeID] ASC
)
)

Wenn wir in diese Tabelle einen Kunden eintragen und dann das Ergebnis mit @@Identity oder SCOPE_Identity() abfragen, gibt es keinen Unterschied:

INSERT INTO Kunden SELECT ‚Martin‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

Beides liefert den Wert 1.

Wenn wir aber nun eine Logging-Tabelle hinzufügen, die ebenfalls eine Identity-Column hat:

CREATE TABLE [dbo].[Logging](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Und der Tabelle Kunden einen Insert-Trigger geben, der in diese Tabelle schreibt:

CREATE TRIGGER dbo.tr_i_Kunden
   ON dbo.Kunden
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Logging (LogText) VALUES (‚Kunde angelegt‘)
    INSERT INTO Logging (LogText) VALUES (‚und dann habe ich noch überprüft, ob er schon da ist‘)
    INSERT INTO Logging (LogText) VALUES (‚und noch irgendwas anderes auch gemacht‘)

END

Wenn wir jetzt einen Kunden anlegen, werden automatisch noch 3 Zeilen in die Logging-Tabelle geschrieben.

Da @@identity die letzte Identity-Column der aktuellen Connection zurückliefert, liefert dies den Identity-Wert aus der Logging-Tabelle.

Da SCOPE_Identity() die letzte Identity-Column des aktuellen Scopes (also der betrachteten Tabelle) in der aktuellen Connection zurückliefert, gibt das den Identity-Wert der Kunden-Tabelle.

Wir sehen das hier:

INSERT INTO Kunden SELECT ‚mein Schatz‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

liefert die Werte 3 für @@identity (da in Logging als letztes die Zeile 3 eingefügt wurde) bzw. 2 für Scope_identity().

Man sollte deshalb grundsätzlich SCOPE_IDENTITY() verwenden, da dies das in der Regel gewünschte Verhalten ist.

Es gibt noch eine Funktion IDENT_CURRENT(‘Tabellenname’). Diese liefert den letzten Identity-Wert für diese Tabelle – egal ob aus meiner Connection oder nicht. Damit sollte man also nicht versuchen, den gerade eingefügten Identity-Wert zu ermitteln. Wenn nämlich parallel eine andere Connection schreibt, hat man Pech gehabt.

MS Integration Services

SSIS: Tabellen-Variablen als Datenquelle

28. Dezember 2012 Martin Cremer Schreibe einen Kommentar

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

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:

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:

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:

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

MS Integration Services

SSIS: Laufzeitprotokollierung je Task

18. November 2012 Martin Cremer Ein Kommentar

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… :

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:

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.

CSoPro BI Blog

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

Schleife über alle Komponenten

Identifikation des bestehenden Error Handlings

SSRS: Top 10 und Rest zum Auf- und Zuklappen

SSAS Tabular DAX: DATEDIFF-Ersatz

Hintergrundwissen

DateDiff von Dates

DateDiff von DateTimes

Erweiterungen: Alter

Erweiterungen: DateDiff in Sekunden

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

DLLs einbinden

Paket laden

Alle Tasks ermitteln

NULL-Werte bei Lookups

Error Handling im Data Flow eines SSIS-Pakets

Sichere Logging-Tabellen trotz @@identity

Scope_Identity statt @@Identity

SSIS: Tabellen-Variablen als Datenquelle

SSIS: Laufzeitprotokollierung je Task

Meine Erfahrungen in der Business Intelligence Welt