[SSIS] Data Transformation : synchronisation des insertions et des mises-à-jour entre une source de données et une destination (Merge Join Transformation)

Ce billet répond, via un exemple très simple, à la problématique suivante : « Comment insérer ou mettre-à-jour 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 avec un composant Merge Join Transformation entre une feuille Excel et une table SQL Server, pour l’exemple.

Le modus operandi de notre exemple d’implémentation sera effectué avec l’outil de développement SQL Server Data Tools (à partir de SQL Server 2012). Mais toutefois, il reste valable dans le cas d’une utilisation de Visual Studio ou de Business Intelligence Development Studio (BIDS) dont l’interface graphique est sensiblement la même.

En outre, le contenu du billet reprend celui de la version axée autour du composant SSIS Lookup Transformation, mais en l’adaptant pour la version Merge Transformation.

Notons qu’une opération d’insertions et de mises-à-jour est appelée UPSERT (ou UPDATE-INSERT).

Avant de commencer…

Présentation des besoins

Admettons que l’on dispose d’un fichier Excel Employe.xls (ou Employe.xlsx) et que l’on souhaite pouvoir synchroniser les insertions et mises-à-jour (UPSERTs) avec une table Employes stockée au sein d’une base de données SQL Server MaBase.

Voici le contenu du fichier Excel :


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 une structure assez simple :


On suppose qu’elle est vierge de tout enregistrement.

    Au sujet du choix de la méthode de synchronisation

La sratégie de synchronisation choisie sera celle incrémentielle. Il est, bien sûr, possible d’adopter une solution plus « brute » qui consiste à tronquer la table de destination avant d’y insérer le contenu de la source de données (Excel, dans notre cas). Cette solution possède principalement 2 limites qui peuvent être problématiques dans le monde réel :

  • La troncature n’est pas supportée sur une table concernée par une contrainte d’intégrité référentielle de type FK (foreign key, ou clé étrangère).
  • Si l’on est amené à travailler avec une dizaine de millions d’enregistrements, le fait de fréquemment insérer tous les enregistrements « from scratch » peut avoir un impact non-négligeable au niveau des performances. Sans parler de problèmes liés aux blocages (toute opération d’insertion ou de mise-à-jour étant transactionnellement bloquante).

Du côté des méthodes de transformation de données, 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 Merge Join Transformation.

Pour information, comme cela a été laissé entendre plus haut, une opération de tri est nécessaire au bon fonctionnement d’un Merge Join. Que ce soit via l’utilisation d’un ORDER BY au sein de la source de données, soit du composant Sort (qui sera choisi dans ce billet).
Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms137653.aspx.

Modus operandi

    Conception d’un data flow d’UPSERT: Data Flow Task – UPSERT pour l’insertion et la mise-à-jour de données

        Etapes préliminaires

  • Au sein de l’onglet Control Flow :
    • Faites glisser un composant Data Flow Task :


    • Après avoir renommé le composant en Data Flow Task – UPSERT (en cliquant dessus, ou en effectuant un clic-droit>Rename), double-cliquez sur ledit composant pour accéder à la définition de ses flux de données (onglet Data Flow).


  • Au sein du Data Flow de Data Flow Task – UPSERT :
    • Faites glisser (ou double-cliquez sur) le composant Excel Source, situé dans la SSIS Toolbox, dans la zone de design :


    • Renommez le composant Excel Source en Excel Source – Employés :


  • Au sein des propriétés du composant Excel Source – Employés (accessible via clic-droit>Edit… ou double-clic), spécifiez le nouveau gestionnaire de connexion Excel :


Dans notre cas, comme la première ligne notre fichier Excel contient le nom des colonnes (ID, Nom, Prénom, Age), l’option First row has column names cochée.

  • Choisissez la feuille du fichier Excel à utiliser (Employés, dans notre cas) :


Un clic sur Preview permet de prévisualiser le contenu de la feuille du fichier Excel :


Si vous le souhaitez, vous pouvez (dans la section Columns)
consulter les colonnes (automatiquement) sélectionnées, et/ou éventuellement les renommer :


Dans tous les cas, validez les modifications effectuées pour le composant Excel Source en pressant sur OK.

  • Toujours au sein de la zone de design Data Flow de Data Flow Task – UPSERT, faites glisser (ou double-cliquez sur) un composant Data Conversion :


  • Renommez le composant Data Conversion en Excel Data Conversion :


  • Placez une contrainte de précédence de succès (bleue sous Data Tools, verte sous BIDS) allant de Excel Source – Employés vers Excel Data Conversion :


  • Au sein des propriétés du composant Excel Data Conversion, spécifiez les types de données de chaque colonne, en tenant compte de celles de la future destination (i.e., la table Employes de SQL Server) :


Pour être exact :

Colonnes du fichier Excel Type de données choisi Type de données correspondant à la table SQL Server
ID Four-byte signed integer INT
Nom Unicode string (20) NVARCHAR(20)
Prénom Unicode string (30) NVARCHAR(30)
Age Four-byte signed integer INT

Vous pouvez donner un alias à chaque colonne Excel convertie.

