Importer des données Excel dans SQL Server via SSIS

Dans cet article, nous allons nous intéresser à SQL Server et SSIS. Je ne vais pas présenter SQL Server mais je peux au moins décrire rapidement SSIS. Il s’agit d’un composant de SQL Server qui permet d’extraire, de charger ou de transformer les informations contenues dans vos bases de données. Dans les anciennes version de SQL Server, vous le connaissez peut-être sous le nom de DTS pour Data Transformation Services.

Le but de cet article est de présenter comment avec SSIS nous pouvons mettre à jour automatiquement en quelques clics une base de données avec avec des données contenues dans des fichiers Excel.

Pour travailler sur une situation un minimum concrète, nous allons imaginer que nous avons une liste d’utilisateurs. Ces utilisateurs disposent tous d’un ordinateur car ils travaillent sur ma prochaine super production « My Super Film » (et autant vous dire qu’ils s’agit uniquement d’acteurs de haut niveau). Je dispose bien entendu d’un outil tel que SCCM pour gérer et administrer mon volumineux parc informatique… de 10 machines. 😉

Dans cet exemple, nous utiliserons 2 sources d’informations différentes :

• Un fichier Excel, extract de l’AD, permettant d’obtenir une liste d’utilisateurs avec leurs informations essentielles : nom, prénom, adresse email, domaine de référence, compte AD, fonction ou bien encore la localisation géographique.
• Un extract de notre outil de gestion de parc pour lister l’ensemble des postes de travail, leur adresse IP, le réseau auquel ils sont connectés, la marque, le modèle ou encore la configuration RAM de la machine ainsi que le dernier utilisateur à s’être identifié sur la machine .

Vu que le but est de montrer comment nous pouvons mettre à jour une base de données à partir de données Excel, nous allons supposer que la DSI de My Super Film refuse de nous laisser accéder en direct aux sources de données (db SQL…) mais nous met à disposition chaque semaine/mois les extracts au format Excel.

Afin de conserver nos informations à jour à tout moment, nous allons donc créer une base de données avec ces 2 fichiers Excel. Nous pourrons ainsi créer une base de données rapprochant les utilisateurs de leurs postes de travail.

NB : Vous retrouverez les fichiers Excel disponibles au téléchargement à la fin de l’article.

Vous devez disposer de SQL Server, SQL Server Mangement Studio et SQL Server Data Tools (correspond à une version gratuite et orientée SQL de Visual Studio).

Etape 1 – Créer une base de données vide

Créer une base de données « vide » sur votre serveur SQL. Dans mon cas, elle portera le nom de « mysuperfilm » et nous permettra de consolider les informations du parc informatique avec les différents utilisateurs.

Etape 2 : Importer les données

Nous allons importer manuellement les 2 fichiers Excel (1 table pour chaque fichier) via SQL Management Studio.

Cliquez du bouton droit sur votre base de données et sélectionnez successivement « Tasks » puis « Import Data« .

Notre source de données d’origine sera, dans un premier temps, le fichier Excel : « Utilisateurs.xls« .

Nous sélectionnons maintenant la destination des données que nous importons. Il s’agit bien entendu de la base de données que nous avons créé à l’étape 1 et qui, dans mon cas, s’appelle « mysuperfilm ».

La façon dont vous vous identifiez au serveur SQL dépend de vous. Dans mon cas, il s’agit d’un serveur SQL standalone. J’utilise donc l’authentification SQL Server.

Cette étape dépend de votre configuration. L’adresse IP du serveur SQL cible et les identifiants pour y accéder. Dans mon cas, j’utilise de l’authentification SQL Server.

• Sélectionnez « Copy data…« 

A cette étape, vous pouvez définir de nouveaux noms pour le nom des colonnes que vous allez importer dans la table SQL. Dans mon cas, je laisse les options par défaut.

• Sélectionnez « Run immediately » puis cliquez sur « Finish » (2 fois).

Si tout s’est bien déroulé, vous devriez avoir le message suivant… et de fait un nouvelle table à votre base de données.

Suivez la même succession d’étapes pour importer, votre second fichier « Gestion Parc SCCM.xls« .

Nous avons donc maintenant une base de données et 2 tables « SCCM » et « Userlist« .

Le souci ici est que nous avons réalisé cet import manuellement… Les données ne sont pas maintenues à jour automatiquement et nous n’allons pas nous amuser à vider les tables puis importer de nouveau le contenu actualisé chaque jour…

Nous allons donc utiliser SSIS afin d’importer de manière automatique nos données et ainsi actualiser l’ensemble de notre base de données. Bien évidemment, pour que ça marche, vos fichiers Excel doivent conserver le même structure d’une mise à jour à l’autre.

