[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

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

  1. Ibrahim dit :

    Bonjour, merci pour ce tuto.

    en arrivant à l’étape de la configuration de merge Join il me sort l’erreur suivant:

    ——————————

    Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata.

    ——————————

    J’ai suivi les étapes comme vous l’avez mis sur le tuto.

    Serait il possible de me guider ?

    Merci par avance

  2. Ibrahim dit :

    c’est bon, j’ai résolu le problème, les types des ID dans les deux conversion était differents , donc j’ai remis les types dans les deux sources en « Four-byte signed integer » et ça marche.

    Merci

  3. Ibrahim dit :

    Merci,
    Après avoir appliqué le tuto, l’insertion et la mise à jour fonctionne à merveille et je vous remercie.
    Maintenant j’ai envie de récupérer aussi le « existing » du conditionnal split devant les lignes qui existe déjà dans ma table et aussi le « new » devant les nouvelles lignes créée dans ma table.
    Je m’explique:
    Ma table de destination contient un champ supplémentaire qui se nome « maj », dans ce champ je en plus de « ID » , « Nom », « Prenom » , « Age » , dans « maj » je veux mettre la valeur  » existing » si « ID » existe déjà dans la table destination, et « New » si c’est une nouvelle insertion.
    Comment pourrais-je procéder ? Si vous avez la réponse ça m’aiderait bien pour avancer sur le sujet .

    Merci d’avance d’avoir pris le temps de lire ma demande.

    • Bonjour Ibrahim,

      Il existe diverses façons de faire, mais si l’on reste dans le contexte de ce billet, il suffirait de suivre la roadmap suivante:
      1) Pour les valeurs existantes : créer une colonne dérivée avec pour valeur « Existing », puis au sein de l’instruction UPDATE d’OLE DB Command, rajouter « maj » et l’associer à ladite colonne dérivée.
      2) Pour les nouvelles valeurs : mettre simplement la valeur par défaut à « New » pour « maj » au sein de la table ciblée. Il est possible de faire cela sans passer par SSIS, pour gagner du temps. Par exemple, sous SSMS: ALTER TABLE Votre_Table ADD DEFAULT ‘New’ FOR maj.

      Concernant le 1), le composant OLE DB Command n’étant pas toujours très performant sur un très gros volume de données, vous pouvez éventuellement utiliser une staging table.

      Bien à vous,
      M.

      • Ibrahim dit :

        Super, Merci pour la réponse, je vais suivre vos instructions :-).

      • Ibrahim dit :

        C’est parfait, ça fonctionne très bien, je débute bien avec le Ssis et j’apprends des choses, merci pour le tuyau de la colonne dérivée, j’ai appris des choses là 😀 , merci infiniment.

      • Ibrahim dit :

        Bonjour, j’ai rajouté une colonne dérivée qui renvoie la date du système, cette date est insérée dans la table destination.
        Est ce qu’il y a un moyen d’identifier que la ligne qui a été modifiée , je m’explique:
        J’ai par exemple la table source qui contien:

        Fichier souce:
        ID | col1 | col2 |
        1 toto toto
        2 tata tata

        Table destination
        1 toto toto
        2 tata tata

        Je veux apporter une modification sur l’id « 2 » comme suite

        1 toto toto
        2 tata TITI

        Dans la table destination je veux identifier que la ligne qui a été modifiée dans la table, c à dire dire que l id 2 modifié avec la nouvelle date du système.

        Aujourd’hui si j’envoie les deux ligne ID 1 et ID 2 (avec la modif) , les deux lignes existent dans la base de données, et la colonne dérivée renvoie à chaque fois la nouvelle date du système qu il y ait une modif ou pas.

        Est ce que je peux identifier que la ligne avec la nouvelle date et pas toutes les lignes existante?

        Je ne sais pas si je demande trop , mais j’en profite car je suis bien parti avec votre tuto :-).

        Merci d’avance.

      • Bonjour Ibrahim,

        Pourquoi ne pas appliquer la même méthode qu’avec Existing, mais en affectant la valeur GETDATE() à la colonne dérivée ?

        Bien à vous,
        M.

  4. Ibrahim dit :

    Bonjour Mohamed,
    J’ai appliqué la même méthode qu’avec Existing en affectant la valeur GETDATE, mais le soucis est que si je charge le même fichier dans la destination , il applique la getdate() pour tout les identifiants du fichier source , que ça soit des identifiants modifiés ou non . Moi je veux que le getdate change que sur la ligne qui a été modifiée et pas toutes les lignes résistantes, merci.

    • Bonsoir Ibrahim,

      Avez-vous vérifié que la condition WHERE était correctement spécifiée, avec les bons filtres, etc… ?

      Bien à vous,
      M.

      • Ibrahim dit :

        J’utilise un connecteur de kingwaysoft pour se connecter à la base de données, c est une connexion au CRM de MSDynamics, donc pour la condition Where c’est directement dans le connecteur qui propose de faire un mappage, la modification se fait bien et sans aucun soucis, c’est Just au niveau de la getdate qui d’actualise à chaque fois pour tout les identifiants modifiés et aussi la mise à jour de la getdate des identifiants existants et qui n’ont pas étaient modifiés.

      • Ibrahim,

        De mon côté, cela fonctionne sans souci, après test rapide.

        Je ne connais pas le connecteur Kingwaysoft, mais je vous propose de faire quelques tests éliminatoires simples. Par exemple, un test manuel sous SSMS pour vérifier si votre requête passe bien.

        Alternativement, possibilité également d’ajouter une colonne « Status », avec valeur mise à « Updated » lors de l’UPDATE, puis lancer une tâche Execute T-SQL qui va updater la date de toutes les lignes existantes dont le statut est à « Updated ». Statut qui devra d’ailleurs être réinitialisé avant chaque lancement du package ou des flux.

        Toujours mieux qu’un trigger coûteux ou le CDC si vous n’avez pas Enterprise.

        My 2 cents,
        M..

      • Ibrahim dit :

        Merci, je vais faire les tests et je reviens vers vous , merci infiniment.

      • Ibrahim dit :

        Bonjour,
        Je pense que ma conception n’est pas adaptée à ce que je veux avoir, au final , j’ai réussi à matcher les colonnes dans la jointure (inner join) , c’est à dire à la sortie de ma jointure j’ai réussi à transférer les lignes qui n’ont pas étaient modifier ce qui est normal avec ma jointure , je compare tous les champs de ma table source avec ceux de ma table destination, si il y a une différence de données je prends pas et si Il y a un match complet je continue ma conception. Mais mon souhait est que je veux avoir les lignes qui n’ont pas marchés à la sortie de ma jointure , et ca je ne vois pas comment faire , les lignes qui n’ont pas marché c’est ceux qui ont étaient modifiées. Je ne vois pas comment faire ? …
        Merci

      • Bonsoir,

        Avez-vous tenté un left join entre la table source et celle destination de sorte que le jeu de résultats l’affiche que les lignes non-présentes dans cette dernière ? Cf., https://bit.ly/2Rci7Tn.

        Bien à vous,
        M.

  5. Ibrahim dit :

    Bonjour,
    Je reviens vers vous pour vous informer que ça marche bien maintenant, merci infiniment, j’avais un problème dans le join, il fallait que je définisse bien le premier sort comme left et faire la jointure, effectivement avec le left join ca fonctionne, merci beaucoup.

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 )

Photo Google+

Vous commentez à l'aide de votre compte Google+. 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 )

Connexion à %s