[SSIS] Data Transformation : synchronisation des suppressions entre une source de données Excel et une base de données SQL Server (Lookup Transformation)

Ce billet répond, via un exemple très simple, à la problématique suivante : « Comment supprimer des lignes de données d’une table en se basant sur le contenu d’une autre source de données ? ». Nous utiliserons la synchronisation entre une feuille Excel et une table SQL Server, pour l’exemple.

Le modus operandi de notre exemple reprendra les grandes lignes du billet relatif à la synchronisation des UPSERTs vue ici.

Rappel des besoins et de la méthode de travail

Comme dans le billet relatif à la synchronisation des UPSERTs,
admettons que l’on dispose d’un fichier Excel Employe.xls (ou Employe.xlsx) et que l’on souhaite pouvoir synchroniser ses changements avec une table Employes stockée au sein d’une base de données SQL Server MaBase.

Voici le contenu du fichier Excel (avec 8 enregistrements) :


Les informations stockées au sein d’une feuille Excel appelée Employés.

La table Employe de la base de données SQL Server, quant à elle, possède, pour rappel, une structure assez simple :


On suppose qu’elle est vierge de tout enregistrement.

Il existe diverses façons de traiter les données à synchroniser, avec notamment l’utilisation de 2 types de composants de data flows SSIS :

  • Merge Join Transformation, qui permet de réaliser une réunion (LEFT, FULL ou INNER) de deux jeux de données préalablement triés afin d’y générer le résultat de leur fusion.
  • Lookup Transformation, qui permet d’effectuer des opérations de recherche via l’équijointure de colonnes d’entrée à des colonnes d’un jeu de données de référence.

Nous allons opter pour une recherche de transformation. Et pour mener à bien l’implémentation de notre mécanisme de suppression synchronisée, nous procéderons à l’utilisation d’une table de travail (ou table intermédiaire) qui sera destinée à récupérer le contenu du fichier Excel, pour être ensuite être comparée avec la table Employes.

A l’instar de ce qui a été vu pour la synchronisation des UPSERTs, nous adopterons une approche incrémentielle pour le chargement des données à supprimer.

Modus operandi

Les phases de nommage sont facultatives.

Création d’une table intermédiaire NoMatchingEmp

Dans l’onglet Control Flow, faites glisser une tâche Data Flow Task, renommez-la Data Flow Task – Lookup DELETE.


Dans l’onglet Data Flow de Data Flow Task – Lookup DELETE :

  • Reprenez toutes les étapes préliminaires et celles sur l’insertion vues dans le billet relatif à l’UPSERT en effectuant 2 petits ajustements :
    • Au sein de la section Connection de Lookup, remplacez la table Employes par la table NoMatchingEmp (table qui sera créée plus loin):


    • Renommage du composant OLE DB Destination – Employés en OLE DB Destination – NoMatchingEmp :


  • Sitôt les 2 ajustements effectués, accédez aux propriétés de NoMatchingEmp :
    • Puis cliquez sur le bouton New, pour créer une nouvelle table sur la base MaBase (où est stockée Employes) :


  • Validez la création de la structure de la table comme suit :


Afin d’arriver au résultat suivant :


  • Effectuez et validez l’équijointure entre la colonne ID du fichier Excel et la colonne Emp_ID de la table NoMatchingEmp :


  • Validez toutes les configurations effectuées dans les propriétés de NoMatchingEmp.
Dans les faits, vu que l’objectif est d’effectuer une suppression de ligne(s), la création d’une seule colonne, Emp_ID, au sein de NoMatchingEmp peut amplement suffire.

Finalisation du mécanisme de suppression

  • Dans l’onglet Control Flow, faites glisser une tâche Execute SQL Task, renommez-la TRUNCATE NoMatchingEmp, puis créez une contrainte de précédence positive de TRUNCATE NoMatchingEmp vers Data Flow Task – Lookup DELETE :


  • Dans les propriétés de TRUNCATE NoMatchingEmp (accessibles via un double-clic, par exemple), spécifiez les paramètres de connexion à la base MaBase (les gestionnaires de connexion créés sont dans la liste déroulante) et le code source SQL de la troncature de la table NoMatchingEmp dans la zone réservée à l’option SqlStatement :


Validez le travail en cliquant sur OK.

  • Dans l’onglet Control Flow, faites glissez une nouvelle tâche Execute SQL Task, renommez-la DELETE, puis placez une contrainte de précédence positive allant de Data Flow Task – Lookup DELETE vers DELETE :


  • Dans les propriétés de DELETE, spécifiez le gestionnaire de connexion relatif à la base MaBase, puis codez la suppression d’enregistrements sur la table Employes :


Voici le détail du code source SQL :


  • Validez le travail…


Tests

Supposons que la table Employes de la base de données SQL Server soit déjà remplie :


Au sein de la zone de contrôle de flux (Control Flow), pressez sur F5, ce qui aura pour effet de donner le résultat suivant :


Et plus exactement, au niveau de Data Flow Task – Lookup DELETE :


Du côté de la table Employes, vu qu’il n’y a pas eu de suppression au sein du fichier Excel, elle reste donc inchangée :

Supprimons, maintenant, 2 enregistrements dans le fichier Excel (IDs 3 et 4) :

Après relance du package SSIS, voici ce que donne le contenu de la table Employes du côté de la base de données :

Pour aller plus loin…

Pour compléter le travail, il est possible de modifier les flux de contrôles de façon à pouvoir inclure une opération de synchronisation d’UPSERTs. Par exemple :


Le mode opératoire est le même pour tout autre type de fichier (plat, raw,…). Vous pouvez garder un œil ici, pour consulter les autres billets relatifs à la transformation de données.

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