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

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.

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.

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.

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

… und dem Ergebnis:

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