[SSIS] Data Transformation : chargement incrémental des insertions et des mises-à-jour 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 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 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. 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 Employes 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 recherche de transformation.

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 positive (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 Lookup :

  • Placez une contrainte de précédente positive de Excel Data Conversion vers Lookup :

  • Dans les propriétés de Lookup :
    • Dans la section General, validez le mode de cache (dans notre exemple, Full cache) et le type de connexion (OLE DB Connection) ; et optez pour la redirection de lignes d’enregistrements en cas de non-correspondance (entre le fichier Excel et la table SQL Server) :

  • 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).

  • Spécifiez la table à utiliser. Cochez l’option Use results of an SQL query pour spécifier les colonnes à joindre de la table Employes (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.

Insertion

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

  • Renommez le composant OLE DB Destination en OLE DB Destination – Employés :

  • Placez une contrainte de précédence positive allant de Lookup vers OLE DB Destination – Employés. Cela aura pour effet de lancer une boîte modale où il faudra choisir l’option de sortie Lookup No Match Output :

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

  • Au sein des propriétés du composant OLE DB Destination – Employés…
    • Dans la section Connection Manager, spécifiez le nouveau gestionnaire de connexion OLE DB pour accéder à la table Employes de la base MaBase de l’instance SQL Server située sur la machine MAC-ROG :

A cette étape, vous pouvez choisir d’ignorer ou non les contraintes, les valeurs nulles, les identités. Le verrouillage de tables sera transactionnellement toujours présent pour les opérations de mises-à-jour afin d’assurer le respect des règles ACID.

  • Dans la section Mappings, faites les correspondances entre chaque colonne convertie du fichier Excel et chaque colonne de la table SQL Server :

  • Validez pour arriver au résultat suivant :

Mise-à-jour

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

  • Renommez le composant OLE DB Command en OLE DB Command – Update Employés :

  • Placez une contrainte de précédence positive allant de Lookup vers OLE DB Command – Update Employés. L’option de sortie Lookup Match Output sera automatiquement choisie, l’autre option (Lookup No Match Output) ayant déjà été spécifié dans la section de ce billet relative à l’insertion (voir plus haut).
  • Au sein des propriétés du composant OLE DB Destination – 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 6 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 Odile Besnard par une dénommée Colette Plantin :

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

Pour finir, insérons 2 nouveaux enregistrements au sein du fichier Excel :

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

Ce qui donne du côté de la table Employes, sur SQL Server :

Pour aller plus loin…

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

Publicités

Un commentaire sur “[SSIS] Data Transformation : chargement incrémental des insertions et des mises-à-jour entre une source de données Excel et une base de données SQL Server (Lookup Transformation)

  1. SANCHEZ dit :

    Merci pour votre poste qui est très clair et intéressant

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