Le Post Infeeny

Les articles des consultants et experts Infeeny

Les Transactions dans SSIS 2012

A quoi servent les transactions ?

Les transactions sont des suites d’opérations (Insertion, Suppression, Mise à Jour…) qui font passer la base de données d’un état A (état initial avant les opérations) à un état B (après les opérations).
Dans le cas où une opération échoue, la transaction permet de revenir à l’état antérieur B, c’est-à-dire avant le démarrage de la transaction.
Toutes les modifications seraient dans ce cas annulées.

Dans l’exemple qui va suivre, nous allons voir comment mettre en place les transactions afin d’annuler des opérations opérées sur une base de données en cas d’échec.

Pré-requis : Activation du service MSDTC

Tout d’abord, activons le service.
Pour se faire, allez dans les services Windows soit par la commande services.msc ou soit en passant par le panneau de configuration -> Outils d’administration -> Services.

Type de démarrage du service MSDTC

Type de démarrage du service MSDTC

Sélectionnez « Coordinateur de transaction distribuées » (Distributed Transaction Coordinator).

Dans le cas présent, on peut noter que le service est en démarrage manuel.
Pour le mettre en automatique à chaque démarrage de la machine :
1- Allez dans les propriétés
2- Changez le type de démarrage

Sélection du type de démarrage automatique

Type de démarrage Automatique

3- Cliquez sur le bouton OK, pour valider la modification.

Nous obtenons :

Démarrage Automatique du service MSDTC

Démarrage Automatique du service MSDTC

4- Maintenant, cliquez-droit sur le service puis démarrez-le.

Démarrage du sevice MSDTC

Démarrage du sevice MSDTC

Une fois le démarrage terminé, l’état du service est  “En cours d’éxécution”.

Service MSDTC en cours d'execution

Service MSDTC en cours d’execution

Pratique : Mise en place d’une transaction

1- Création de la table stgCustomer

Tout d’abord, exécutez dans SSMS, le script de création de la table stgCustomer :

USE [AdventureWorks2012]
GO

