• Artikel

Arbeiten mit Parametern und Rückgabecodes im Task „SQL ausführen“

SQL-Anweisungen und gespeicherte Prozeduren verwenden häufig input-Parameter, output-Parameter und Rückgabecodes. In Integration Services unterstützt der Task „SQL ausführen“ die Parametertypen Input, Output und ReturnValue. Sie können den Input-Typ für Eingabeparameter, den Output-Typ für Ausgabeparameter und den ReturnValue-Typ für Rückgabecodes verwenden.

HinweisHinweis

Parameter können in einem Task SQL ausführen nur verwendet werden, wenn dies vom Datenanbieter unterstützt wird.

Parameter in SQL-Befehlen, einschließlich Abfragen und gespeicherte Prozeduren, werden benutzerdefinierten Variablen zugeordnet, die im Bereich des Tasks SQL ausführen, eines übergeordneten Containers oder im Bereich des Pakets erstellt werden. Die Werte für Variablen können zur Entwurfszeit festgelegt oder zur Laufzeit dynamisch aufgefüllt werden. Sie können Parameter auch Systemvariablen zuordnen. Weitere Informationen finden Sie unter SQL Server Integration Services-Variablen und Systemvariablen.

Das Arbeiten mit Parametern und Rückgabecodes in einem Task „SQL ausführen“ bedeutet jedoch mehr, als nur zu wissen, welche Parametertypen der Task unterstützt und wie diese Parameter zugeordnet werden. Es müssen weitere Benutzungsanforderungen und Richtlinien beachtet werden, um Parameter und Rückgabecodes erfolgreich in einem Task „SQL ausführen“ zu verwenden. Diese Benutzungsanforderungen und Richtlinien werden am Ende dieses Themas behandelt:

  • Verwenden von Parametern, Namen und Markern

  • Verwenden von Parametern mit Datums- und Zeitdatentypen

  • Verwenden von Parametern in WHERE-Klauseln

  • Verwenden von Parametern mit gespeicherten Prozeduren

  • Abrufen von Werten von Rückgabecodes

  • Konfigurieren von Parametern und Rückgabecodes im Editor für den Task „SQL ausführen“

Verwenden von Parameternamen und Markern

Die Syntax des SQL-Befehls verwendet verschiedene Parametermarkierungen, je nach verwendetem Verbindungstyp im Task SQL ausführen. Beispielsweise erfordert der ADO.NET-Verbindungs-Managertyp, dass der SQL-Befehl eine Parametermarkierung im Format @varParameter verwendet, während der OLE DB-Verbindungstyp das Fragezeichen ("?") als Parametermarkierung erfordert.

Die Namen, die in den Zuordnungen zwischen Variablen und Parametern als Parameternamen verwendet werden können, variieren ebenfalls je nach Managertyp. Beispielsweise verwendet der ADO.NET-Verbindungs-Managertyp einen benutzerdefinierten Namen mit einem @-Präfix, während der OLE DB-Verbindungs-Managertyp die Verwendung des numerischen Wertes einer 0-basierten Ordnungszahl als Parameternamen erfordert.

In der folgenden Tabelle finden Sie eine Auflistung der Anforderungen für SQL-Befehle für Verbindungs-Managertypen, die der Task SQL ausführen verwenden kann.

Verbindungstyp

Parametermarkierung

Parametername

Beispiel SQL-Befehl

ADO

?

Param1, Param2, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET

@<Parametername>

@<Parametername>

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

ODBC

?

1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

EXCEL und OLE DB

?

0, 1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

Verwenden von Parametern mit ADO.NET- und ADO-Verbindungs-Managern

ADO.NET- und ADO-Verbindungs-Manager besitzen besondere Anforderungen für SQ-Befehle, die Parameter verwenden:

  • ADO.NET-Verbindungs-Manager erfordern die Verwendung von Parameternamen als Parametermarker in SQL-Befehlen. Das bedeutet, dass Variablen Parametern direkt zugeordnet werden können. Beispielsweise wird die @varName-Variable dem Parameter mit der Bezeichnung @parName zugeordnet. Auf diese Weise wird dem @parName-Parameter ein Wert bereitgestellt.

  • ADO-Verbindungs-Manager erfordern die Verwendung von Fragezeichen ("?") als Parametermarkierung in SQL-Befehlen. Sie können jedoch einen beliebigen benutzerdefinierten Namen, mit Ausnahme von ganzzahligen Werten, als Parameternamen verwenden.

Variablen werden Parameternamen zugeordnet, um Parametern Werte bereitzustellen. Anschließend verwendet der Task „SQL ausführen“ den Ordinalwert des Parameternamens in der Parameterliste, um die Variablenwerte in Parametern zu laden.

