Ein Resultset mit dem Foreach-Schleifencontainer zerlegen

Die Task Foreach-Schleifencontainer (Foreach-Loop Task) kann dazu verwendet werden, um alle Zeilen in einem Resultset einzeln zu verarbeiten.

Wenn Sie eine Datenbank vom SQL Server 7.0 oder 2000 auf den SQL Server 2005 migrieren sollten Sie nach der Migration die Statistiken aktualisieren.

In dem Beispielpaket werden für alle Benutzerdatenbanken, also nicht die Master, Model, TempDB und MSDB, die Statistiken mit sp_updatestats aktualisiert.
Zusätzlich wird für jede Datenbank der Befehl DBCC UPDATEUSAGE ausgeführt.

Dies sollte nach der Migration von SQL Server 7.0 oder 2000 einmal für jede Datenbank ausgeführt werden.

Überblick
Zuerst wird mit Hilfe einer SQL ausführen Task ein Resultset mit allen Namen der Benutzerdatenbanken gefüllt. Dieses Resultset wird dann mit Hilfe des Foreach-Schleifencontainers in einzelne Zeilen zerlegt. Im Container baut eine Scripttask die entsprechenden SQL Befehle zusammen die dann anschließend mit einer SQL ausführen Task ausgeführt werden.

Bitte beachten Sie, dass bei größeren Datenbanken die beiden Befehle einige Zeit und Ressourcen in Anspruch nehmen. Außerdem werden Änderungen an den Statistiken in der Datenbank ausgeführt.

Deshalb sollten Sie das Paket nicht in Ihrer produktiven Umgebung ohne Tests ausführen.
Für Probleme in Ihrer produktiven Umgebung kann ich nicht haften.

Das entsprechende Demopaket finden Sie hier.

Die einzelnen Schritte:

Schritt 1: Variablen definieren
Erstellen Sie bitte eine Variable mit der Sichtbarkeit für das ganzes Paket mit dem Namen rsAllUserDB. Das erreichen Sie dadurch, dass Sie die Arbeitsfläche auswählen und dann die Variable definieren. Es darf keine Task oder kein Container explizit ausgewählt sein. Als Datentyp wird Object ausgewählt.

Schritt 2: Task SQL ausführen
Nachdem ein OLE DB Verbindungs-Manager für den entsprechenden Server erstellt wurde, kann dieser in der Task Sql ausführen bei Connection ausgewählt werden. Selbstverständlich können Sie auch einen Verbindungs-Manager für ADO.Net erstellen und diesen verwenden. In diesem Fall muss allerdings der ConnectionType auf ADO.Net umgestellt werden.
Unter SQLStatement wird der entsprechende SQL Befehl eingegeben. Für das Beispiel wird
select [name] as DatabaseName from sys.databases where database_id > 4
verwendet.
Damit das Resultset weiter verwendet werden kann, muss die Eigenschaft ResultSet auf Vollständiges Resultset umgestellt werden.



Da keine Parameter für den SQL-Befehl verwendet werden, kann der Punkt Parameterzuordnung übersprungen werden.

Unter dem Punkt Resultset wird unter Ergebnisname die Ziffer 0 eingetragen.

Beim Punkt Variablenname wird der Name der Variable Benutzer::rsAllUserDB eingetragen oder ausgewählt. Diese Variable wurde im Schritt 1 definiert.

Falls die Variable noch nicht definiert wurde, so kann Sie auch hier direkt definiert werden. Bitte bedachten Sie dabei, dass der Container (Sichtbarkeitsbereich) das ganze Paket ist. Dies wird standardmäßig vorgeblendet.

Nach dem Ausführen der Task SQL ausführen enthält diese Variable das komplette Resultset.

Schritt 3: Foreach-Schleifencontainer
Der Foreach-Schleifencontainer wird in die Ablaufsteuerung gezogen. Der grüne Verbinder wird von der Task SQL ausführen zum Foreach-Schleifencontainer gezogen. So wird eine feste Reihenfolge zwischen den Tasks erreicht.

