SSIS – Procédures T-SQL – OUTPUT
SQL Server by Romain CASTERES
| Lu 2649 fois
le 24 juin 2011 à 13h22
Lorsque l’on utilise une procédure dans SSIS, il est souvent intéressant d’avoir un retour de celle-ci : un code d’erreur, le nombre de lignes traitées, l’identifiant de la ligne…
Prenons un exemple : Je souhaite à l’aide d’une connexion OLE DB, alimenter une table avec une procédure stockée et connaître le nombre de lignes qu’a insérées ma procédure à la fin de l’exécution de celle ci.
Ma procédure stockée :
CREATE PROCEDURE dbo.PRC_ALIM_PAIEMENTS(
@IdDemande INT, –Identifiant de la demande
@DateDebut VARCHAR(50), –Date de création de la demande
@DateFin VARCHAR(50), –Date de fin de traitement de la demande
@BaseGnVars VARCHAR(30),
[…]
@NumLignesOUT VARCHAR(30) OUTPUT –Nombre de lignes insérées dans ma table
) AS
BEGIN
BEGIN TRY
BEGIN TRAN
[…]
COMMIT TRAN
SET @NumLignesOUT=@NUMCOUNT
END TRY
BEGIN CATCH –Gestion en cas d’erreur
SELECT @ErrMsg = ERROR_MESSAGE()
IF @@TRANCOUNT>0
ROLLBACK TRAN
RAISERROR (@ErrMsg, 16, 1)
END CATCH
END
Voici comment récupérer l’information dans SSIS :
1 – Dans un flux de contrôle : Utilisation du composant « Tache d’exécution de requêtes SQL »
La tâche d’exécution de SQL exécute des instructions ou des procédures stockées SQL à partir d’un package. La tâche peut contenir une seule ou plusieurs instructions SQL s’exécutant de façon séquentielle.
- Dans Business Intelligence Development Studio, ouvrez le projet Integration Services qui contient le package souhaité.
- Dans l’Explorateur de solutions, double-cliquez sur le package pour l’ouvrir.
- Créer une nouvelle variable de package « NumLignes » de type Int.
- Cliquez sur l’onglet Flux de contrôle puis, à partir de la Boîte à outils, glissez la tache d’exécution de requête SQL:
- Cliquez avec le bouton droit et sélectionnez Modifier ou Afficher l’éditeur avancé.
- Renseignez la connexion, dans le SQLStatment tapez l’instruction SQL :
EXEC PRC_ALIM_PAIEMENTS
@IdDemande = ?,
@DateDebut = ?,
@DateFin = ?,
@BaseGnVars = ?,
[…]
@NumLignesOUT = ? OUTPUT
- Dans l’onglet Mappage de paramètre :
La transformation de commande OLE DB exécute une instruction SQL pour chaque ligne d’un flux de données. Vous pouvez exécuter une instruction SQL qui insère, met à jour ou supprime des lignes d’une table de base de données.
- Dans Business Intelligence Development Studio, ouvrez le projet Integration Services qui contient le package souhaité.
- Dans l’Explorateur de solutions, double-cliquez sur le package pour l’ouvrir.
- Cliquez sur l’onglet Flux de données puis, à partir de la Boîte à outils, faites glisser la transformation de Colonne dérivée « NumLignes » et ajouté y une nouvelle colonne qui contiendra le nombre de ligné qu’a inséré ma procédure.
- Faites glisser la transformation de commande OLE DB sur la surface de dessin.
- Connectez la transformation de commande OLE DB à partir de la transformation précédente (Colonne dérivée).
- Cliquez avec le bouton droit et sélectionnez Modifier ou Afficher l’éditeur avancé.
- Sous l’onglet Gestionnaires de connexions, sélectionnez un gestionnaire de connexions OLE DB dans la liste Gestionnaires de connexions.
- Cliquez sur l’onglet Propriétés du composant, puis sur les points de suspension (…) dans la zone SqlCommand.
- Dans l’Éditeur de valeur de chaîne, tapez l’instruction SQL paramétrée avec un point d’interrogation (?) comme marqueur de paramètre pour chaque paramètre :
EXEC PRC_ALIM_PAIEMENTS
@IdDemande = ?,
@DateDebut = ?,
@DateFin = ?,
@BaseGnVars = ?,
[…]
@NumLignesOUT = ? OUTPUT
- Dans l’onglet Mappage de colonnes :
> La nouvelle colonne dérivée contient le nombre de lignes insérées par la procédure stockée !
Articles sur le même sujet :
A propos de Romain CASTERES
Ingénieur spécialisé dans le décisionnel des systèmes d'informations. Certifié Microsoft Technology Specialist : Business Intelligence Development and Maintenance, SQL Server 2008 Database Development, Implementation and Maintenance.