Verwenden von Parametern mit EXCEL-, ODBC- und OLE DB-Verbindungs-Managern

EXCEL-, ODBC- und OLE DB-Verbindungs-Manager erfordern die Verwendung von Fragezeichen ("?") als Parametermarkierungen in SQL-Befehlen sowie die Verwendung 0-basierter bzw. 1-basierter numerischer Werte als Parameternamen. Wenn der Task „SQL ausführen“ den ODBC-Verbindungs-Manager verwendet, wird der Parametername, der dem ersten Parameter in der Abfrage zugeordnet wird, mit 1 bezeichnet, andernfalls wird er mit 0 bezeichnet. Für nachfolgende Parameter gibt der numerische Wert des Parameternamens den Parameter in dem SQL-Befehl an, dem der Parametername zugeordnet wird. Beispielsweise wird der Parametername mit der Bezeichnung "3" dem dritten Parameter zugeordnet, der im SQL-Befehl durch das dritte Fragezeichen ("?") dargestellt wird.

Um Werte für Parameter bereitzustellen, werden den Parameternamen Variablen zugeordnet. Der Task SQL ausführen verwendet dann den Ordinalwert des Parameternamens, um die Variablenwerte in Parametern zu laden.

Einige OLE DB-Datentypen werden, abhängig vom Anbieter, den der Verbindungs-Manager verwendet, nicht unterstützt. Beispielsweise erkennt der Excel-Treiber nur einen begrenzten Satz von Datentypen. Weitere Informationen zum Verhalten des Jet-Anbieters mit dem Excel-Treiber finden Sie unter Excel-Quelle.

Verwenden von Parametern mit OLE DB-Verbindungs-Managern

Wenn der Task „SQL ausführen“ den OLE DB-Verbindungs-Manager verwendet, ist die BypassPrepare-Eigenschaft des Tasks verfügbar. Sie sollten für diese Eigenschaft true festlegen, wenn der Task „SQL ausführen“ SQL-Anweisungen mit Parametern verwendet.

Bei Verwendung eines OLE DB-Verbindungs-Managers können Sie keine parametrisierten Unterabfragen verwenden, da der Task „SQL ausführen“ keine Parameterinformationen über den OLE DB-Anbieter ableiten kann. Sie können jedoch einen Ausdruck verwenden, um die Parameterwerte in der Abfragezeichenfolge zu verketten und die SqlStatementSource-Eigenschaft des Tasks festzulegen.

Verwenden von Parametern mit Datums- und Zeitdatentypen

Verwenden von Datums- und Zeitparametern mit ADO.NET- und ADO-Verbindungs-Managern

Beim Lesen von Daten der SQL Server-Typen time und datetimeoffset gelten für einen Task „SQL ausführen“, der einen ADO.NET- oder ADO-Verbindungs-Manager verwendet, folgende zusätzliche Anforderungen:

  • Bei time-Daten wird vom ADO.NET-Verbindungs-Manager gefordert, dass diese Daten in einem Parameter gespeichert werden, dessen Parametertyp Input oder Output und dessen Datentyp string lautet.

  • Für datetimeoffset-Daten verlangt ein ADO.NET-Verbindungs-Manager, dass diese Daten in einem der folgenden Parameter gespeichert werden:

    • Ein Parameter mit dem Parametertyp Input und dem Datentyp string.

    • Ein Parameter mit dem Parametertyp Output oder ReturnValue und dem Datentyp datetimeoffset, string oder datetime2. Wenn Sie einen Parameter auswählen, dessen Datentyp string oder datetime2 ist, werden die Daten von Integration Services in den Datentyp string bzw. datetime2 konvertiert.

  • Für einen ADO-Verbindungs-Manager ist es erforderlich, dass time-Daten oder datetimeoffset-Daten in einem Parameter mit dem Parametertyp Input oder Output und dem Datentyp adVarWchar gespeichert werden.

Weitere Informationen zu SQL Server-Datentypen und deren Zuordnung zu Integration Services-Datentypen finden Sie unter Datentypen (Transact-SQL) und SQL Server Integration Services-Datentypen.

Verwenden von Datums- und Zeitparametern mit dem OLE DB-Verbindungs-Manager

Bei der Verwendung eines OLE DB-Verbindungs-Managers besitzt ein Task „SQL ausführen“ bestimmte Speicheranforderungen für Daten mit den SQL Server-Datentypen date, time, datetime, datetime2 und datetimeoffset. Sie müssen diese Daten in einem der folgenden Parametertypen speichern:

  • In einem Eingabeparameter mit dem Datentyp NVARCHAR

  • In einem Ausgabeparameter mit dem entsprechenden Datentyp, wie in der folgenden Tabelle aufgeführt

    Parametertyp Output

    Date-Datumstyp

    DBDATE

    date

    DBTIME2

    time

    DBTIMESTAMP

    datetime, datetime2

    DBTIMESTAMPOFFSET

    datetimeoffset

