[SSIS] SSIS Transactions: gestion de transactions

Ce billet présente de façon élémentaire comment SSIS travaille avec les transactions.

Les configurations et tests effectués dans ce billet sont réalisés au sein de SQL Server Data Tools (SSDT), mais peuvent s’appliquer à Business Intelligence Development Studio (BIDS).

SSIS et les transactions

Notions de transactions

Les transactions sont des unités opérationnelles destinées à maintenir l’intégrité des données. Pour plus de détails, vous pouvez jeter un coup d’œil ici.

Présentation des options de transactions SSIS

La propriété TransactionOption de SSIS (suivant le niveau de l’objet SSIS traité : package, tâche, container,…) peut être utilisée pour activer une transaction. Cette propriété possède 3 options :

  • Required à Pour l’objet SSIS concerné, démarre une nouvelle transaction, ou autorise l’utilisation de la transaction ayant démarré au sein de l’objet parent (c’est-à-dire, celui précédant l’objet SSIS concerné).
  • Supported à Permet à l’objet SSIS concerné de joindre la transaction utilisée par l’objet parent, mais sans en démarrer une nouvelle. Il s’agit de l’option par défaut au sein de SSIS.
  • NotSupported à Comme son nom l’indique, cette option n’autorise pas l’utilisation de transactions.

Voici un exemple d’activation d’une transaction au niveau d’un container de séquence (arbitrairement appelé Transaction container pour la forme), sous SSDT :


Présentation des niveaux d’isolation supportés

Comme on peut l’apercevoir sur la dernière figure, juste au-dessus de TransactionOption se trouve une option IsolationLevel qui permet de spécifier le niveau d’isolation à utiliser pour une transaction. Par défaut, Serializable est le niveau d’isolation employé par SSIS qui a pour avantage de réduire la manifestation d’anomalies transactionnelles (lectures « sales », lectures non-répétitives,…). En revanche, le revers de la médaille est qu’il fournit un niveau de verrouillage très strict pouvant impacter la durée des verrous posés.

D’autres niveaux d’isolation existent: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, READ COMMITTED SNAPSHOT, SNAPSHOT.

Pour mieux comprendre le fonctionnement des différents niveaux d’isolation, vous pouvez faire un saut ici. A noter que dans le cas de l’utilisation du niveau d’isolation READ COMMITTED SNAPSHOT ou SNAPSHOT, le versioning est maintenu au sein de SQL Server (base de données tempDB).

Comment SSIS traite avec les transactions

    Lancement d’une transaction

Les transactions sont, au sein de SSIS, activées via le paramétrage de la propriété TransactionOption (valeur à Required) au niveau d’un package, d’une tâche, d’une boucle ou d’un container. Et leur lancement n’est possible que si la fonctionnalité MSDTC (Microsoft Distributed Transaction Coordinator) est elle-même activée au sein de la (ou des) machine(s) où travailleront les transactions.

Le lancement de MSDTC peut se faire en tapant net start msdtc dans l’invite de commandes MS DOS :
Sans l’activation de cette fonctionnalité, l’erreur suivante sera levée :
Le lancement de MSDTC peut se faire en tapant net start msdtc dans l’invite de commandes MS DOS :


Le services de composants (Component Services accessible en tapant dcomcnfg) peut également être utilisé pour son activation ou configuration avancée. Voire Services (services.msc).

Sans l’activation du MSDTC, l’erreur suivante sera levée :

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B « The Transaction Manager is not available. ». The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Validation (COMMIT) et retour-arrière (ROLLBACK)

Supposons que :

  • L’on possède une table Emp, au sein d’une base de données MaBase, dont le contenu initial est le suivant :


  • Au sein d’un package SSIS, on utilise un container de séquence nommé Processing rows avec TransactionOption à Required, et contenant 2 composants d’exécution de requêtes SQL (de type Execute SQL Task) permettant de supprimer des lignes de données (composant volontairement nommé DELETE rows)
    ou de les mettre à jour (UPDATE rows) :


  • Avec la configuration suivante pour DELETE rows :


L’objectif de DELETE rows est donc de supprimer l’employé Duverne.

  • Et pour UPDATE rows :


    Le but d’UPDATE rows est de modifier l’âge de l’employé Dupont en lui donnant 35 ans au lieu de 34.

  • La propriété TransactionOption est à
    Required pour le container de séquence Processing rows et laissée à Supported pour les deux composants DML.

Test 1 : ROLLBACK en cas d’erreur.

Maintenant, simulons une erreur de traitement au sein du container en spécifiant la valeur False à la propriété ForceExecutionResult d’UPDATE rows :


Après l’exécution du container encapsulé dans une transaction :


… vérification du contenu de la table Emp via un simple SELECT dans la base de données cible:


Comme on peut le constater, même si l’opération de suppression s’est bien déroulée, elle a été annulée dès le moment où celle de mise-à-jour a échoué au sein de leur container commun. Le fait qu’un objet (package, container,…) soit encapsulé dans une transaction (TransactionOption à Required) permet d’annuler tout ordre DML si au moins une erreur est rencontrée.

Test 2 : COMMIT en cas de succès effectif.

Repassons la propriété ForceExecutionResult à sa valeur par défaut None pour UPDATE rows et relançons les traitements :


Pour finir, revérification du contenu de la table Emp :


En clair, si tous les éléments d’un objet (package, container, tâche,…) se déroulent avec succès, toute la transaction est validée après sa complétion.

Notons que si des transactions multiples sont amenées à être utilisées pour l’exécution d’une transaction (cas de 3 tâches dont l’une démarre une transaction, la seconde effectue des opérations spécifiques et la dernière est chargée la termine), la spécification de la valeur de RetainSameConnection à True, dans les propriétés du OLE DB
Connection Manager utilisé par les composants concernés serait souhaitable. Cela permet d’exécuter des tâches transactionnelles multiples sur SQL Server au sein d’une même connexion.

Pour aller plus loin…

Gardez un œil ici pour suivre d’autres sujets sur les transactions autour de SSIS.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s