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.
- 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 :
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.
- 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 :
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é !
Merci pour ce post très intéressant.
Un article très intéressant, il est très utile d’avoir suivi une formation à ce genre d’outils sur SSIS, plus on a d’informations sur ce qui se passe lors du lancement d’un package plus on gagne du temps en cas d’erreurs.
Merci infiniment pour ces clarifications! c’est très interessant!