Wenn die Daten nicht im entsprechenden Eingabe- oder Ausgabeparameter gespeichert werden, erzeugt das Paket einen Fehler.

Verwenden von Datums- und Zeitparametern mit dem ODBC-Verbindungs-Manager

Bei der Verwendung eines ODBC-Verbindungs-Managers besitzt ein Task „SQL ausführen“ bestimmte Speicheranforderungen für Daten mit den SQL Server-Datentypen date, time, datetime, datetime2 oder datetimeoffset. Sie müssen diese Daten in einem der folgenden Parametertypen speichern:

  • Ein input-Parameter mit dem Datentyp SQL_WVARCHAR

  • Ein output-Parameter mit dem entsprechenden Datentyp, wie in der folgenden Tabelle aufgeführt

    Parametertyp Output

    Date-Datumstyp

    SQL_DATE

    date

    SQL_SS_TIME2

    time

    SQL_TYPE_TIMESTAMP

    -Oder-

    SQL_TIMESTAMP

    datetime, datetime2

    SQL_SS_TIMESTAMPOFFSET

    datetimeoffset

Wenn die Daten nicht im entsprechenden Eingabe- oder Ausgabeparameter gespeichert werden, erzeugt das Paket einen Fehler.

Verwenden von Parametern in WHERE-Klauseln

In SELECT-, INSERT-, UPDATE- und DELETE-Befehlen sind häufig WHERE-Klauseln enthalten, um Filter anzugeben, die die Bedingungen definieren, die die Zeilen in den Quelltabellen für einen SQL-Befehl erfüllen müssen. Parameter stellen die Filterwerte in den WHERE-Klauseln bereit.

Sie können Parametermarkierungen verwenden, um Parameterwerte dynamisch bereitzustellen. Die Regeln für die in der SQL-Anweisung zu verwendenden Parametermarkierungen und Parameternamen hängen vom Typ des Verbindungs-Managers ab, den der Task SQL ausführen verwendet.

In der folgenden Tabelle finden Sie eine Auflistung von Beispielen des SELECT-Befehls nach verschiedenen Verbindungs-Managertypen. Die INSERT-, UPDATE- und DELETE-Anweisungen ähneln sich. In den Beispielen wird die SELECT-Anweisung verwendet, um Produkte aus der Product-Tabelle in AdventureWorks zurückzugeben, die eine ProductID aufweisen, die größer oder kleiner als die angegebenen Werte von zwei Parametern ist.

Verbindungstyp

SELECT-Syntax

EXCEL, ODBC und OLEDB

SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?

ADO

SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?

ADO.NET

SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

In den Beispielen werden Parameter mit den folgenden Namen benötigt:

  • Die EXCEL- und OLED DB-Verbindungs-Manager verwenden die Parameternamen 0 und 1. Der ODBC-Verbindungstyp verwendet die Namen 1 und 2.

  • Der ADO-Verbindungstyp kann zwei beliebige Parameternamen verwenden, wie z. B. Param1 und Param2, deren Zuordnung muss aber nach ihrer Ordnungsposition in der Parameterliste erfolgen.

  • Der ADO.NET-Verbindungstyp verwendet die Parameternamen @parmMinProductID und @parmMaxProductID.

Verwenden von Parametern mit gespeicherten Prozeduren

Für SQL-Befehle, die gespeicherte Prozeduren ausführen, kann die Parameterzuordnung ebenfalls verwendet werden. Die Regeln für die zu verwendenden Parametermarkierungen und Parameternamen hängen, wie die Regeln für parametrisierte Abfragen, vom Typ des Verbindungs-Managers ab, den der Task SQL ausführen verwendet.

In der folgenden Tabelle finden Sie eine Auflistung von Beispielen des EXEC-Befehls nach verschiedenen Verbindungs-Managertypen. Die Beispiele führen die gespeicherte Prozedur uspGetBillOfMaterials in AdventureWorks aus. Die gespeicherte Prozedur verwendet die @StartProductID sowie den @CheckDate-Parameter und den input-Eingabeparameter.

Verbindungstyp

EXEC-Syntax

EXCEL und OLEDB

EXEC uspGetBillOfMaterials ?, ?

ODBC

{call uspGetBillOfMaterials(?, ?)}

