Partager via

Paramètres et codes de retour dans la tâche d'exécution SQL

  • Article

Les instructions SQL et les procédures stockées utilisent fréquemment des paramètres de types input, output et des codes de retour. Dans Integration Services, la tâche Exécuter SQL prend en charge les Inputtypes de paramètres , Outputet ReturnValue . Vous utilisez le type Input pour les paramètres d'entrée, Output pour les paramètres de sortie et ReturnValue pour les codes de retour.

Notes

Vous ne pouvez utiliser des paramètres dans une tâche d'exécution SQL que si le fournisseur de données les prend en charge.

Les paramètres des commandes SQL, notamment les requêtes et les procédures stockées, sont mappés à des variables définies par l'utilisateur créées dans l'étendue de la tâche d'exécution SQL, un conteneur parent ou dans l'étendue du package. Les variables peuvent être définies au moment de la conception ou être remplies dynamiquement lors de l'exécution. Vous pouvez également mapper des paramètres à des variables système. Pour plus d’informations, consultez Variables Integration Services (SSIS) et Variables système.

Toutefois, l'utilisation de paramètres et de codes de retour dans une tâche d'exécution SQL ne permet pas uniquement de savoir quels types de paramètres sont pris en charge par la tâche et de quelle manière ces paramètres seront mappés. D'autres indications et spécifications d'utilisation permettent d'utiliser avec succès des paramètres et des codes de retour dans la tâche d'exécution SQL. Le reste de cette rubrique traite de ces indications et spécifications d'utilisation.

Utilisation de marqueurs et de noms de paramètres

Selon le type de connexion que la tâche d'exécution SQL utilise, la syntaxe de la commande SQL utilise différents marqueurs de paramètres. Par exemple, le type de gestionnaire de connexions ADO.NET impose que la commande SQL utilise un marqueur de paramètre au format @varParameter, tandis que le type de connexion OLE DB exige le marqueur de paramètre point d’interrogation (?).

Les noms que vous pouvez utiliser comme noms de paramètres dans les mappages entre variables et paramètres varient également selon le type de gestionnaire de connexions. Par exemple, le type de gestionnaire de connexions ADO.NET utilise un nom défini par l’utilisateur avec le préfixe @, tandis que le type de gestionnaire de connexions OLE DB impose l’utilisation de la valeur numérique d’un ordinal de base 0 comme nom de paramètre.

Le tableau suivant indique les conditions requises des commandes SQL pour les types de gestionnaires de connexions que la tâche d'exécution SQL peut utiliser.

Type de connexion Marqueur de paramètre Nom du paramètre Exemple de commande SQL
ADO ? Param1, Param2, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET @<nom de paramètre> @<nom de paramètre> SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC ? 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL et OLE DB ? 0, 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

Utilisation de paramètres avec les gestionnaires de connexions ADO.NET et ADO

Les gestionnaires de connexions ADO.NET et ADO ont des spécifications particulières pour les commandes SQL qui utilisent des paramètres :

  • Les gestionnaires de connexions ADO.NET exigent que la commande SQL utilise des noms de paramètres comme marqueurs de paramètres. Cela signifie que des variables peuvent être mappées directement à des paramètres. Par exemple, la variable @varName est mappée au paramètre nommé @parName et fournit une valeur au paramètre @parName.

  • Les gestionnaires de connexions ADO.NET imposent que la commande SQL utilise des points d'interrogation (?) comme marqueurs de paramètres. Toutefois, vous pouvez utiliser les noms définis par l'utilisateur, à l'exception des valeurs entières, comme noms de paramètres.

Pour fournir des valeurs aux paramètres, les variables sont mappées à des noms de paramètres. Puis, la tâche d'exécution SQL utilise la valeur ordinale du nom de paramètre dans la liste des paramètres pour charger des valeurs de variables dans des paramètres.

Utilisation de paramètres avec les gestionnaires de connexions EXCEL, ODBC et OLE DB

Les gestionnaires de connexions EXCEL, ODBC et OLE DB imposent que la commande SQL utilise des points d'interrogation (?) comme marqueurs de paramètres et des valeurs numériques de base 0 et de base 1 comme noms de paramètres. Si la tâche d'exécution SQL utilise le gestionnaire de connexions ODBC, le nom de paramètre qui mappe au premier paramètre dans la requête est nommé 1 ; sinon, le paramètre est nommé 0. Pour les paramètres suivants, la valeur numérique du nom de paramètre indique le paramètre dans la commande SQL à laquelle le nom de paramètre mappe. Par exemple, le paramètre nommé 3 est mappé au troisième paramètre, qui est représenté par le troisième point d'interrogation (?) dans la commande SQL.

Pour fournir des valeurs aux paramètres, les variables sont mappées à des noms de paramètres et la tâche d'exécution SQL utilise la valeur ordinale du nom du paramètre pour charger des valeurs de variables dans des paramètres.

Selon le fournisseur que le gestionnaire de connexions utilise, certains types de données OLE DB peuvent ne pas être pris en charge. Par exemple, le pilote Excel ne reconnaît qu'un ensemble limité de types de données. Pour plus d’informations sur le comportement du fournisseur Jet avec le pilote Excel, consultez Source Excel.