Damit die Werte jeder Spalte und Zeile in eine Variable gespeichert werden kann, werden zusätzlich Variablen benötigt. Dazu wird der Foreach-Schleifencontainer ausgewählt und über das Menü SSIS - Variablen ausgeführt. Jetzt können die Variablen definiert werden, die allerdings nur einen Sichtbarkeitsbereich innerhalb des Foreach-Schleifencontainers haben.

Es ist auch möglich, die Variablen direkt im Foreach-Schleifencontainer zu definieren.
In diesem Fall ist der Defaultwert für die Sichtbarkeit (Container) - sichtbar innerhalb des ganzen Pakets - leider nicht optimal eingestellt. Besser ist, die Sichtbarkeit auf den Foreach-Schleifencontainer zu begrenzen.

Im Beispiel werden zwei Variablen vom Typ String definiert. Die erste Spalte DatabaseName bekommt den Index 0, für jede weitere Spalte wird der Index um eins erhöht. Falls es notwendig ist, kann der Wert einer Spalte mehr als einer Variablen zugewiesen werden.



Schritt 4: Variablen definieren
Eine Variable vom Typ String und dem Sichtbarkeitsbereich Foreach-Schleifencontainer mit dem Namen SQL wird erzeugt.


 

Schritt 5: Scripttask
Die Aufgabe dieser Scripttask ist es, mit Hilfe der Werte in den Variablen zwei gültige SQL Befehle zu erstellen.
Unter dem Punkt Script ReadOnlyVariables wird die Variable DatabaseName an die Scriptfunktion übergeben.

Wenn der Variablenname eindeutig im Sichtbarkeitsbereich ist, ist es nicht notwendig den Namespace Benutzer mit anzugeben.

Zusätzlich wird die Systemvariable InteractiveMode and die Funktion übergeben.

Unter dem Punkt ReadWriteVariables wird die Variable SQL eingetragen, die beim Schritt 4 erzeugt wurde.

Wird die Schaltfläche Script entwerfen betätigt, so wird die Entwicklungsumgebung für VB.Net geöffnet.

Als Besonderheit möchte ich Sie darauf hinweisen, dass in Abhängigkeit des Wertes der Variable InteractiveMode eine Messagebox angezeigt wird. Die Systemvariable InteractiveMode ist immer TRUE wenn das SSIS Paket im SQL Server Business Intelligence Developer Studio (BIDS), sprich im Debugmode, ausgeführt wird. Starten Sie das Paket über DTExex, DTExecUI oder mit Steuerung F5 im BIDS, so ist diese Systemvariable immer FALSE. So kann ganz einfach Debugcode in die Funktionalität der Funktion eingebaut werden, der die Ausführung nicht stört.

In der Funktion wird einfach mit Hilfe der Variable DatabaseName die SQL Anweisung zusammengebaut und in die Variable SQL gespeichert.

Schritt 6: Task SQL ausführen
Als letzte Task wird nochmals eine SQL ausführen Task in den Foreach-Schleifencontainer gezogen. Auch hier wird der grüne Pfeil von der Scripttask zur SQL ausführen Task gezogen.

Unter dem Punkt Allgemein wird unter Connection der Verbindungs-Manager eingetragen. Als SQLSourceType wird der Wert Variable ausgewählt. Bei Punkt SourceVariable wird die Variable Benutzer::SQL ausgewählt.

Zusammenfassung
Mit diesem einfachen Beispiel können Sie die notwendigen Verarbeitungsschritt nachvollziehen, die notwendig sind, wenn eine Resultset in der Ablaufsteuerung zeilenweise zerlegt und verarbeitet werden soll.

Hier finden Sie das entsprechende Demopaket.

Bitte passen Sie den Verbindungs-Manager an Ihre Umgebung an.

Komponentenindex:

• Foreach-Schleifencontainer
• Script Task
• SQL ausführen Task

erstellt am 16.4.2006