Weitere Informationen über die ODBC-Aufrufsyntax finden Sie im Thema Prozedurparameter in der ODBC Programmer's Reference in der MSDN Library.

ADO

Wenn IsQueryStoredProcedure auf False festgelegt ist, EXEC uspGetBillOfMaterials ?, ?

Wenn IsQueryStoredProcedure auf True festgelegt ist, uspGetBillOfMaterials

ADO.NET

Wenn IsQueryStoredProcedure auf False festgelegt ist, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

Wenn IsQueryStoredProcedure auf True festgelegt ist, uspGetBillOfMaterials

Um Ausgabeparameter verwenden zu können, erfordert die Syntax die Verwendung des OUTPUT-Schlüsselworts am Ende jeder Parametermarkierung. Zum Beispiel ist die folgende Ausgabeparametersyntax richtig: EXEC myStoredProcedure ? OUTPUT.

Weitere Informationen zum Verwenden von Eingabe- und Ausgabeparametern mit gespeicherten Prozeduren von Transact-SQL finden Sie unter Parameter (Datenbankmodul), Zurückgeben von Daten mithilfe von OUTPUT-Parametern und EXECUTE (Transact-SQL).

Abrufen von Werten von Rückgabecodes

Eine gespeicherte Prozedur kann einen ganzzahligen Wert, der als Rückgabecode bezeichnet wird, zurückgeben, um den Ausführungsstatus einer Prozedur anzuzeigen. Verwenden Sie Parameter des ReturnValue-Typs, um Rückgabecodes im Task SQL ausführen zu implementieren.

In der folgenden Tabelle finden Sie eine Auflistung einiger Beispiele des EXEC-Befehls nach verschiedenen Verbindungstypen, die Rückgabecodes implementieren. In alle Beispiele wird ein input-Parameter verwendet. Die Regeln für die Verwendung von Parametermarkierungen und Parameternamen sind für alle Parametertypen, Input, Output und ReturnValue, gleich.

Ein Teil der Syntax unterstützt keine Parameterliterale. In diesem Fall müssen Sie die Parameterwerte mithilfe einer Variablen bereitstellen.

Verbindungstyp

EXEC-Syntax

EXCEL und OLEDB

EXEC ? = myStoredProcedure 1

ODBC

{? = call myStoredProcedure(1)}

Weitere Informationen über die ODBC-Aufrufsyntax finden Sie im Thema Prozedurparameter in der ODBC Programmer's Reference in der MSDN Library.

ADO

Wenn IsQueryStoreProcedure auf False festgelegt ist, EXEC ? = myStoredProcedure 1

Wenn IsQueryStoreProcedure auf True festgelegt ist, myStoredProcedure

ADO.NET

Wenn IsQueryStoreProcedure auf True festgelegt ist.

myStoredProcedure

In der in der obigen Tabelle gezeigten Syntax verwendet der Task "SQL ausführen" zum Ausführen der gespeicherten Prozedur den Quelltyp Direkteingabe. Der Task "SQL ausführen" kann auch den Quelltyp Dateiverbindung verwenden, um eine gespeicherte Prozedur auszuführen. Unabhängig davon, ob der Task "SQL ausführen" den Quelltyp Direkteingabe oder Dateiverbindung verwendet, implementieren Sie den Rückgabecode mit einem Parameter des Typs ReturnValue. Weitere Informationen zum Konfigurieren des Quelltyps für die vom Task "SQL ausführen" ausgeführte SQL-Anweisung unter Editor für den Task 'SQL ausführen' (Seite Allgemein).

Weitere Informationen zum Verwenden von Rückgabecodes mit gespeicherten Prozeduren von Transact-SQL finden Sie unter Zurückgeben von Daten mithilfe eines Rückgabecodes und RETURN (Transact-SQL).

Konfigurieren von Parametern und Rückgabecodes im Task „SQL ausführen“

Klicken Sie auf das folgende Thema, um weitere Informationen zu den Eigenschaften von Parametern und Rückgabecodes zu erhalten, die Sie im SSIS-Designer festlegen können:

Klicken Sie auf das folgende Thema, um weitere Informationen zum Festlegen dieser Eigenschaften im SSIS-Designer zu erhalten:

Externe Ressourcen

Integration Services (kleines Symbol) Bleiben Sie mit Integration Services auf dem neuesten Stand

Die neuesten Downloads, Artikel, Beispiele und Videos von Microsoft sowie ausgewählte Lösungen aus der Community finden Sie auf der Integration Services-Seite von MSDN oder TechNet:

Abonnieren Sie die auf der Seite verfügbaren RSS-Newsfeeds, um automatische Benachrichtigungen zu diesen Aktualisierungen zu erhalten.