NB : Dans un cas réel d’utilisation, je vous recommander d’utiliser des fichiers ayant une structure la plus simple possible. Idéalement, des fichiers CSV.

Etape 3 – Création projet SSIS

Nous exécutons maintenant SQL Server Data Tools (Visual Studio 2010) et créons un nouveau projet « Integration Services« .

Nous arrivons sur une fenêtre qui doit ressembler à cela (selon la version que vous utilisez) :

Nous allons maintenant designer notre job SSIS pour qu’il importe automatiquement nos fichiers Excel au sein d’une base de données.

NB : Dans mon cas il s’agit de SQL Server 2012 et la table s’appelle « mysuperfilm ». Elle est hébergée sur une VM dont l’adresse IP est la suivante 10.0.0.12 (pour une meilleure compréhension des captures qui vont suivre).

Cliquez sur la partie droite et insérez une « Data Flow Task » sur votre espace  de travail.

Double cliquez dessus pour configurer en détails votre Data Flow Task.

Nous allons maintenant définir une première source d’entrée à insérer dans notre base de données : notre premier fichier Excel, « Utilisateurs.xls« . Pour ce faire, sélectionnez dans la Toolbox le composant « Excel Source » (dans « Other Sources« ) et insérez le sur votre espace de travail par glisser-déposer.

Cliquez du bouton du bouton droit sur ce composant et sélectionnez « Edit » puis le bouton « New » dans la nouvelle fenêtre qui apparaît. Nous allons définir le fichier Excel à importer dans notre base de données.

Notre fichier dispose effectivement de noms de colonnes, nous cochons donc l’option « First row…« . Mon fichier a été créé sous Excel 2013 mais a été enregistré au format XLS pour une meilleure compatibilité.

Si tout s’est bien passé, vous retourner à la fenêtre précédente et il devrait détecter le nom de notre (unique) feuille Excel. Vous pouvez éventuellement prévisualiser les données via le bouton « Preview« .

A ce niveau, vous avez toute latitude sur la façon et le type de données que vous souhaitez récupérer de ce fichier. Quelle feuille voulez-vous récupérer ? Voulez-vous renommer le nom des colonnes pour changer leurs noms une fois importées dans la table SQL ? Dans cet exemple, je laisse tout par défaut et je termine en cliquant sur « OK ».

Notre première source de données a été correctement identifiée, le symbole de « croix rouge » disparaît du composant « Excel source« .

Nous réalisons maintenant la même succession d’étape pour ajouter notre second source de données Excel, le fichier « Gestion Parc SCCM.xls ». Si tout se passe bien, vous devriez maintenant obtenir quelque-chose qui ressemble à ça :

NB : Vous pouvez renommer chacun des composants pour une meilleure lisibilité.

Les sources de données à importer sont maintenant explicitées. Ils ne nous reste plus qu’à lui dire où les importer !

Pour ce faire, nous allons utiliser le composant « OLE DB Destination » dans la section « Other Destinations« . Glisser-déposer ce composant sur votre espace de travail. Nous allons maintenant connecter ensemble chacun des différents composants en présence.

Pour ce faire, cliquez sur chaque composant Excel, cliquez sur la flèche bleu et connectez le à un composant « OLE DB Destination« . Vous devez réaliser la même connexion pour chaque composant Excel, il vous faudra donc 2 composants « OLE DB Destination« .

Nous allons maintenant configurer les 2 composants « OLE DB Destination » (que j’ai renommé) en cliquant du bouton droit puis en sélectionnant l’option « Edit ». Vous allez probablement avoir le message d’erreur suivant :

C’est normal ! 🙂

Il faut indiquer à chaque composant « OLE DB Destination » ce qu’il aura comme données en entrées. Pour ce faire, on connecte chaque composant « Excel source » à un composant « OLE DB Destination » en utilisant la flèche bleu.

C’est connecté ? Cliquez à nouveau du bouton droit sur un premier composant « OLE DB Destination » et sélectionnez « Edit » puis « New » sur la première fenêtre qui apparaît… et encore « New » sur la seconde.

Il nous faut maintenant configurer le serveur SQL auquel nous connecter.

Une fois revenu à la fenêtre suivant, vous devriez pouvoir choisir parmi la liste des tables qui ont été détectées dans votre base de données.

Notre première série de composants Excel Source + OLE DB Destination est désormais configurée. Cela nous permettra d’importer les données de la liste des utilisateurs.

Nous réalisons maintenant la même configuration pour le fichier gestion de parc. La configuration sera toutefois un peu plus rapide, il vous suffit de faire votre choix parmi les deux menus déroulants :

