Home
Datenquellen
Controllflow
DataFlow
SSIS
ToC
Impressum
 

Die Suche Transformation (Lookup Transformation)

Die Suche Transformation ist eine Transformation, die immer wieder sinnvoll eingesetzt werden kann. Obwohl die Komplexität in der Regel kein Problem darstellt, gibt es einige Dinge, die beachtet werden sollten.

Damit Sie mit der Funktion der Suche Transformation spielen können habe ich Ihnen ein entsprechendes Demopaket zusammen gebaut (Lookup.zip). Bitte passen Sie die Verbindung-Manager (Connection Manager) im Paket entsprechend Ihrer Umgebung an.
Beim Demopaket sind auch Daten, die ich vorher aus der Datenbank AdventureWorks und der Tabelle Person.Contact erzeugt habe als Textdatei dabei. Das Demopaket erzeugt zwei gespeicherte Prozeduren mit den Namen SSISContact und SSISContact2 im Datenbankschema dbo. Die letzte Task im Demopaket löscht diese beiden Prozeduren wieder. Wenn Sie das verhindern wollen, deaktivieren Sie bitte die letzte Task.



Ich habe einzelne Beispiele in unterschiedlichen Datenflusstasks abgebildet. Bitte aktivieren Sie die jeweilige Datenflusstask die Sie ausführen wollen bevor Sie das Paket starten.

Bitte passen Sie die Conncection Manager an Ihre Umgebung an.

Rahmenbedingungen
Insgesamt sind 19973 Datensätze in der Eingabedatei im Textformat gespeichert. Es sind einige Sätze dabei wo die Kombination Vorname und Nachname nicht eindeutig ist. Als Datenziel habe ich jeweils die Trash Destination verwendet.

Beispiel 1
In der Suche Transformation wird die Tabelle Person.Contact ausgewählt. Im Reiter Spalten müssen die Spalten ausgewählt werden, die die entsprechenden Referenzwerte beinhalten. Unten im Dialog wird die Suchspalte ContactID als neue Spalte NewContactID eingetragen.

Beim Ereignis PreExecute der Datenfluss Transformation, in der sich die Suchfunktion befindet, wird die gesamte Tabelle mit allen Spalten in den Hauptspeicher gelesen. Dies ist das Standardverfahren, das aber geändert werden kann.
Nur keine Angst, es gibt eine Obergrenze, die Sie einstellen können. Mehr dazu später im Text.
Bei kleineren Tabellen, in unserem Beispiel nicht einmal 20.000 Zeilen, dürfte das kein Problem sein. Wird die Verarbeitung gestartet so können Sie sehen, dass die Suchfunktion sehr schnell arbeitet. Es werden keine Datensätze ausgesteuert, die nicht gefunden werden.





Beispiel 2
Als erste Verbesserung werden jetzt nur die Spalten aus der Tabelle selektiert, die auch wirklich benötigt sind. Es wird nicht mehr einfach die Tabelle referenziert, sondern ein entsprechender SQL Select Befehl geschrieben. Es bringt Ihnen keine Vorteile, wenn Sie eine Order by Bedingung anfügen. Das sind die Spalten FirstName, LastName und die ContactID. Dadurch wird verhindert, dass unnötig Hauptspeicher verbraucht wird.

Zusätzlich ist in dem Datenfluss die Bedingtes Teilen (Conditional Split) nach der Suche Transformation eingefügt. Mit der Bedingtes Teilen Transformation wird der Datenstrom aufgeteilt.

Diejenigen Zeilen, bei denen die gelieferte mit der ermittelten ContactID übereinstimmt gehen nach Übereinstimmung. Die Datensätze bei denen keine Übereinstimmung vorhanden ist, werden in den Default Output geschoben. Bei 456 Datensätzen ist das Suchkriterium First- und LastName nicht eindeutig.

Wird ein Daten Viewer verwendet, so fällt auf, dass es undefiniert ist, welche ContactID verwendet wird. Im Verarbeitungsbericht, der unter Status in der Entwicklungsumgebung angezeigt wird, wird von Integration Services ein entsprechender Hinweis ausgegeben, dass doppelte Verweisschlüssel gefunden wurden. Da es sich nur um eine Warnung handelt, bricht die Task nicht mit einem Fehler ab.
Damit Sie vorhersagbare Ergebnisse erzielen können, müssen Sie sicher stellen, dass nur eindeutige Verweisschlüssel vorkommen können.

Beispiel 3
In diesem und in den folgenden Beispielen werden die doppelten Verweisschlüssel einfach ignoriert. Als Suchdatenbestand wird jetzt die  gespeicherte Prozedur SSISContact verwendet.

Es werden immer noch alle Datensätze, die die gespeicherte Prozedur erzeugt in den Hauptspeicher gelesen. Deshalb darf die gespeicherte Prozedur keine Parameter haben. Das Ergebnis ist genau so, als wenn wir die Tabelle oder über einen SQL Befehl auf die Tabelle zugreifen.
Der Vorteil einer gespeicherten Prozedur als Datenquelle liegt darin, dass auch komplexere SQL Befehle zentral auf dem Server abgelegt werden können. Mehrere SSIS Pakete könnten diese gespeicherte Prozedur verwenden.

Beispiel 4
In diesem Beispiel wird jetzt mit den beiden Spalten UFirstName und ULastName aus der Eingabedatei in der Tabelle gesucht. Alle Zeichen in diesen beiden Spalten enthalten ausschließlich Grossbuchstaben.