Utilisation de paramètres avec les gestionnaires de connexions OLE DB

Quand la tâche d’exécution SQL utilise le gestionnaire de connexions OLE DB, la propriété BypassPrepare de la tâche est disponible. Vous devez définir cette propriété à true si la tâche d'exécution SQL utilise des instructions SQL avec des paramètres.

Lorsque vous utilisez un gestionnaire de connexions OLE DB, vous ne pouvez pas utiliser de sous-requêtes paramétrables, car la tâche d'exécution SQL ne peut pas dériver d'informations de paramètre par le biais du fournisseur OLE DB. Toutefois, vous pouvez utiliser une expression pour concaténer les valeurs des paramètres dans la chaîne de requête et définir la propriété SqlStatementSource de la tâche.

Utilisation de paramètres avec des types de données de date et d’heure

Utilisation de paramètres de date et d'heure avec les gestionnaires de connexions ADO.NET et ADO

Lors de la lecture des données des types time SQL Server et datetimeoffset, une tâche d’exécution SQL qui utilise un gestionnaire de connexions ADO.NET ou ADO a les exigences supplémentaires suivantes :

  • Pour time les données, un gestionnaire de connexions ADO.NET exige que ces données soient stockées dans un paramètre dont le type de paramètre est Input ou Output, et dont le type de données est string.

  • Pour datetimeoffset les données, un gestionnaire de connexions ADO.NET exige que ces données soient stockées dans l’un des paramètres suivants :

    • Un paramètre de type Input et dont le type de données est string.

    • Un paramètre de type Output ou ReturnValue et dont le type de données est datetimeoffset, string ou datetime2. Si vous sélectionnez un paramètre dont le type de données est string ou datetime2, Integration Services convertit les données en chaîne ou datetime2.

  • Un gestionnaire de connexions ADO impose que les données time ou datetimeoffset soient stockées dans un paramètre de type Input ou Output et dont le type de données est adVarWchar.

Pour plus d’informations sur les types de données SQL Server et leur mappage aux types de données Integration Services, consultez Types de données (Transact-SQL) et Types de données Integration Services.

Utilisation de paramètres de date et d'heure avec les gestionnaires de connexions OLE DB

Lors de l’utilisation d’un gestionnaire de connexions OLE DB, une tâche d’exécution SQL a des exigences de stockage spécifiques pour les données des types de données SQL Server, date, timedatetime, datetime2et datetimeoffset. Vous devez stocker ces données dans l'un des types de paramètres suivants :

  • Un paramètre d'entrée doté du type de données NVARCHAR.

  • Un paramètre de sortie doté du type de données approprié, tel que répertorié dans le tableau suivant.

    Type de paramètre Output Type de données Date
    DBDATE date
    DBTIME2 time
    DBTIMESTAMP datetime, datetime2
    DBTIMESTAMPOFFSET datetimeoffset

Si les données ne sont pas stockées dans le paramètre d'entrée ou de sortie approprié, le package échoue.

Utilisation de paramètres de date et d'heure avec les gestionnaires de connexions ODBC

Lors de l’utilisation d’un gestionnaire de connexions ODBC, une tâche d’exécution SQL a des exigences de stockage spécifiques pour les données avec l’un des types de données SQL Server, date, time, datetimedatetime2ou datetimeoffset. Vous devez stocker ces données dans l'un des types de paramètres suivants :

  • Un paramètre de type input doté du type de données SQL_WVARCHAR

  • Un paramètre de type output doté du type de données approprié, tel que répertorié dans le tableau suivant.

    Type de paramètre Output Type de données Date
    SQL_DATE date
    SQL_SS_TIME2 time
    SQL_TYPE_TIMESTAMP

    -ou-

    SQL_TIMESTAMP    		 datetime, datetime2
    SQL_SS_TIMESTAMPOFFSET datetimeoffset

Si les données ne sont pas stockées dans le paramètre d'entrée ou de sortie approprié, le package échoue.

Utilisation de paramètres dans des clauses WHERE

Les commandes SELECT, INSERT, UPDATE et DELETE incluent fréquemment des clauses WHERE pour spécifier des filtres qui définissent les conditions auxquelles chaque ligne des tables sources doit satisfaire pour se qualifier pour une commande SQL. Les paramètres fournissent les valeurs de filtre dans les clauses WHERE.

Vous pouvez utiliser des marqueurs de paramètres pour fournir dynamiquement des valeurs de paramètres. Les règles pour lesquelles des marqueurs de paramètres et des noms de paramètres peuvent être utilisés dans l'instruction SQL varient selon le type de gestionnaire de connexions que la tâche d'exécution SQL utilise.

Le tableau suivant présente des exemples de la commande SELECT par type de gestionnaire de connexions. Les instructions INSERT, UPDATE et DELETE sont similaires. Les exemples utilisent la commande SELECT pour retourner les produits de la table Product dans AdventureWorks2012 qui ont un ProductID supérieur et inférieur aux valeurs spécifiées par deux paramètres.