• Le serveur SQL auquel se connecter (c’est le même) ;
• La table dans laquelle nous allons déverser les données (il s’agit cette fois de la table SCCM).

NB : Comportement étrange, vous devez afficher la section « Mappings » pour que le bouton « OK » soit accessible (le mapping étant effectué par défaut). Vous pouvez bien entendu le modifier à cette étape en fonction de vos besoins.

Si vous avez tout configuré correctement, le symbole « croix rouge » disparaît des composants « OLE DB Destination« .

Etape 4 – Exécution et import des données

Si vous avez bien suivi jusque-là, vous pouvez maintenant importer vos données en exécutant le projet SSIS. Comme tout projet Visual Studio, utilisez le bouton vert lecture pour démarrer… 🙂

Vous devriez avoir une erreur ! 🙁

Pour information, l’environnement que j’utilise se présente comme suit : Windows Server 2012 + SQL Server 2012 + Visual Studio 2012 with SQL Server Data Tools.

Si vous avez rencontré une erreur, vous pouvez visualiser les détails de cette erreur, en cliquant dans la partie supérieure sur le bouton « Progress« .

Dans mon cas, il me dit en substance, qu’il préférerait que j’exécute mon package SSIS en 32 bits…

J’ignore pourquoi il ne veut pas l’exécuter en 64 bits ou pourquoi par défaut il s’est mis sur ce mode… Quoiqu’il en soi, nous pouvons modifier ce paramètre en cliquant du bouton droit sur le nom du projet (à droite) puis en sélectionnant « Properties« . Puis, modifier l’option Run64BitRuntime et passez là à « False« .

Exécutez de nouveau votre projet et ça devrait passer 🙂 !

Voilà, vous avez importé vos 2 fichiers Excel par simple clic dans votre base de données.

Bien sûr dans notre exemple, c’est plutôt rapide mais imaginez que vous ayez plusieurs fichiers qui comportent chacun 30 000 ou 50 000 lignes… L’exécution sera nettement plus longue !

Nous avons toutefois oublié quelque-chose… Vous venez d’exécuter votre package SSIS… Voyons voir ce qu’il a importé dans nos tables SQL.

Etape 5 – Eviter les doublons / Purger les tables

Notre package SSIS importe les nouvelles données qui se trouvent dans nos fichiers Excel… Toutefois chaque fois que nous exécutons le projet, les données sont importées encore… et encore… Nous n’avons donc plus 10 entrées mais 20 entrées (ou plus si vous avez exécuter le projet plusieurs fois).

Pour éviter ce désagrément, il convient de vider les tables avant d’importer les nouvelles données.

Nous revenons donc sur notre Data Flow Task et nous allons ajouter un composant « Execute T-SQL Statement Task« , connecté le à votre « Data Flow Task« , puis cliquez du bouton droit « Edit« .

• Cliquez sur le bouton « New » et configurer sur quel serveur votre requête SQL va s’exécuter ;
• Insérer la requête SQL suivant pour purger les 2 tables avant d’importer les données.

truncate table [mysuperfilm].[dbo].[SCCM];
truncate table [mysuperfilm].[dbo].[Userlist];

Vous pouvez maintenant exécuter à nouveau votre projet SSIS :

• Les données SQL contenues dans les tables Userlist et SCCM vont d’abord être purgées ;
• Puis les nouvelles données contenues dans les fichiers Excel vont être importées à nouveau.

Vous pouvez vérifier à nouveau vos tables SQL et constater qu’il n’y a plus de doublons cette fois-ci.

Conclusion

De cette façon, vous pouvez facilement actualiser et/ou mettre à jour une base de données SQL avec des données provenant de fichiers Excel. Il vous suffit par exemple d’écraser les anciens fichiers par les nouveaux et exécuter de nouveau votre projet SSIS pour mettre à jour les données.

Mieux encore, vous pouvez imaginer récupérer les fichiers Excel automatiquement par scripting… Il ne resterait alors plus qu’à exécuter le package SSIS pour actualiser les données… Encore mieux d’encore mieux, vous exécuter le package SSIS via une tâche cron chaque jour au moment qui vous arrange… Vos données sont alors actualisées automatiquement.

Bref, vous l’aurez compris il y a une multitude d’améliorations possibles.

Ressources

Comme d’habitude, vous trouverez-ci-dessous les 2 fichiers Excel que j’ai utilisé dans ce test.

1. Utilisateurs
2. Gestion Parc SCCM

Dans le prochain article je m’intéresserai à PowerPivot et je repartirai de ce même exemple de base de données.