Dienstag, 24. April 2012

Parameter in der OLE DB Datenquelle

Die OLE DB Datenquelle bietet die Möglichkeit, flexible Abfragen mit Parametern zu verwenden. Nur: in welchem Format geben Sie die Parameter an?
Im folgenden Beispiel verwendet die OLE DB Datenquelle eine gespeicherte Prozedur, um die Daten zu holen. Die Prozedur hat den Namen getNutzerByPLZ und erwartet die beiden Parameter PLZ_von und PLZ_bis.
Das folgende Bild zeigt die Konfiguration der Quelle:

Konfiguration der OLE DB Datenquelle mit gespeicherter Prozedur (2008 R2)

Sie sehen, dass der SQL-Befehl EXEC die Parameter explizit aufführt. Zur Laufzeit wird an jeden Parameter der Wert übergeben, den SSIS für das Platzhalterzeichen ? einsetzt. Welche Werte das sind, konfigurieren Sie durch Anklicken der Schaltfläche "Parameter..." Das folgende Bild zeigt den Dialog:

Binden von SSIS-Variablen an Parameter der gespeicherten Prozedur (2008 R2)

Zur Laufzeit wird der Inhalt der jeweils angegebenen Variablen an den entsprechenden Parameter der gespeicherten Prozedur übergeben. Angenehm bei dieser Schreibweise ist die Tatsache, dass der Editor die Zuordnung der Fragezeichen zu den Namen der Prozedurparameter vornimmt. Das reduziert die Möglichkeit von Konfigurationsfehlern und ermöglicht das nachträgliche Hinzufügen weiterer Parameter ohne großen Aufwand.

Bei SSIS 2012 sieht es etwas anders aus


SQL Befehl mit Parametern bei OLE DB Datenquelle in SSIS 2012
Bindung der Parameter an Variablen in SSIS 2012

Wenn Sie dasselbe bei der 2012er Version versuchen, bekommen Sie jedoch einen Syntaxfehler! Hier dürfen Sie beim EXEC nicht mehr die Namen der Prozedurparameter aufführen (siehe Bild). Da die Parameterzuordnung jedoch auch in dieser Version den Namen der Parameter erwartet, ist dies auch gut - man muss es nur wissen...

Beim SQL Task geht's auch


Dieselbe Art der Parameterübergabe können Sie auch beim Task 'SQL ausführen' in der Ablaufsteuerung verwenden:

Aufruf einer gespeicherten Prozedur mit Parametern im Task 'SQL ausführen'
Konfigurieren der Parameterzuordnung im Task 'SQL ausführen'
Interessanterweise werden in diesem Dialog die benannten Parameter nicht automatisch angeboten, sondern Sie müssen diese von Hand eingeben. Ansonsten ist die Konfiguration aber analog zur OLE DB Datenquelle.

 

SSIS: Schnelles Laden mit dem OLE DB Datenziel

Wenn Sie in einer Datenfluss-Task mit dem OLE DB Datenziel in eine SQL Server Datenbanktabelle schreiben, gibt es zwei Möglichkeiten des Datenzugriffsmodus: "Tabelle oder Sicht" und "Tabelle oder Sicht – schnelles Laden".  Nachfolgend möchte ich die Unterschiede zwischen diesen beiden Möglichkeiten genauer beleuchten.

Der normale Ladevorgang

Die Konfiguration dieses Datenzugriffsmodus ist sehr einfach: Es gibt keine weiteren Einstellmöglichkeiten im Standard-Editor. Mit dem Profiler können Sie herausfinden, wie dieser Modus funktioniert:
  • Zunächst erzeugt das SSIS-Paket einmal einen Aufruf der gespeicherten Prozedur sp_cursoropen.
  • Anschließend erzeugt es für jeden zu schreibenden Datensatz einen Aufruf der gespeicherten Prozedur sp_cursor.
Vorteil: Jede einzelne Datenzeile, die beim Schreiben in die Zieltabelle einen Fehler verursacht, kann am Fehlerausgang des OLE DB Datenziels abgegriffen und gesondert weiterverarbeitet werden.

Nachteil: SQL Server protokolliert in diesem Modus jeden Schreibvorgang vollständig im Transaktionsprotokoll. Die vollständige Protokollierung der einzelnen Schreibvorgänge kann das Einfügen großer Datenmengen erheblich ausbremsen.

