Chargement efficient de fichiers avec SSIS

En règle générale, lorsqu’il faut charger plusieurs fichiers ayant le même format, tout développeur SSIS utilise une simple boucle en récupérant les fichiers un à un pour les charger séquentiellement en base.  Dans la majorité des cas, tout cela fonctionne très bien tant que vous pouvez vider le répertoire dans lequel ils sont stockés.

Prenons un cas d’usage très simple, votre société possède un réseau de distribution de 333 boutiques (c’est 666 /2 damned çà sent le roussi). Chacune d’elles dépose en fin de journée un fichier. Ces fichiers étant consommés par différents systèmes, ils  sont centralisés dans un dossier partagé sur lequel vous n’avez que le droit de lecture. Au démarrage de votre solution vous intégrer donc 333 fichiers, au bout d’une quinzaine de jours vous intégrer toujours 333 fichiers mais vous aller parcourir un dossier d’archivage d’environ 5 000 fichiers et qui devrait atteindre les 10 000 fichiers au bout d’un mois.

L’implémentation type que je retrouve chez mes clients est la suivante :

  • création d’une d’administration, permettant de lister les fichiers déjà chargés

  • mise en place d’un package SSIS décomposé comme suit :
  1. Une requête permettant de vider la table de staging et de supprimer de la table d’administration tous les enregistrements au statut « en cours ».
  2. Une boucle permettant de lister les fichiers contenus dans le dossier d’archivage et réalisant pour chaque fichier les opérations suivantes :
  • tester si le fichier n’a pas déjà été importé. Pour cela on se base sur la table d’administration citée précédemment
  • charger, le cas échéant, le fichier dans une table de staging
  • mettre à jour la table d’administration avec la date et l’heure de chargement du fichier

Nous avons donc un package ressemblant à ceci :

Les résultats obtenus avec ce type d’implémentation sont les suivants :

Taille dossier d’archivage 333 5 000 10 000
Temps chargement des 333 fichiers 34s 2mn49s 8mn

N.B : Les tests ont été effectués sur mon portable : un Dell Latitude, Core i5-2520M CPU @2.5GHz, 8Go de RAM, DD de 500Go SATAII 7200 rpm, Windows Server 2008R2 SP1 64 bits,  SQL Server 2012 avec des fichiers d’une taille moyenne de 2Ko.

Le constat est sans appel, les performances se dégradent très rapidement, pourtant la quantité et le volume de données intégrés reste les mêmes. Voyons désormais comment améliorer les performances de façon significative.

La boucle sur les fichiers est inévitable, nous ne pouvons pas faire autrement, que de les lire un à un afin d’en récupérer le contenu. L’optimisation se situe donc sur le parcours du dossier et l’identification des fichiers à intégrer.

Pour cela, nous allons lister les fichiers à intégrer, dans un recordset, à l’aide d’un Data Flow Task. Il nous faut donc :

1. Récupérer la liste des fichiers avec leur date de dernière modification à l’aide d’une Script Component. Un petit peu de code C# permet d’effectuer cette tâche sans encombre :

DirectoryInfo di = new DirectoryInfo(Variables.fichierPath);
foreach (FileInfo fi in di.GetFiles(Variables.fichierPattern))
{
Output0Buffer.AddRow();
Output0Buffer.Fichier = fi.FullName;
Output0Buffer.DateDerModif = Convert.ToInt64(fi.LastWriteTime.ToString(« yyyyMMddhhmmss »));
}

2. Récupérer la dernière date de chargement des fichiers dans la table d’admin. Pour cela une simple requête SQL suffit.

select Fichier,cast(format(max(DateChargement),’yyyyMMddhhmmss’) as bigint) as DateDerChargement
from adm.FichierCharge
group by Fichier
order by Fichier

3. Fusionner les deux sources de données avec une jointure externe

4. Identifier les fichiers à intégrer à l’aide d’un Conditionnal Split. Il suffit de ne filtrer que les enregistrements n’ayant pas de date de chargement ou dont la date de dernière modification est supérieure ou égale à la dernière date de chargement.

5. Nous avons alors l’ensemble des fichiers que nous devons intégrer. Cette liste est insérée

a. dans la variable listeFichier, de type Object, à l’aide d’un Recordset Destination

b. dans la table d’administration; chaque fichier est inséré avec le statut « en cours »

Votre Data Flow Task doit ressembler à  ceci :

Passons désormais à l’intégration des fichiers dans la table de staging. Cette étape est réalisée par une boucle permettant d’énumérer la liste des fichier issue du Recordset listeFichier à l’aide d’un Foreach Loop Container. Pour chaque occurrence, on modifie, par expression, la chaîne de connexion au fichier et on insère le contenu du fichier dans la table de staging à l’aide d’un Data Flow Task.

Une fois la boucle d’intégration terminée, il ne reste plus qu’à mettre à jour la table d’administration ; tous les fichiers au statut « en cours » passent à « ok ».

Au final, votre package doit avoir cette allure :

Comparons désormais les temps d’exécution de notre implémentation par rapport à la méthode « traditionnelle »

Taille dossier d’archivage 333 5 000 10 000
Temps chargement des 333 fichiers 23s 23s 23s

Le résultat est sans appel.

« Efficient Dynamics », rien de tel que la devise d’un constructeur automobile allemand à transposer en BI; tel devrait être le credo de tout package SSIS.

L’optimisation n’a de limite que l’imagination !

2 réflexions sur “Chargement efficient de fichiers avec SSIS

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