Validez en cliquant sur OK.

  • Faites glisser (ou double-cliquez sur) un composant Sort :


  • Placez une contrainte de précédence de succès allant d’Excel Data Conversion vers Sort :


  • Dans les propriétés de Sort, sélectionnez Excel Data Conversion.ID afin que le tri soit effectué en fonction de la colonne d’IDs convertie, et assurez-vous que les autres colonnes converties soient les seules traversées (section Pass Through) :


    Vous pouvez éventuellement cocher l’option Remove rows with duplicate sort values si vous souhaitez éliminer tout éventuel doublon.

  • Suivez les mêmes étapes allant de l’implémentation d’Excel Source à Sort, mais en remplaçant Excel Source par OLE DB Source qui pointera vers la base de destination : MaBase :


    Avec :

    • Configuration d’OLE DB Source (renommé en OLE DB Source – Employés)
      comme suit :
      • Onglet Connection Manager :

    Si aucun gestionnaire de connexion pointant vers Ma Base n’a été préalablement configuré, alors cliquez sur le bouton New…, puis, dans la section Connection, spécifiez les informations de connexion de la table Employes de la base de données MaBase située sur une instance par défaut SQL Server située sur une machine appelée MAC-ROG. Pour ce faire, cliquez sur New afin d’afficher, par la suite, la boîte modale suivante où vous pourrez remplir vos informations :


Le login SQL ssis_user a, bien sûr, été préalablement créé au sein de l’instance SQL Server. Vous pouvez cependant utiliser un compte Windows (en le supposant idéalement dédié aux tâches SSIS).

Validez et sélectionnez la table Employes :


  • Onglet Columns :


  • Configuration du Data Conversion (renommé en OLE DB Data Conversion) :


  • Configuration du Sort (renommé en Sort Table) de la même façon que pour la version Excel.
  • Faites glisser un composant Merge Join dans le canevas :


  • Placez une contrainte de précédence de succès allant de Sort vers Merge Join avec Left Join comme type de jointure :


    L’utilisation d’une jointure de type Left Join permet de ne sélectionner que les lignes de données de la source de données qui ne sont pas présents dans la table de destination. Plus de détails ici, si vous souhaitez comprendre comment fonctionne ce type de jointure.

  • Placez également une contrainte de précédence allant de Sort Table vers Merge Join :


  • Configurez le Merge Join comme suit :


  • Faites glisser un composant Conditional Split et placez une contrainte de précédence allant de Merge Join vers ledit composant :


  • Configurez le Conditional Split comme suit :


    La sortie par défaut a été renommée en Existing (au lieu de Conditional Split Default Output) pour des raisons de lisibilité.

    Le Merge Join avec option « Left Join » récupère tout le contenu de l’Excel – que les données soient présentes ou non dans la table Employes – puis remplace, dans le jeu de résultats, les lignes Excel absentes dans ladite table par des valeurs nulles (NULL).
    Le but du Conditional Split est donc, ici, de récupérer les lignes de données de l’Excel qui sont en NULLs dans la table Employes, puis de « splitter » le nouveau jeu de résultats en 2 parties : l’une pour les insertions (New) et l’autre pour les mises-à-jour (Existing).

Insertion

  • Faites glisser un composant OLE DB Destination (renommé en OLE DB Destination – Employés), puis placez une contrainte de précédence entre Conditional Split et ledit composant, avec New comme option de sortie :


  • Configurez OLE DB Destination – Employés comme suit :
    • Optez pour le même gestionnaire de connexion qu’OLE DB Source – Employés, puis spécifiez la table à utiliser : Employes. Cochez ensuite l’option Use results of an SQL query pour spécifier les colonnes à joindre de la table Employes (méthode choisie à titre indicatif, sachant que vous pouvez directement la sélectionner dans la liste déroulante de l’option Use a table or a view si vous comptez utiliser toutes ses colonnes) :


La prévisualisation confirme qu’elle est vide, comme indiqué en début de billet :


  • Dans la section Columns, effectuez une équijointure entre la colonne ID convertie du fichier Excel et la colonne Emp_ID de la table SQL Server :


  • Validez le travail en cliquant sur OK pour arriver au résultat suivant :


Mise-à-jour

  • Toujours au sein du canevas de Data Flow de Data Flow Task – UPSERT, faites glisser (ou double-cliquez sur) un composant OLE DB Command (renommé en OLE DB Command – Update Employés)
    :


  • Placez une contrainte de précédence de succès allant de Conditional Split vers OLE DB Command – Update Employés. L’option de sortie Existing  est à choisir :


  • Au sein des propriétés du composant OLE DB Command – Employés…
    • Dans la section Connection Manager,
      spécifiez le gestionnaire de connexion OLE DB MAC-ROG.MaBase (déjà créé lors de l’implémentation du traitement des insertions) pour accéder à la table Employes de la base MaBase de l’instance SQL Server située sur la machine MAC-ROG :


  • Dans la section Component Properties, spécifiez le code SQL paramétré de mise-à-jour de la table Employes dans la zone dédiée à SqlCommand :


Ce qui donnera, après validation :


  • Dans la section Column Mappings, faites les correspondances entre chaque colonne convertie du fichier Excel et chaque paramètre, sachant que :
    • Param_0 correspond au premier « ? » de notre code SQL spécifié précédemment. Et donc, au paramètre de Emp_Nom.
    • Param_1, à celui d’Emp_Prenom.
    • Param_2, à celui d’Age.
    • Param_3, à celui d’Emp_ID.


  • Validez pour arriver au résultat suivant :


Tests

Dans l’onglet Control Flow ou Data Flow Task – UPSERT, pressez sur F5 pour executer les flux de travaux.

Si tout va bien, le résultat sera le suivant :


On peut noter que 8 lignes (soient toutes les lignes du fichier Excel) ont été insérées. Pour vérifier cela (du côté de notre fidèle compagnon SQL Server Management Studio) :


Maintenant, mettons à jour le fichier Excel en remplaçant l’employée Colette Plantin par une dénommée Odile Besnard, âgée de 49 ans :


Après relance de notre package, voici le résultat :


Ce qui donnera au niveau de la base de données :


Pour finir, insérons un nouvel enregistrement au sein du fichier Excel :


Après avoir relancé le package, voici le résultat :



Pour aller plus loin…

Le mode opératoire peut être utilisé pour les autres types 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