CREATE TABLE [dbo].[stgCustomer](
[CustomerID] [int] NULL,
[PersonID] [int] NULL,
[StoreID] [int] NULL,
[TerritoryID] [int] NULL,
[AccountNumber] [varchar](20) NULL,
[ModifiedDate] [datetime] NULL
ON [PRIMARY]
GO

noteNote : Cette table est tout simplement la copie simplifiée de la table Sales.Customer de la base AdventureWorks2012.
Nous allons insérer les données de la table Sales.Customer dans cette nouvelle table.

2- Création du package Transaction.dtsx

Dans SSDT (Sql Server Data Tools), Créez un nouveau package et renommez le Transaction.dtsx.
Puis double-cliquez pour l’ouvrir.

Création du package transaction.dtsx

Création du package transaction.dtsx

3- Création de la connexion OLE DB

Etant donné que nous aurons besoin d’utiliser la table que nous venons de créer,
nous allons créer une connexion à la base AdventureWork2012.

a. Pour cela, faite un clique-droit sur le gestionnaire de connexion (Connection Managers) et sélectionnez “New OLE DB Connection”.

Sélection de la connexion OLE DB

Sélection de la connexion OLE DB

b. Dans la fenêtre suivante cliquez sur le bouton “New” pour créer la connexion.

Création de la connexion OLE DB

Création de la connexion OLE DB

c. Renseignez le nom de votre serveur (Server Name) et sélectionnez le nom de votre base dans la liste déroulante.

Configuration de la connexion OLE DB

Configuration de la connexion OLE DB

 d. Cliquez sur le bouton OK pour terminer.

Maintenant, vous remarquerez que la connexion à bien été créée.
En effet dans la partie “Data connections” et “Data connection properties”, on retrouve les informations essentielles.

Détails de la connexion OLE DB localhost.AdventureWorks2012

Détails de la connexion OLE DB localhost.AdventureWorks2012

e. Cliquez sur le bouton OK pour terminer.

note Note : Dans le gestionnaire de connexion, la connexion est maintenant présente
(Connexion localhost.AdventureWorks2012)

Connexion localhost.AdventureWorks2012

4- Suppression des données de la table stgCustomer : Execute Sql Task

a. Insérer le composant Exécute SQL Task dans le flux de contrôle (Control Flow).
b. Renommez le SQLT – Truncate table stgCustomer.

Composant Execute SQL Task

Composant Execute SQL Task

c. Double-cliquez pour l’éditer et renseignez les champs suivants :

– Connection : le nom de la connexion à utiliser. (1)
– SQLStatement : la requête à exécuter => TRUNCATE TABLE stgCustomer(2)

Configuration de l’Execute SQL Task

Configuration de l’Execute SQL Task

d. Cliquez sur le bouton OK pour terminer.

5- Alimentation de la table stqCustomer

a. Insérez un “Sequence Conteneur”, et reliez les par une contrainte de précédence “Succes” (flèche verte).

Mise en place de la contrainte de précédence succès

Mise en place de la contrainte de précédence succès

b. Dans le conteneur, insérez un composant “Data Flow Task” et renommer le “DFT – Alim stqCustomer”.

Composant Data Flow Task

Composant Data Flow Task

c. Double-cliquez sur le composant pour l’éditer

et insérez une “OLEDB Source” et une “OLEDB Destination” et renommez les respectivement “SRC – Sales Customer” et “DEST – stgCustomer”.

d.
 Reliez les avec un flux de donnée (flèche bleu).

Source OLE DB et Destination OLE DB

Source OLE DB et Destination OLE DB

e. Double-cliquez sur la source “SRC – Sales.Customer” pour l’éditer

et enseignez la connexion utilisée (celle crée précédemment), ainsi que le nom de la table source (Sales.Customer).
Puis cliquez OK pour valider.

Configuration de la source OLE DB

Configuration de la source OLE DB

note Note : Cliquez sur le bouton “Préview” pour voir les données contenus de la table.

f. Faites de même pour le composant de destination “DEST – stgCustomer”, en sélectionnant comme table de destination “stgCustomer”.
Puis Vérifier dans le menu “Mappings” que les colonnes ont bien été reliées.
Cliquez sur le bouton OK pour terminer.

Mapping des champs de la source et de la destination

Mapping des champs de la source et de la destination

5- Mise à jour de la table stgCustomer

a. Retournez dans le flux de contrôle pour insérer dans le conteneur un “Execute SQL Task” et renommez le “SQLT – Update stgCustomer”.
b. Renseignez la connexion à utilisée et la requête à exécuter.

UPDATE stgCustomer
SET CustomerID ‘MSBI’;

Avec cette requête, nous allons tenter de mettre à jour le champ CustomerID de la table stgCustomer.

Configuration et écriture de la requête Update du comporant Execute SQL Task

Configuration et écriture de la requête Update du comporant Execute SQL Task

c. Cliquez sur le bouton OK pour terminer.

d. Reliez le Data Flow Task et le Execute SQL Task.

Voici à quoi ressemble le package final.
Les actions sont donc les suivantes :

– On vide la table stgCustomer =>  SQLT- Truncate Table stgCustomer
– On l’alimente à partir de la table Sales.Customer => DFT – Alim stgCustomer
– On met à jour le champ CustomerID de la table stgCustomer => SQLT – Update stgCustomer

Contenu du package

Contenu du package


6- Exécution du package

a. Exécutez le package.

Exécution du package en erreur

Exécution du package en erreur

Le package c’est terminé avec une erreur :

Message d'erreur du package

Message d’erreur du package

[Execute SQL Task] Error: Executing the query « UPDATE stgCustomer
SET CustomerID = ‘MSBI’; » failed with the following error: « Conversion failed when converting the varchar value ‘MSBI’ to data type int. ». Possible failure reasons: Problems with the query, « ResultSet » property not set correctly, parameters not set correctly, or connection not established correctly.

Cause : En effet dans l’UPDATE, nous avons tenté de mettre à jour CustomerID, qui est de type int par une valeur de type String.
Cette erreur a été était faite volontairement et rentre dans le cadre de notre démonstration.

b.  Exécutez la requête ci-dessous afin de vérifier que malgré l’erreur, la table stgCustomer a bien été alimentée.

SELECT *
FROM [AdventureWorks2012].[dbo].[stgCustomer]

Contenu de la table stgCustomer après exécution du package

Contenu de la table stgCustomer après exécution du package

 7- Activer la transaction avec TransactionOption

Maintenant nous allons mettre en place le système de Transaction qui va permettre à la table d’être remise à son état initiale sans les nouvelles données de l’exécution qui a générée une erreur.

a. Allez dans les propriétés du Sequence Container, en faisant un clique-droit sur ce dernier.

Activation de la transaction

Activation de la transaction

 b. Modifier la valeur champ “TransactionOption” à “Required

TransactionOption-Required

TransactionOption-Required

note Note :

TransactionOption :

  • Required indique que le conteneur démarre une transaction, à moins qu’une autre transaction soit déjà démarrée par son conteneur parent. Si la transaction existe déjà, le conteneur rejoint la transaction.
    Par exemple :
    si un package non configuré pour prendre en charge les transactions inclut un conteneur de séquences utilisant l’option Required, le conteneur de séquences démarre sa propre transaction. Si le package a été configuré pour utiliser l’option Required , le conteneur de séquences rejoint la transaction du package.
  • Supported (valeur par défaut) indique que le conteneur ne démarre pas de transaction, mais rejoint toute transaction démarrée par son conteneur parent.
    Par exemple :
    si un package avec quatre tâches d’exécution SQL démarre une transaction et que les quatre tâches utilisent l’option Supported, les mises à jour de la base de données effectuées par les tâches d’exécution SQL sont annulées en cas d’échec d’une des tâches. Si le package ne démarre pas de transaction, les quatre tâches d’exécution SQL ne sont pas liées par une transaction et aucune mise à jour de la base de données n’est annulée, sauf celle effectuée par la tâche qui a échoué.=> Dans notre cas nous avons mis le conteneur en Required et laissé par défaut les composants qu’il contient en Supported.
    Ces dernières vont donc rejoindre voir hériter la transaction démarrée par le Sequence Container.
  • NotSupported indique que le conteneur ne démarre pas de transaction et ne rejoint aucune transaction existante. Une transaction démarrée par un conteneur parent n’affecte pas les conteneurs enfants configurés pour ne pas prendre en charge les transactions.
    Par exemple :
    si un package est configuré pour démarrer une transaction et qu’un conteneur de boucles For dans le package utilise l’option NotSupported, aucune des tâches de la boucle For ne peut être annulée si elle échoue.

IsolationLevel:

Dans la partie transaction, parmi les propriétés disponible, il y aussi la propriété IsolationLevel.
Cette dernière permet de gérer les interactions entre transactions.
Par défaut sa valeur est « Sérializable », mais n’est prise en compte que si la transaction est active, c’est-à-dire que la valeur de la propriété TransactionOption à pour valeur « Required ».
Elle permet à une transaction de ne pas lire de données erronées, par exemple en cours de modification par une autre transaction.

Aller plus loin : http://msdn.microsoft.com/fr-fr/library/system.transactions.isolationlevel.aspx

8- Exécution du package

a. Exécutez de nouveau le package.

Dans l’onglet « Progress », le message « Starting distributed transaction » apparaît normalement dans la trace.
Ce qui signifie que le conteneur à bien été configuré :

Starting distributed transaction

Starting distributed transaction

Exécution du package avec TransactionOption à Required

Exécution du package avec TransactionOption à Required

Suite à l’échec du conteneur on obtient dans l’onglet « progress », le message suivant :

Aborting the current distributed transaction

Aborting the current distributed transaction

b. Vérifier les données contenues dans la table stgCustomer.

Contenu de la table stgCustomer avec la transaction active

Contenu de la table stgCustomer avec la transaction active

Résultat : Les données n’ont pas été insérer dans la table.

On dit que la transaction à fait un rollback des données c’est a dire que lors de l’échec, toutes les modifications qui ont eu lieu sur la table ont été annulées.

note Note : Dans un autre article j’implémenterais une autre méthode, visant à utiliser les requêtes SQL.

Tutorial rédigé par Harry KANCEL

Sources :

http://msdn.microsoft.com/fr-fr/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://fr.wikipedia.org/wiki/Transaction_informatique

Aller plus loin :
Isolation des transactions (Option IsolationLevel) : http://docs.postgresql.fr/8.2/transaction-iso.html
Transaction héritées : http://msdn.microsoft.com/en-us/library/ms141083.aspx
Transactions multiples : http://msdn.microsoft.com/en-us/library/ms141059.aspx
IsolationLevel : http://msdn.microsoft.com/fr-fr/library/system.transactions.isolationlevel.aspx
http://en.wikipedia.org/wiki/Isolation_(database_systems)

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

%d blogueurs aiment cette page :