Type de connexion Syntaxe SELECT
EXCEL, ODBC et 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

Les exemples requièrent des paramètres avec les noms suivants :

  • Les gestionnaires de connexions EXCEL et OLED DB utilisent les noms de paramètres 0 et 1. Le type de connexion ODBC utilise 1 et 2.

  • Le type de connexion ADO peut utiliser deux noms de paramètres (par exemple, Param1 et Param2), mais les paramètres doivent être mappés selon leur position ordinale dans la liste des paramètres.

  • Le type de connexion ADO.NET utilise les noms de paramètres @parmMinProductID et @parmMaxProductID.

Utilisation de paramètres avec des procédures stockées

Les commandes SQL qui exécutent des procédures stockées peuvent également utiliser le mappage de paramètres. Les règles d'utilisation des marqueurs de paramètres et des noms de paramètres varient selon le type de gestionnaire de connexions que la tâche d'exécution SQL utilise, tout comme les règles des requêtes paramétrables.

Le tableau suivant présente des exemples de la commande EXEC par type de gestionnaire de connexions. Les exemples exécutent la procédure stockée uspGetBillOfMaterials dans AdventureWorks2012. La procédure stockée utilise les @StartProductID paramètres et @CheckDateinput .

Type de connexion Syntaxe EXEC
EXCEL et OLEDB EXEC uspGetBillOfMaterials ?, ?
ODBC {call uspGetBillOfMaterials(?, ?)}

Pour plus d’informations sur la syntaxe d’appel ODBC, consultez la rubrique Paramètres de procédure dans le Guide de référence du programmeur ODBC publié dans MSDN Library.
ADO Si IsQueryStoredProcedure a la valeur False, EXEC uspGetBillOfMaterials ?, ?

Si IsQueryStoredProcedure a la valeur True, uspGetBillOfMaterials
ADO.NET Si IsQueryStoredProcedure a la valeur False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

Si IsQueryStoredProcedure a la valeur True, uspGetBillOfMaterials

Pour utiliser des paramètres de sortie, la syntaxe impose que le mot clé OUTPUT suive chaque marqueur de paramètre. Par exemple, la syntaxe de paramètre de sortie suivante est correcte : EXEC myStoredProcedure ? OUTPUT.

Pour plus d’informations sur l’utilisation de paramètres d’entrée et de sortie avec des procédures stockées Transact-SQL, consultez EXECUTE (Transact-SQL).

Obtention de valeurs de codes de retour

Une procédure stockée peut retourner une valeur entière appelée « code de retour » pour indiquer l'état d'exécution d'une procédure. Pour implémenter des codes de retour dans la tâche d'exécution SQL, vous utilisez des paramètres du type ReturnValue.

Le tableau suivant présente par type de connexion des exemples de commandes EXEC qui implémentent des codes de retour. Tous les exemples utilisent un paramètre de type input. Les règles d’utilisation des marqueurs de paramètres et des noms de paramètres sont les mêmes pour tous les types deInput paramètres: , Outputet ReturnValue.

Certaines syntaxes ne prennent pas en charge les littéraux de paramètres. Dans ce cas, vous devez fournir la valeur du paramètre en utilisant une variable.

Type de connexion Syntaxe EXEC
EXCEL et OLEDB EXEC ? = myStoredProcedure 1
ODBC {? = call myStoredProcedure(1)}

Pour plus d’informations sur la syntaxe d’appel ODBC, consultez la rubrique Paramètres de procédure dans le Guide de référence du programmeur ODBC publié dans MSDN Library.
ADO Si IsQueryStoreProcedure a la valeur False, EXEC ? = myStoredProcedure 1

Si IsQueryStoreProcedure a la valeur True, myStoredProcedure
ADO.NET Définir IsQueryStoreProcedure a la valeur True.

myStoredProcedure

Dans la syntaxe affichée dans la table précédente, la tâche d’exécution SQL utilise le type de source Entrée directe pour exécuter la procédure stockée. La tâche d’exécution SQL peut aussi utiliser le type de source Connexion de fichiers pour exécuter une procédure stockée. Que la tâche d’exécution SQL utilise le type source Entrée directe ou Connexion de fichier , utilisez un paramètre du ReturnValue type pour implémenter le code de retour. Pour plus d’informations sur la configuration du type source de l’instruction SQL exécutée par la tâche Execute SQL, consultez Exécuter l’Éditeur de tâche SQL (page Général).

Pour plus d’informations sur l’utilisation de codes de retour avec des procédures stockées Transact-SQL, consultez RETURN (Transact-SQL).

Configuration de paramètres et de codes de retour dans la tâche d'exécution SQL

Pour plus d’informations sur les propriétés des paramètres et des codes de retour que vous pouvez définir dans SSIS Designer, cliquez sur la rubrique suivante :

Pour plus d'informations sur la définition de ces propriétés dans le concepteur SSIS , cliquez sur la rubrique suivante :

Définir les propriétés d'une tâche ou d'un conteneur

Contenu associé

Voir aussi

Tache d’exécution de requêtes SQL
Ensembles de résultats dans la tâche d’exécution SQL