Chargement des données sous SSIS : Statique vs Dynamique

Accueil / Actualités / Chargement des données sous SSIS : Statique vs Dynamique
Chargement des données sous SSIS : Statique vs Dynamique

Posté par Maxime Barsamian le 11 Mars 2021

Il arrive souvent qu’on nous demande d’insérer un grand nombre de fichiers dans une ou plusieurs tables d’une base de données SQL Server, et ce dans une fenêtre de temps relativement courte. Dernièrement, on m’a demandé d’alimenter une nouvelle base : composée seulement d’un petit nombre de tables, elle pouvait néanmoins être amenée à évoluer régulièrement (ajout ou suppression de tables et/ou colonnes), ce qui pouvait engendrer des coûts de développement importants à chaque évolution.

Pour mettre en place ce projet, je disposais de l’ETL de Microsoft : SSIS (Integration Service Catalog). Cet outil est très efficace pour gérer de manière native toute la chaîne d’alimentation : d’une source en passant par une transformation optionnelle des données, vers une destination. Cependant, pour le type de projet que nous allons découvrir dans cet article, les limites des composants natifs sont vites atteintes. Heureusement, et c’est l’une des grande force de SSIS, il est toujours possible de répondre à une problématique qui n’est pas gérée nativement avec un minimum de code .Net.

 

Contexte du projet

Nous recevons tous les jours des centaines de fichiers plats contenant une grande quantité de données. Ces données doivent être extraites puis analysées (afin de s’assurer de leur cohérence) avant d’alimenter les tables de la base SQL Server. Il existe plusieurs méthodes pour mettre en place la chaîne d’alimentation. Je vais vous présenter ici trois façons d’insérer des jeux de données avec SQL Server et nous verrons quels sont leurs avantages et défauts.

 

Méthode 1: Dataflow, chargement ligne par ligne

Pour alimenter des tables à partir de composants de SSIS, on utilise régulièrement des dataflow et des scripts SQL.

Le dataflow va contenir un composant source pour extraire les données des fichiers plats, d’un transformateur pour vérifier les données puis d’une destination pour alimenter la table cible.

Avantage

  • Simple à réaliser
  • Permet d’isoler en sortie du composant de destination OLE DB les lignes en erreur

 Défauts

  • L’enregistrement de données se fait  ligne par ligne, ce qui peut être très long si la source contient une grande quantité de données
  • On est dépendant de la structure des données au niveau de la source. Les colonnes doivent être mappées. Ceci oblige donc à créer une chaîne d’alimentation pour chaque table de destination. Ce développement peut devenir rapidement fastidieux si la base évolue régulièrement

Temps de chargement pour charger 1 million de lignes : 3 minutes

 

Méthode 2: Dataflow, chargement rapide (Bulk Copy)

 La méthode n°2 reprend le même concept que la méthode 1 à la différence près que la table de destination voit son mode de chargement changer en Fast Load

Comme son nom l’indique, ce mode a l’avantage de charger plus rapidement les données. Au lieu d’être insérées ligne par ligne, elles sont insérées en bloc.

Temps de chargement pour charger 1 million de lignes : 8 secondes

Avantage

  • Simple à réaliser
  • Chargement rapide
  • Modification du BatchSize pour obtenir de meilleures performances

 Défauts

  • On est dépendant de la structure de la source.
  • Les données étant insérées en bloc, ceci empêche d’isoler les lignes en erreur en sortie du composant destination OLE DB

 

Méthode 3 : C# SqlBulkCopy

Dans cette méthode, nous avons un script SQL afin de récupérer la liste des tables à alimenter. Puis nous utilisons un composant de SSIS “Script Task” qui permet d’écrire du code C#. Dans un premier temps, on récupère la liste des fichiers puis nous chargeons les données via la classe SqlBulkCopy. Cette dernière étape est encapsulée dans une boucle “ForEach” pour alimenter chaque table.

Définition

Le Bulk Copy est une opération de copie en bloc dans SQL Server. Elle permet de copier rapidement un bloc de fichiers volumineux dans une table d’une base de donnée SQL Server. Cette opération est utilisée à partir d’une classe “SQLBulkCopy”.

A l’aide de cette classe, on peut réaliser :

  • Une opération unique de copie en bloc
  • Plusieurs opérations de copie en bloc
  • Une opération de copie en bloc dans une transaction

Elle fait intervenir la notion de datatable dans laquelle nous pouvons renseigner n’importe quel type de source de données, un tableau de dataRows, et une instance d’une classe qui implémente l’interface IDataReader.

