Archiv der Kategorie: MS Integration Services

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

Rollierendes Log-File in SSIS-Paketen

Bei Integration Services-Paketen ist es einfach, ein Logfile einzustellen (SSIS>Logging…), das gewünschte Events (Errors, Warnings, Information, …) protokolliert. Im Standard wird ein solches Logfile bei jedem Lauf des Pakets größer, da immer neue Zeilen angefügt werden. Dies hat Nachteile, da es Eingriffe durch einen Administrator erfordert.

In der Regel verwende ich deshalb in meinen Paketen Logfiles, die den Wochentag im Logfilenamen haben (datei_1.log für Montag, datei_2.log für Dienstag, …), so dass die Logfiles nach 7 Tagen überschrieben werden, so dass eine ausreichende Anzahl von Logfiles für die Fehlersuche zur Verfügung stehen, der belegte Plattenplatz aber nicht ständig steigt.

Dazu gehe ich wie folgt vor:

Es werden zwei Variablen definiert, deren Werte über Konfigurationsdateien (*.dtsconfig via SSIS>Package Configurations…) eingestellt werden:

  • setting_LogPath: Der Pfad, in dem die Logfiles liegen sollen, z.B. „c:temp“
  • setting_LogFileName: Name des Logfiles, z.B. testlog

Dann gibt es eine Variable „LogFile“, die über eine Expression berechnet wird:

Variable LogFile - einige Properties

@[User::setting_LogPath] + ((right(@[User::setting_LogPath],1)==“\“) ? „“ : „\“) + @[User::setting_LogFileName] + „_“ + (DT_WSTR, 1) DATEPART( „dw“, GETDATE() ) + „.log“

