Archiv der Kategorie: MS Integration Services

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

MS Integration Services

SSIS: Achtung bei Groß- und Kleinschreibung – AGGREGATE

3 Kommentare

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
MS Integration Services

SSIS semi-blocking transformation für Distinct: PIVOT

Schreibe einen Kommentar

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