SSIS: Tabellen-Variablen als Datenquelle

In SSIS kann man als Datenquelle ein SQL-Statement eintragen:

Eingabefeld für SQL als Datenquelle

Manchmal möchte man in dieser Maske kompliziertere Berechnungen verwenden. Dafür kann man T-SQL (wie bei der Programmierung von Stored Procedures) verwenden. Allerdings funktioniert es nicht ganz wie gedacht:

Als erstes kann man keine temporären Tabellen (beginnend mit #) verwenden. Als Beispiel betrachten wir das SQL-Statement:

select 1 as zahl ,2 as doppelte into #t
select * from #t

Dieses einfache Statement liefert im SQL Server Management Studio – wie erwartet:

(1 Zeile(n) betroffen)
zahl        doppelte
———– ———–
1           2

(1 Zeile(n) betroffen)

Fügt man dieses Statement aber in der SSIS-Datenquelle hinzu, erhält man nach OK folgenden Fehler:

Fehler beim Vorbereiten: #t unbekannt

bzw. als Text:

Fehler bei Datenflusstask [T-SQL Programm [1]]: SSIS-Fehlercode ‚DTS_E_OLEDBERROR‘. OLE DB-Fehler. Fehlercode: 0x80040E14.
Ein OLE DB-Datensatz ist verfügbar. Quelle: ‚Microsoft SQL Server Native Client 10.0‘ HRESULT: 0x80040E14 Beschreibung: ‚Anweisung(en) konnte(n) nicht vorbereitet werden.‘.
Ein OLE DB-Datensatz ist verfügbar. Quelle: ‚Microsoft SQL Server Native Client 10.0‘ HRESULT: 0x80040E14 Beschreibung: ‚Ungültiger Objektname ‚#t‘.‘.

Auch eine verspätete Überprüfung des SQLs bringt keine Besserung.

Allerdings funktioniert es, Tabellen-Variablen zu verwenden. Dort heißt obiger Code entsprechend:

Declare @t as TABLE (zahl integer, doppelte integer)
insert into @t select 1 ,2
select * from @t

Dieser Code lässt sich als Datenquelle eintragen und SSIS erkennt korrekt die beiden Spalten:

Spalten korrekt erkannt

Startet man das SSIS-Paket (damit man etwas sieht, habe ich ein UNION ALL dahinter gehängt), scheint es zu funktionieren:

Datenquelle zu UNION ALL

Alles ist grün – wunderbar.

Aber nein! Es wurde keine Zeile übertragen.

Das ist ein bekanntes Problem, dass in dem INSERT-Statement bereits ein Dataset erzeugt wird – dem entspricht in obiger Ausführung der erste Text “(1 Zeile(n) betroffen)”. Die Daten des zweiten Befehls werden dann gar nicht mehr im SSIS weiterverwendet!

Also muss man das SQL so abwandeln:

set nocount on
Declare @t as TABLE (zahl integer, doppelte integer)
insert into @t select 1 ,2
select * from @t

Durch das “set nocount on” wird das erste Dataset unterdrückt, so dass es jetzt funktioniert:

image

Wir sehen, es wurde tatsächlich eine Zeile übertragen – und die Daten stimmen auch, wie der Data Viewer zeigt.