SSIS – Procédures T-SQL – OUTPUT

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ée 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 :

01Dans 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.

  1. Dans Business Intelligence Development Studio, ouvrez le projet Integration Services qui contient le package souhaité.
  2. Dans l’Explorateur de solutions, double-cliquez sur le package pour l’ouvrir.
  3. Créer une nouvelle variable de package « NumLignes » de type Int.
  4. 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:

    SSIS

  5. Cliquez avec le bouton droit et sélectionnez Modifier ou Afficher l’éditeur avancé.
  6. Renseignez la connexion, dans le SQLStatment tapez l’instruction SQL :

    EXEC PRC_ALIM_PAIEMENTS

    @IdDemande = ?,

    @DateDebut = ?,

    @DateFin = ?,

    @BaseGnVars = ?,

    […]

    @NumLignesOUT = ? OUTPUT

  7. Dans l’onglet Mappage de paramètre :

    SSIS 2

Et voila, votre nouvelle variable « NumLignes » contient le nombre de lignes insérées par la procédure stockée !

02Dans un flux de données : Utilisation du composant « Commande OLE DB »

 

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.

  1. Dans Business Intelligence Development Studio, ouvrez le projet Integration Services qui contient le package souhaité.
  2. Dans l’Explorateur de solutions, double-cliquez sur le package pour l’ouvrir.
  3. 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.
  4. Faites glisser la transformation de commande OLE DB sur la surface de dessin.

    SSIS 1

  5. Connectez la transformation de commande OLE DB à partir de la transformation précédente (Colonne dérivée).
  6. Cliquez avec le bouton droit et sélectionnez Modifier ou Afficher l’éditeur avancé.
  7. Sous l’onglet Gestionnaires de connexions, sélectionnez un gestionnaire de connexions OLE DB dans la liste Gestionnaires de connexions.
  8. Cliquez sur l’onglet Propriétés du composant, puis sur les points de suspension (…) dans la zone SqlCommand.
  9. 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

  10. Dans l’onglet Mappage de colonnes :

    SSIS 4

Remarque : le champ de destination @NumLignesOut alimente notre colonnes d’entrée NumLignes !

–> Et voila, votre nouvelle colonne dérivée contient le nombre de ligne inséré par la procédure stocké !

3 Comments

Leave a comment

En savoir plus sur Pulsweb - Romain Casteres

Abonnez-vous pour poursuivre la lecture et avoir accès à l’ensemble des archives.

Continue reading