Einige Bemerkungen zu dieser Berechnung:

  • Falls der Pfad nicht auf endet, wird ein an den Pfad angefügt. Achtung muss wie in C# zu \ „escapet“ werden.
  • Der Wochentag wird über Datepart ermittelt. Statt GetDate() könnte auch das Startdatum des Pakets (System-Variable @[System::StartTime]) verwendet werden
  • Im deutschen SSIS heißen die Variablen @[Benutzer:: statt @[User::

Im Connection Manager wird dann eine File-Connection angelegt:

SSISLogFile im Connection Manager

Über die Expressions dieser Connection wird der Dateiname auf die Variable „Logfile“ gesetzt:

Eigenschaften der SSISLogFile Connection

Somit wird jeden Wochentag ein anderes Logfile verwendet. Da dabei immer angefügt wird, muss noch das morgige Logfile gelöscht werden.

Dazu wird noch eine Variable „LogFile2Delete“ angelegt, die über folgende Expression – analog zu LogFile – berechnet wird:

@[User::setting_LogPath] + ((right(@[User::setting_LogPath],1)==“\“) ? „“ : „\“) + @[User::setting_LogFileName] + „_“ + (DT_WSTR, 1) DATEPART( „dw“, dateadd(„day“, 1, GETDATE()) ) + „.log“

Über ein FileSystemTask wird diese Datei dann zum Ende des Pakets gelöscht. Falls die Datei nicht existiert, macht das nichts – die FileSystemTask schmeißt keinen Fehler.

Lösche morgiges Logfile über eine FileSystemTask

Einstellungen der FilesystemTask

SSIS – binäre Collation bei der Suche

In einem früheren Blog-Eintrag habe ich beschrieben, wie die Suche im SQL Server Integration Services (im Standard) abhängig von der Groß- und Kleinschreibung ist. Das liegt daran, dass der Cache im Integration Services binär angelegt werden, so dass „Martin“, „martin“ und „MARTIN“ 3 unterschiedliche ELemente sind, wohingegen bei einem SELECT DISTINCT Vorname FROM Personen dies (in der Standard-Collation) nur als einen Wert zurückliefern würde.

Dieses Phänomen tritt aber natürlich nicht nur bei Groß- und Kleinschreibung auf, sondern bei allen Strings, die laut Collation gleich sind.

Damit führt das Standard-Szenario nicht zum gewünschten Ergebnis, wenn ich zum Beispiel Vornamen in eine Dimensionstabelle umsetzen will:

Paket-Beispiel Vornamen auf IDs umsetzen

Laden der Dimensionen (Vorname)Beispiel für Faktenimport

 

In meinem Beispiel habe ich die Personen-Tabelle wie folgt gefüllt:

Inhalt der Tabelle Personen

Auf der Nachnamen-Spalte habe ich die Standard-Collation (SQL_Latin1_General_CP1_CI_AS) verwendet, die Vornamen-Spalte habe ich so eingestellt, dass auch Akzente ignoriert werden (Collation Latin1_General_CI_AI) (OK, das ist ein bisschen gestellt, aber in der Praxis hatte ich einen ähnlichen Fall).

Somit liefert select distinct vorname from person:

SELECT DISTINCT Vorname FROM Person

und select distinct nachname from person:

SLECT DiSTINCT Nachname FROM Person

Man beachte, dass bei der Standard-Collation ß wie ss behandelt wird.

Nun ist klar, dass beim Lookup nach Vorname im Faktenimport nicht alle Vornamen einen Treffer liefern und somit das ETL einen Fehler schmeißt.

Außerdem ist es vermutlich nicht sinnvoll, dass Michel und Michél gleiche Vornamen sind. Deswegen beitet es sich in diesem Fall an, dass alle unterschiedlichen Schreibweisen der Vornamen auch unterschiedliche Dimensionselemente werden. Dazu kann man im Dimensions-SELECT statt „SELECT DISTINCT Vorname FROM Personen“ besser „SELECT DISTINCT vorname COLLATE Latin1_General_BIN as Vorname from person“ verwendet. Damit instruiert man den SQL Server die binäre Collation zu verwenden und somit sind beim SQL Server selbst die einzelnen Vornamen nicht mehr gleich – und schon geht’s.

Eine andere Alternative wäre gewesen, über eine Funktion Strings, die gleich sein sollen, in einen eindeutigen String zu überführen (wie in meinem letzten Blog-Eintrag mit UPPER()). Bei Akzenten ist das aber nicht so einfach.

Eine weitere Alternative ist, den Lookup nicht mit vollem Cache zu machen, was aber negative Auswirkungen auf die Performance hat. Also könnte man auch zuerst einen Lookup mit vollem Cache und im Fehlerfall einen nachgelagerten Lookup ohne Cache ausführen. Im zweiten Lookup kann man dann über die Collation genau definieren, welche Strings gleich sein sollen und welche nicht.

In einem solchen Fall muss man also auf jeden definieren, welche Strings im DWH als gleich angesehen werden sollen. Über die COLLATION kann man das – wie gezeigt – sehr fein einstellen.

Natürlich sind solche Szenarien selten, da in der Regel nicht beliebige Freitextwerte in einer Dimension vorkommen – aber, wie in meinen Projekten geschehen, ab und zu gibt es dann doch solche Fälle.

Konfigurationsdateien im SSIS – Nutzen und Fallen

In SSIS-Paketen können alle möglichen Einstellungen in Konfigurationsdateien ausgelagert werden. Insbesondere können damit

  • die Eigenschaften von Verbindungen wie ConnectionString, User, Passwort
  • oder Variablen wie Name des zu durchsuchenden Datei-Ordners oder ähnliches

extern konfiguriert werden – ganz analog zur web.config in ASP.NET oder app.config in Windows-Programmen. Theoretisch ist es möglich, viel mehr Attribute als oben genannte in der Konfigurationsdatei zu pflegen – aus Übersichtlichkeitsgründen ist das aber nicht empfohlen.

Dies hat den Sinn, dass das Paket in der Entwicklungs-, Test- und Produktionsumgebung immer gleich ist und nur die Konfigurationsdateien bei einer Installation angepasst werden. Dadurch wird sichergestellt, dass wirklich das getestete Paket in Produktion geht und nicht aus Versehen beim Öffnen im Visual Studio irgendwelche Einstellungen angepasst wurden. Dies ist in meinen Augen – neben vielen anderen Features – ein wesentlicher Vorteil von SSIS gegenüber seinem Vorgänger DTS.

Konfigurationsdateien werden erstellt über SSIS > Package Configurations… :

Erstellen von Konfigurationsdateien

In der Regel werden XML-Konfigurationsdateien verwendet. Dort kann man den Ort der Konfigurationsdatei und die enthaltenen Attribute angeben. Dadurch erzeugt das Visual Studio die entsprechende Konfigurationsdatei. In dieser Konfigurationsdatei sind alle Werte der Attribute gespeichert, so wie sie derzeit (im Verbindungsmanager, Variablenfenster, etc.) definiert sind. Einzige Ausnahme: Falls im Verbindungsmanager ein Passwort verwendet wird, wird in der Konfigurationsdatei zwar dieses Attribut angelegt, aber nicht der Wert übernommen. (erste kleine Falle)

Nun ist die Idee, bei der Installation in der Produktiv-Umgebung das dtsx-Paket unverändert zu lassen und nur die Konfigurationsdatei anzupassen. Beim Starten des Pakets kann man dann die zu verwendende Konfigurationsdatei angeben (z.B. im SQL Server Agent oder beim direkten Aufruf von DTExec) und diese wird beim Ausführen des Pakets verwendet.

Beispiel für die Verwendung einer Konfigurationsdatei im SQL Server Agent

Allerdings gibt es ein paar Fallen, die man umschiffen sollte:

Falle 1: Änderungen an Variablen während der Entwicklung gehen beim Starten des Pakets im Visual Studio verloren:

Wenn man im Visual Studio Eigenschaften ändert, die bereits in die Konfigurationsdatei exportiert wurden, ist diese Änderung wirkungslos. Beim Start des Pakets wird ja die Konfigurationsdatei geladen und dessen Wert verwendet. Eine automatische Aktualisierung der Konfigurationsdatei bei Änderungen im Visual Studio findet (glücklicherweise) nicht statt.

Falle 2: Das Paket versucht beim Start die im Paket definierte Konfigurationsdatei zu laden

Im produktiven Umfeld liegt die Konfigurationsdatei wahrscheinlich nicht am selben Ort (im Dateisystem) wie in der Entwicklungsumgebung. Wenn man nun das Paket (wie unter Idee beschrieben) in der Produktion unter Angabe der Produktions-Konfigurationsdatei startet, so versucht das Paket zunächst die Datei zu laden, deren Pfad im Paket (s. obiger Screenshot) definiert ist. Diese Datei wird in der Regel nicht gefunden werden. Dies wird als Fehler protokolliert – das Paket bricht aber nicht ab. Danach wird die beim Aufruf mitgegebene Konfigurationsdatei geladen. Somit läuft das Paket sauber durch – allerdings ist ein Fehler protokolliert, der Verwirrung stiften kann. Deswegen ist es am sinnvollsten, den Haken bei „Enable package configurations“ (s. Screenshot) wieder zu entfernen. Durch diesen Haken hat man sich somit nur auf einfache Art und Weise eine Konfigurationsdatei erstellen lassen (anstelle sie komplett selbst manuell zu erstellen).

Nun könnte man sich wundern, ob dann trotzdem die beim Aufruf mitgegebene Konfigurationsdatei geladen wird. Die Bedeutung des Hakens ist in meinen Augen nämlich etwas verwirrend. Es besagt nämlich nicht „Dieses Paket hat eine Kofnigurationsdatei mit den dort eingestellten Attributen“. Egal ob der Haken gesetzt ist oder nicht, kann eine Konfigurationsdatei beim Start angegeben werden, die beliebige Attribute verändert, also auch Attribute, die nicht im Visual Studio zum Export in die Konfigurationsdatei markiert worden waren. Der Haken besagt somit, dass „Dieses Paket lädt beim Start automatisch die angegebene Konfigurationsdatei – danach werden die beim Start übergebenen Konfigurationsdateien geladen“.

Dieses Verhalten ist in meinen Augen wenig intuitiv – aber so ist es halt. Dadurch kann man sein eigenes Paket also auch nicht schützen, so dass bestimmte Attribute nicht von außen durch Konfigurationsdateien überschrieben werden können.

Verschlüsselungs-Fehlermeldung beim Ausführen von SSIS-Paketen

Ein gängiges Szenario für das produktive Ausführen von SSIS-Paketen ist die Ausführung im SQL Server Agent.

Für Verwirrung sorgt dabei häufig eine Fehlermeldung, dass der Knoten aufgrund eines Verschlüsselungsfehlers nicht geöffnet werden kann. Falls ein Paket nicht ausgeführt werden konnte, führt das leicht auf eine falsche Fährte.

kryptographischer Fehler

Dieser Fehler ist nämlich nicht Ursache dafür, dass das Paket auf einen Fehler gelaufen ist. Dieser Fehler tritt in folgender Situation auf:

  • Das Paket wird durch einen User geöffnet, der das Paket nicht gespeichert hat.
  • Das Paket enthält eine Verbindung, die ein Passwort gesetzt hat – auch wenn diese Verbindung gar nicht verwendet wird (weil die Verbindungsinformationen z.B. durch eine Konfigurationsdatei o.ä. überschrieben werden.)
  • Die Einstellungen des Pakets (ProtectionLevel) stehen auf „encrypt sensitive with user key“

Dann versucht Integration Services nämlich alle sensitiven Daten – das ist vor allem das Password in einer Verbindung – mit dem aktuellen User zu entschlüsseln – und scheitert und protokolliert den Fehler. Integration Services bricht dann aber nicht die Verarbeitung ab, sondern verwendet dann ein leeres Passwort. In meinem Beispiel-Screenshot war der tatsächliche Fehler eine (bewusst herbeigeführte) Division durch Null. Diesen Fehler sieht man ganz am Ende.

Im produktiven Umfeld ist das im Normalfall kein Problem, da normalerweise Integrierte Sicherheit (also Anmeldung mit dem Windows-User) verwendet wird oder die Verbindungsinformationen (Server, Datenbank und eben auch User und Passwort) extern verwaltet werden (also in einem Config-File oder im SQL Server Agent etc.). Letzteres hat den Grund, dass man Pakete auf dem Produktivsystem ohne Veränderung (d.h. ohne Öffnen im Visual Studio) aus dem Testsystem installieren will.

Somit kann man einfach die Einstellung auf „Do not save sensitive“ stellen. Dann tritt der oben beschriebene Fehler nicht auf und die verwirrende Fehlermeldung in der Task History des SQL Server Agent taucht nicht auf. In meinem Beispiel erkennt man die Division durch 0 jetzt viel besser:

Fehlermeldung Division durch 0 viel leichter erkennbar

 

Um allerdings eine gute Grundlage für die Fehlersuche zu haben, reicht auch die Anzeige im SQL Server Agent nicht aus. Man sollte besser Logging aktivieren – (dazu vielleicht später mehr 🙂 )

SSIS Pakete im SQL Server Agent unter einem eigenen User starten

Zur zeitzgesteuerten Verarbeitung von SSIS-Paketen bietet sich der beim SQL Server 2005 mitgelieferte SQL Server Agent an. Wie man im Screen Shot sieht, bietet der SQL Server Agent an, die SSIS-Pakete unter einem bestimmten User auszuführen. Diese Kombobox („Run as“) ist im Standard allerdings auf den Benutzer-Konto des SQL Server Agent-Dienstes beschränkt:

Als RunAs steht nur das Benutzerkonto des SQL Server Agents zur Verfügung

Hier möchte ich zeigen, wie man diese Kombobox um einen beliebigen User erweitern kann und somit auch ein Paket unter diesem User ausführen kann.

Dazu muss zunächst ein Credential (ein Windows-Konto samt Kennwort) angelegt werden und dieser dann für die Ausführung von SSIS-Paketen freigegeben werden.

Das Credential gibt man im SQL Server Management Studio (wenn man auf den relationalen Datenbank-Server verbunden ist) unter Security > Credentials ein, also z.B.:

Ein Credential wird angelegt

Unter SQL Server Agent > Proxies – also hier:

Eingabe Proxies– muss dieser Credential nun für die Ausführung von SSIS-Paketen definiert werden. Dazu erstellt man einen neuen Proxy unter einem beliebigen Namen, der das neu eingegebene Credential verwendet. Bei den erlaubten Subsystemen setzt man den Haken bei den SSIS-Paketen (und allen weiteren gewünschten Systemen):

Erstellen eines Proxies mit Erlaubnis für SSIS-Pakete

Und schon kann das Paket im SQL Server Agent unter diesem Proxy ausgeführt werden:

Ausführung des Jobs unter dem neu angelegten Proxy

SSIS: Achtung bei Groß- und Kleinschreibung – Warum

Gestern und vorgestern habe ich anhand der Beispiele Aggregate und Lookup beschrieben, welche Unterschiede zwischen SSIS und SQL Server in Bezug auf Groß- und Kleinschreibung zu beachten sind.

Hier ein Wort zur Motivation:

Man könnte sich ja fragen, warum ist da der SSIS so pingelig und erschwert mir als altem SQL-Entwickler die Arbeit?

Ein Grundprinzip beim SSIS ist Performance (worüber wir alle ja froh sind).

Offensichtlich ist es schneller, wenn ein Unterschied zwischen Groß- und Kleinschreibung gemacht wird, denn – binär gesehen – besteht natürlich ein Unterschied zwischen „a“ und „A“. Also seien wir froh 🙂 , dass der SSIS sich genau so verhält.

SSIS: Achtung bei Groß- und Kleinschreibung – Lookup

Gestern habe ich beschrieben, welche Unterschiede zwischen SQL Server und SSIS bei Groß- und Kleinschreibung bei der Aggregation zu beachten sind.

Natürlich ist das Verhalten der beiden Produkte konsistent:

  • SQL Server unterscheidet grundsätzlich (im Standard) nicht zwischen Groß- und Kleinschreibung
  • SSIS unterscheidet grundsätzlich (im Standard) zwischen Groß- und Kleinschreibung

Damit ist auch klar, dass andere Transformationen von Unterschieden betroffen sind. Heute betrachte ich die SSIS-Transformation Lookup (Suche):

Als Beispiel verwende ich wieder die Tabelle Customers

Tabelle Customers

und die neue Tabelle Countries

tabelleCountries

Eine Anmerkung zu diesem Beispiel: Die Verwendung von Strings für Schlüssel liefert mir ein einfaches Besipiel, soll aber natürlich nicht als Standard für die Datenmodellierung angesehen werden 🙂

Der SSIS-Data Flow soll nun alle Kunden mit zugehöriger Hauptstadt ermitteln.

Die Lookup-Transformation funktioniert auf zwei unterschiedliche Arten:

  • komplettes Laden der Lookup-Tabelle (hier Countries) vor dem Start der Ausführung des eigentlichen Data Flows – dies ist der Standard
  • Einzelnes Laden der nachzuschlagenden Datensätze – wenn man unter dem 3. Reriter (Advanced) den Haken bei „Enable memory restriction“ aktiviert.

Ich beginne mit dem zweiten:

Einzelnes Laden der nachzuschlagenden Datensätze:

Die Einstellungen für die einzelnen Reiter der Lookup-Transformation sind wie folgt:

  • Reference Table: verwende Tabelle Countries
  • Columns: Der Join geht über Country — Country. Als zusätzliche Spalte wird Capital ausgegeben
  • Advanced: Wir setzen den Halen bei „Enable memory restriction“. Der Rest bleibt im Standard (keine weiteren Haken)

Als Ergebnis erhalten wir:

ErgebnisLookup2

Das liefert also das gewünschte Ergebnis.

Wie geht hier SSIS intern vor?

Für jede Zeile wird ein SQL-Statement ausgeführt, das zu der Country die Capital dazuliest (Also z.B. SELECT * FROM Countries WHERE Country = ‚de‘) [Das genaue SQL-Statement sieht man im Bereich „Caching SQL-Statement“ auf dem 3. Reiter (Advanced)].

Da der SQL Server nicht zwischen Groß- und Kleinschreibung unterscheidet, unterscheidet also auch diese Version der Lookup-Transformation nicht.

Natürlich ist aber dieses Vorgehen bei großen Datenmengen imperformant, da für jede Zeile ein SQL-Select ausgeführt wird. Daran ändern auch die weiteren Optionen unter Advanced nichts (grundlegendes): Enable Caching würde nur verhindern, dass nicht zweimal dasselbe SQL-Statement ausgeführt wird (Wenn also „de“ in zwei Zeilen auftauchen würde). Der Cache selbst wäre im übrigen wieder case-sensitive (D.h. unterscheidet zwischen Groß- und Kleinschreibung) – es würden also für „de“ und „DE“ zwei SELECTs ausgeführt.

Deswegen jetzt die Betrachtung der Standard-Methode des Lookups:

komplettes Laden der Lookup-Tabelle

Wir entfernen den Haken „Enable memory Restriction“ im Tab „Advanced“ und starten das Paket nochmals.

Sofort erhalten wir einen Fehler, dass der Lookup keinen Treffer findet. Durch Erweitern des Data Flows sehen wir die nicht gefundenen Datensätze:

Bild

Wir sehen also, dass der Lookup im Standard Groß- und Kleinschreibung unterscheidet.

Wie kann man dieses Problem umgehen?

Man kann natürlich die verwendeten Spalten vor dem Zugriff auf Großbuchstaben konvertieren:

  • Im SSIS durch das Einfügen einer derived Column, die mittels UPPER( [Country]) entweder eine neue Spalte erzeugt oder die bestehende überschreibt.
  • Im SQL-Statement für den Referenz-SELECT durch UPPER(), z.B. SELECT Upper(Country) as CountryUpper, * from countries

Natürlich gibt es noch andere Möglichkeiten zum Umgehen des Problems wie Fehlerhandling. Das erscheint mir hier aber konstruiert.

SSIS: Achtung bei Groß- und Kleinschreibung – AGGREGATE

Der SQL-Server unterscheidet (im Standard) bei den Daten nicht zwischen Groß- und Kleinschreibung.

Gerade für versierte SQL-Entwickler führt dies bei der Verwendung der Integration Services zu Schwierigkeiten, da Integration Services sehr wohl zwischen Groß- und Kleinschreibung unterscheidet. Somit verhalten sich SSIS-Transformationen leicht anders als ihre SQL-Pendants:

Ich möchte auf die SSIS-Transformationen Aggregation (Aggregate) und die Suche (Lookup) [im nächsten Blog-Eintrag] eingehen:

Zunächst vergleiche ich die SSIS-Transformation Aggregate mit dem SQL-Befehl GROUP BY:

Als Ausgangsdaten verwende ich die Tabelle CustomersTabelle Customers

Damit liefert das SQL-Statement

SELECT Country, count(*) as Anzahl
FROM Customers
Group by Country

folgendes Ergebnis (de=DE im SQL Server):

Bild

Die naheliegende SSIS-Transformation liefert aber (de <> DE im SSIS):

Bild

Hierbei habe ich das Standard-Verhalten der Tools dargestellt. Man kann aber in beiden Produkten ein gleiches Verhalten erzeugen:

  • Im SQL Server könnte man die Collation Spalte ändern, so dass die Werte auch Groß- und Kleinschreibung unterscheidet, also zum Beispiel Latin1_General_CS_AS
    Bild
    Diese Änderung betrifft natürlich die komplette Tabelle und somit alle Abfragen auf diese Tabelle!
  • Wenn man nur diese eine Abfrage im SQL auf „Beachte Groß- und Kleinschreibung“ setzen will, so kann man folgendes Statement verwenden:
    SELECT Country , count(*) as Anzahl
    FROM (select country COLLATE Latin1_General_CS_AS as Country, CustomerName FROM Customers) as tab
    Group by Country
    Ohne die Verwendung von Derived Tables (dem obigen „SubSelect“) bzw. Common Table Expressions geht es nicht. Ein Statement wie
    SELECT Country COLLATE Latin1_General_CS_AS, count(*) as Anzahl
    FROM Customers
    Group by Country
    liefert also immer noch 5 Zeilen.
  • In der SSIS-Transformation Aggregate kann man einstellen, dass die Groß- und Kleinschreibung ignoriert werden soll:
    Bild

SSIS semi-blocking transformation für Distinct: PIVOT

In SSIS gibt es mehrere Möglichkeiten, auf einer Datenmenge einen DISTINCT anzuwenden.
Die einfachsten sind SORT und AGGREGATE.

Diese Transformationen haben jedoch den Nachteil, dass sie sogenannte blocking transformations sind, d.h. dass der nächste Schritt erst durchgeführt wird, nachdem alle Daten durch die SORT– oder AGGREGATE-Transformation gegangen sind.
Dies erhöht natürlich den Speicherbedarf und die Performance.

Die PIVOT-Transformation bietet aber auch diese Möglichkeit und ist dabei nur semi-blocking, d.h. PIVOT arbeitet asynchron, startet aber bereits mit der Ausgabe, auch wenn noch nicht alle Daten verarbeitet wurden. [Ein netter Blog-Eintrag über blocking, semi-blocking etc. findet sich hier: sqlblogcasts.com/blogs/jorg/archive/2008/02/27/…]

Ein Nachteil ist natürlich, dass die PIVOT-Transformation nicht so einfach einzustellen ist, da nur der Advanced Editor zur Verfügung steht, alle Output-Spalten manuell eingetragen werden müssen und sogar die Lineage-ID getippt werden muss. Außerdem muss der Input nach dem Schlüssel sortiert vorliegen – sonst funktioniert PIVOT nicht korrekt. Dies kann aber über ein ORDER BY im SQL leicht erreicht werden.

Es ist zu beachten, dass mindestens eine Spalte ein Pivot-Schlüssel sein muss und eine Spalte die pivotierte Spalte sein muss. Das Ergebnis dieser Spalte muss man allerdings nicht verwenden.

Hier die Einstellungen in einem Beispiel:

Als Abfrage verwenden wir

SELECT YEAR(OrderDate) AS Auftragsjahr, MONTH(OrderDate) AS AuftragsMonat, DAY(OrderDate) AS Auftragstag
FROM Purchasing.PurchaseOrderHeader
ORDER BY 1, 2, 3

 

auf die AdventureWorks-Datenbank.

Das PIVOT-Element wird wie folgt definiert:

  • Tab „Input Columns“: Alle Spalten als READONLY markieren
  • Tab „Input and Output Properties“ > „Pivot Default Input“ > „Input Columns“:
    • Für Auftragsjahr und AuftragsMonat die Eigenschaft „PivotUsage“ auf 1 stellen (soll heißen „Schlüssel-Element“)
    • Für Auftragsdatum (diese Spalte brauchen wir nicht mehr) die Eigenschaft „PivotUsage“ auf 2 stellen (soll heißen „diese Spalteninhalte werden pivotiert“ – da aber keine Ziel-Spalten dazu angegeben wurden, passiert das nicht)
  • Tab „Input and Output Properties“ > „Pivot Default Output“ > „Output Columns“:
    • zwei neue Spalten anlegen „Auftragsjahr“ und „Auftragsmonat“
    • Jeweils als Name „Auftragsjahr“ bzw. „Auftragsmonat“ eintragen
    • Jeweils als SourceColumn die LineageID der Quell-Spalte eintragen (Diese ersieht man aus den Eigenschaften der entsprechenden Input Column – 1 Zeile über dem Namen)

Hier ein paar Screen Shots zu den Einstellungen… :

EinstellungenSeite1

EinstellungenSeite2

… und dem Ergebnis:

DataFlow

Vorher Nachher

Eine Anmerkung:

Dieser Trick funktioniert nur, wenn man eine Spalte im Recordset hat, die man nicht mehr benötigt – wie in unserem Fall der Auftragstag. Wenn man aber eine solche Spalte nicht zur Verfügung hat, kann über eine abgeleitete Spalte (derived column) leicht eine solche erstellen.

Das dtsx-Paket steht hier zum Download bereit: PivotStattAggregate.zip