Comment l’utiliser :

1 – Récupération de la structure de la table

Dans un premier temps, nous récupérons la liste des colonnes de la table qui sera alimentée. Pour cela, nous utilisons la classe new List<ListElement>

Pour chaque colonne, nous identifions son nom, son type et sa position dans la table

2 – Lecture du fichier

Nous récupérons ensuite le nom complet du fichier et lisons toutes les lignes du fichier via la méthode ReadAllLignes

3 – DataTable

Nous créons un objet DataTable pour contenir les données que nous allons insérer. Le DataTable a des colonnes et des lignes comme la structure d’une table. Dans le code ci-dessous, nous ajoutons les colonnes dans la DataTable. Puis nous insérons les données formatées ligne par ligne. Il est important que la datatable et la table SQL de destination aient un schéma identique. Pour cela, nous utilisons un mappage dynamique pour chaque type de données si le schéma est différent.

Afin de notifier les erreurs de conversion de données, voici le code :

4 – SqlBulkCopy

 Enfin, nous copions le bloc de données en mémoire via l’opération SqlBulkCopy. On encapsule toutes nos insertions dans une transaction. De cette manière, soit l’intégralité du traitement sera correctement effectué soit il échouera dans son ensemble, permettant ainsi de garder la base de données dans un état cohérent. Pour utiliser une transaction, on l’ajoute dans l’option de l’objet.

Notez qu’ici nous devons créer explicitement un objet SqlConnection afin de créer un SqlTransaction. Nous devons créer manuellement la connexion, l’ouvrir, créer une transaction, puis transmettre la connexion et les objets de transaction à notre SqlBulkCopy.

Taille du lot

Par défaut, tous les enregistrements de la source seront écrits dans la table cible en un seul lot. Cela signifie que, à mesure que le nombre d’enregistrements dans la source augmente, la mémoire consommée par SqlBulkCopy augmentera. Si vous avez des limites de mémoire, vous pouvez réduire le nombre d’enregistrements écrits dans chaque lot. De cette façon, SqlBulkCopy écrira des lots plus petits dans la base de données et consommera donc moins de mémoire. Comme il y a plusieurs échanges avec la base de données, cela aura un impact légèrement négatif sur les performances. Pour définir la taille du lot, on utilise l’option BatchSize

    bulkCopy.BatchSize = 100000;

Cet exemple écrit 100 000 enregistrements dans chaque lot. L’opération se fait en boucle jusqu’à que toutes les données soient insérées dans la table de destination.

Temps de chargement pour charger 1 million de lignes : 6 secondes

Avantage

  • le bulk copy présente l’avantage de charger rapidement en mémoire les données. Sécurité du paramétrage, malléable et générique.

Inconvénient

  • On perd la logique du ETL en faisant de l’ELT : La transformation (le T de ETL) peut se faire après le chargement des données mais difficilement avant (à moins de l’intégrer dans le code C#)
  • Attention à l’utilisation de la mémoire. Dans le cas d’utilisation du bulk copy dans SSIS, ce dernier va charger la taille du batch size en mémoire. Or dans ma version de Visual Studio 2015, le mode débug était lancé en 32bits avec une limite à 4 Go ! D’une manière plus globale, on veille à maîtriser la consommation RAM des package SSIS afin de ne pas impacter les performances des autres programmes du serveur (typiquement le moteur SQL Serveur). L’idée est donc de ne charger qu’un nombre limité de données en bloc. Prenons par exemple, une ligne de données représentant une taille de 10 Ko. Si on fait la simple opération 4 000 000 Ko / 10 Ko = 400 000. Donc, afin d’éviter un dépassement 4Go de mémoire consommée par le package, , on peut ne charger 400 000 lignes en bloc dans une table SQL. L’ajout d’une boucle permet de charger la totalité des données sans risque

 

Conclusion

Au travers de cet article, nous avons vu les différentes méthodes pour charger les données. Les méthodes 2 et 3 sont les meilleures car elles chargent les données rapidement.  Malheureusement la méthode 2 a un gros impact sur le coût de développement car nécessite de réaliser autant de dataflow qu’il y a de tables dans la base de données. La méthode 3 est idéale pour automatiser la chaîne d’alimentation en cas d’évolution de la base de données. Elle nécessite cependant d’avoir des bases solides en C# et implique une plus grande difficulté en terme de maintenabilité. Enfin il est important de noter que la transformation des données peut être compliquée à gérer dans le code C# et qu’on privilégiera dans ce cas un traitement a posteriori.