Für keinen einzigen Datensatz wird jetzt die ContactID gefunden.

Die Suche Transformation berücksichtigt immer die Groß- und Kleinschreibung von Textfeldern. Es gibt keine Möglichkeit, dieses Verhalten zu ändern.

Beispiel 5
In diesem Beispiel werden vorsorglich alle Suchspalten mit der Transformation Datenkonvertierung (Character Map) in Großbuchstaben umgewandelt. Zusätzlich werden alle Suchspalten in der Suchtabelle temporär in Großbuchstaben umgewandelt. Damit ist sichergestellt, dass es keine Unterschiede zwischen Groß- und Kleinbuchstaben im Suchstring mehr gibt.

Beim ausführen werden jetzt alle Datensätze wieder gefunden.
In der Datenkonvertierung gibt es eine Option Linguistische Schreibweise die nur mit Klein- und Großbuchstaben zusammen ausgewählt werden kann. Leider bringt diese Option kein sichtbares Ergebnis.

Beispiel 6
Im letzten Beispiel wird eine gespeicherte Prozedur unter dem Reiter Erweitert eingesetzt.

In diesem Reiter können wir sehr fein bestimmen, wie groß der Zwischenspeicher werden darf. Üblicherweise wird dieser Zwischenspeicher satzweise gefüllt bis die angegebene Größe im Hauptspeicher erreicht ist. Danach wird der Satz, der am längsten nicht mehr referenziert wurde aus dem Speicher entfernt und durch einen neuen Satz ersetzt.
Wenn die Datensätze, wie in unserem Beispiel fast ausnahmslos unterschiedliche Referenzwerte suchen, wird die Laufzeit der Verarbeitung dadurch deutlich langsamer als wenn die ganze Tabelle in den Speicher geladen wird. Auf meinen Rechner braucht die Verarbeitung der Beispieldaten mit dieser Technik ungefähr 5 Minuten. Werden die beiden Suchspalten mit einem Index versehen, geht die Verarbeitung natürlich wesentlich schneller. Doch üblicherweise werden wesentlich mehr Sätze aus der Eingabe gegen eine kleinere Suchtabelle verglichen.
Es gibt in der Praxis allerdings Fälle, wo das Suchen des entsprechenden Datensatzes sehr komplex sein kann. Damit diese komplexe Logik überhaupt verwendet werden kann, wird hier eine gespeicherte Prozedur eingesetzt. Dieses Verfahren ist zwar langsamer, aber damit werden bestimmte Verarbeitungen erst möglich.
Damit diese gespeicherte Prozedur verwendet werden kann müssen folgende Bedingungen erfüllt werden:

  1. Zuerst muss im Reiter Verweistabelle eine Abfrage definiert werden, die genau die gleichen Metadaten liefert, wie die gespeicherte Prozedur. Allerdings wird diese Abfrage nicht zur Laufzeit ausgeführt; sie dient nur dafür, dass wir überhaupt die gespeicherte Prozedur mit Parameter versehen können.
  2. Die Spalten werden wie bisher im Reiter Spalten zugewiesen.
  3. Im Reiter Erweitert kann die gespeicherte Prozedur eingegeben werden. Die Parameter werden mit ? (Fragezeichen) von links nach rechts zugeordnet.

An dieser Stelle ist es nicht möglich, dass folgender syntaktisch einwandfreier Aufruf eingegeben wird:

[AdventureWorks].[dbo].[SSISContact2]

Unter den Parameter sehen Sie die Zuordnung der entsprechenden Felder im Datenstrom. Die Datentypen der Parameter müssen genau stimmen. Die Fragezeichen im Aufruf werden von links nach rechts durchnumeriert, beginnend mit Parameter0.

Zusammenfassung

Die Suche Transformationen ist in der Regel sehr einfach zu verwenden und meistens auch ausreichend schnell.

Die Suche Transformation erfordert immer eine OLE DB Verbindung zu einer Datenquelle. Kann zu einer Datenquelle keine OLE DB Verbindung hergestellt werden, so kann diese in der Suche Transformation nicht verwendet werden.

Beim Suchen von Textfeldern wird immer zwischen Groß- und Kleinbuchstaben unterschieden. Der Inhalt des Datentyp CHAR ist nie gleich mit dem Datentyp VARCHAR.

Es können gespeicherte Prozeduren eingesetzt werden.

Standardmäßig wird maximal 5 Megabyte Hauptspeicher als Cache auf Rechnern mit einem 32-Bit Betriebssystem verwendet. Wird ein 64-Bit Betriebssystem verwendet, so sind es standardmäßig 25 Megabyte. Diese Werte können über die Eigenschaften verändert werden

Es ist möglich, dass auch Tabellen mit einem größeren Speicherbedarf komplett in den Speicher geladen werden. Unter den Eigenschaften finden Sie die Eigenschaft CacheType der die drei Werte

  • Kein - es wird kein Cache genutzt; jeder Wert erfordert einen Zugriff auf die Datenquelle
  • Teilweise - der Cache wird mit jedem Zugriff bis zur definierten Obergrenze aufgebaut-
  • Vollständig - das ist der Default Wert

annehmen kann. Diese Einstellungen müssen immer im Zusammenhang mit der Speichergrenze gesehen werden.

Komponentenindex:

  • Task SQL ausführen
  • Sequenzcontainer
  • Datenflusstask
  • Flatfilequelle
  • Ausdruckssuche Transformation
  • Bedingtes Teilen Transformation
  • Zeichenzuordnung Transformation
  • Trash Destination
     

geändert am 21.7.2006