Der schnelle Ladevorgang

Konfigurationsdialog für schnelles Laden
Diesen Nachteil kann das schnelle Laden aufheben. Wiederum ist es der Profiler, mit dem Sie herausfinden können, welche SQL Statements SSIS an die Zieldatenbank schickt. Diesmal sieht der Ablauf anders aus:
  • Das SSIS-Paket erzeugt ein einziges INSERT BULK Statement.
Vorteil: Ein BULK- oder Masseneinfügevorgang wird im SQL Server minimal protokolliert, was einerseits zu einem erheblich höheren Datendurchsatz führt, aber andererseits mit Einschränkungen beim Wiederherstellen der Zieldatenbank verbunden ist (Stichwort: eingeschränktes Point-In-Time Recovery). In aller Regel überwiegen die Vorteile des höheren Datendurchsatzes.

Nachteil: Bei dieser Variante wird das Einfügen im Rahmen einer Transaktion durchgeführt. Wenn also 100.000 Zeilen ins Ziel eingefügt werden und die letzte Zeile beim Schreiben einen Fehler auslöst, dann rollt der Zielserver die gesamte Transaktion zurück und der ganze Einfügevorgang war wirkungslos.
Um diesen Nachteil wettzumachen, können Sie folgendes Entwurfsmuster anwenden:

Entwurfsmuster für die Auswertung fehlerhafter Zeilen beim schnellen Laden
Solange im Rahmen einer Transaktion keine fehlerhaften Zeilen auftreten, leistet das OLE DB Ziel mit schnellem Laden ganze Arbeit. Falls eine fehlerhafte Zeile das schnelle Laden abbricht, reicht das erste OLE DB Ziel alle Datensätze dieser Transaktion an das nächste OLE DB Ziel weiter, das die Datensätze normal lädt. Das geht langsamer, hat aber den Vorteil, dass die fehlerhafte Zeile als einzige in der Fehlertabelle (drittes OLE DB Ziel) landet; alle anderen Datensätze erreichen das gewünschte Ziel.

Feinheiten beim schnellen Ladevorgang

Beim schnellen Laden gibt es (abgesehen von den vier Haken für Beibehalten der Identitätswerte, Behandeln von NULL-Werten, Setzen einer Tabellensperre und das Überprüfen von Einschränkungen) zwei Einstellungen, die für das Verarbeiten von Transaktionen von Bedeutung sind:
  1. Maximale Einfügungcommitgröße (maximum insert commit size) bestimmt die Anzahl von Zeilen, nach denen die Transaktion spätestens beendet wird. Mit der Standardeinstellung (siehe Bild) wird wohl jeder realistische Ladevorgang im Rahmen einer Transaktion durchgeführt. Aber ist das auch gut? Bei sehr großen Datenmengen kann das Transaktionsprotokoll der Zieldatenbank dadurch auf eine erhebliche Größe anwachsen. Außerdem sperrt der Datenbankserver immer mehr Datensä tze (Stichwort: Sperrenausweitung / lock escalation), was andere Prozesse behindert könnte. Um dem entgegenzuwirken, sollten Sie die Größe einer Transaktion zum Beispiel auf 5000 Zeilen begrenzen. Dann beendet der Datenflusstask immer nach 5000 Zeilen die Transaktion und beginnt eine neue.
  2. Zeilen pro Batch ist eine Größe, zu der praktisch keine Dokumentation zu finden ist. Das hängt damit zusammen, dass der INSERT BULK Befehl (Verwechslungsgefahr: es handelt sich dabei nicht um das BULK INSERT Kommando), den SSIS generiert, nur sehr oberflächlich dokumentiert ist. Programmierer können ihn mit der .NET Klasse SQLBulkCopy verwenden, aber auch hier findet sich keine weitergehende Dokumentation zur Batchgröße. So bleibt uns SSIS-Entwicklern nichts übrig, als im Zweifelsfall verschiedene Werte für diesen Parameter auszuprobieren und den für unseren Fall besten Wert experimentell zu ermitteln.
Vielen Dank an Torsten Strauß für seine Hinweise zu diesem Thema! Über Kommentare und Ergänzungen zu diesem Thema freue ich mich – vielleicht lässt sich ja doch noch mehr Licht in diese Ecke des OLE DB Ziels bringen.