SQL Server 2005 Integration Services : Import/export Excel

Fleur-Anne BLAIN

Ce tutoriel a pour objectif de présenter la création de packages SSIS qui permettront soit d'importer des données d'un fichier Excel vers une base de données SQL Server 2005, soit d'exporter des données de cette même base vers un fichier Excel. La transformation de données sera aussi nécessaire. ♪

- Titre : SQL Server 2005 Integration Services : Import/export Excel

- Auteur : Fleur-Anne BLAIN

- Parution : 29 avril 2008

- Mise à jour : 29 avril 2008

- Licence : Copyright ® 2011 Fleur-Anne BLAIN. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Introduction

- I. Présentation des exemples

- I-A. Import de nos données articles

- I-B. Export de nos données Employés

- II. Import Excel vers SQL Server 2005

- II-A. Création et configuration de notre source de données : Excel Source

- II-B. Transformation des données

- II-C. Destination des données : la table Articles sur SQL Server 2005

- III. Export SQL Server 2005 vers Excel

- III-A. Source SQL Server 2005

- III-B. Destination : Fichier Excel Employés

- Conclusion

- Liens utiles

- Remerciements

Introduction

Excel reste un outil des plus utilisés en entreprises y compris pour l'import/export de données. C'est pourquoi il est intéressant d'en découvrir le fonctionnement avec SQL Server Integration Services : SSIS. Nous nous baserons sur deux exemples simples. Le premier concerne un fichier Excel d'articles à importer dans une base SQL Server 2005 et le second sur une table employés à exporter vers un fichier Excel.

Dans ce tutoriel, nous aborderons la création de projets BI avec SQL Server 2005, la création et configuration des sources de données, quelques transformations de base et la création et configuration des destinations de données.

Dans un premier temps, nous présenterons nos deux exemples qui nous serviront pour ce tutoriel. Puis, nous aborderons l'exemple d'import de données d'un fichier Excel vers notre base de données SQL Server 2005 et nous terminerons par l'exemple d'export de données avec les mêmes outils.

I - Présentation des exemples

Prérequis : d'un point de vue logiciel, vous devez disposer de Microsoft Visual Studio 2005 et Microsoft SQL Server 2005 au minimum (en dehors des versions Express qui ne prennent pas en charge SSIS). D'un point de vue connaissance, si vous n'êtes pas familiarisés avec SSIS, je vous conseille de lire ce tutoriel :

Présentation de SQL Server Intgération Services : SSIS, car nous ne reviendrons pas sur toutes les bases de l'outil.

I-A - Import de nos données articles

Avant d'importer des données, il faut bien que notre fichier Excel en contienne. Ci-dessous, notre petit exemple d'articles que nous allons importer :

De plus, avant d'importer quoi que ce soit, il nous faut créer la table qui accueillera nos informations. La base de données utilisée est Microsoft SQL Server 2005.

Script table Article

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Article](
[Id_Article] [int] NOT NULL,
[Nom_Article] [nvarchar](50) NOT NULL,
[Quantite_Article] [int] NULL,
CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED
(
[Id_Article] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Pour résumer notre premier flux de données :

I-B - Export de nos données Employés

Dans cet exemple, nous partons de la base de données pour en exporter les données vers un fichier Excel. Dans un premier temps, nous créerons notre table, dans laquelle nous insérons nos données comme suit :

Script table employés

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employe](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Nom] [nvarchar](50) NOT NULL,
[Prenom] [nvarchar](50) NULL,
[Sexe] [nvarchar](1) NULL,
[Fonction] [nvarchar](50) NULL,
CONSTRAINT [PK_Usr] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Pour résumer notre second flux de données :

II - Import Excel vers SQL Server 2005

Après avoir lancé Microsoft Visual Studio, nous créons un nouveau projet de type Integration Services. Cela nous donnera accès à toute la fameuse boite à outils SSIS. Nous allons développer cette partie par étape : création et configuration de la source de données, transformation, et enfin création et configuration de la destination.

II-A - Création et configuration de notre source de données : Excel Source

Dans un premier temps, il nous faut spécifier quel type de flux de contrôle nous allons utiliser. Nous choisissons tâche de flux de données comme suit :

Ensuite, nous passons à l'onglet flux de données. Pour notre import, la source de données est un fichier Excel. Donc nous choisissons bien évidemment la source de données Excel.

Passons a la configuration de notre source de données. Nous double cliquons sur notre composant source.

Notons que la version d'Excel est maximum 2003. Une version d'Office 2007 est réservée à SQL Server 2008.

Concernant le fichier Excel, nous pouvons voir qu'il n'y a qu'une case à cocher ou non si la première ligne du fichier à importer est un entête.

Nous avons donc sélectionné notre fichier Excel à importer, laissé la case cochée, car nous disposons des titres de colonnes dans notre fichier, nous choisissons la feuille 1 (sheet1$). Afin de visualiser si notre connexion est valide, nous pouvons prévisualiser le contenu du fichier. S'il y a en effet quelque chose dans la prévisualisation (comme suit), c'est déjà bon signe. De plus, au niveau du manager de connexion, une connexion s'est ajoutée pour notre source de données Excel. S'il y a des modifications de chemins ou autres, il nous suffira d'en modifier les propriétés.

Par la suite, nous passons à l'onglet colonnes. C'est ici que nous choisissons nos colonnes à importer. Dans notre cas, nous importons tout :

Remarque : parfois, la clef d'un tuple dans une base de données est générée automatiquement. Il est donc plus judicieux dans ces cas-là de ne pas importer la colonne identifiant, car cela causerait des conflits à l'import.

II-B - Transformation des données

Nous sommes obligés afin de contrôler l'intégrité des données à importer de passer par un intermédiaire de transformation. Nous utiliserons donc le composant conversion de données, qui permet surtout de vérifier les types des données. En effet, ce composant effectue un mapping afin que le type des données à insérer soit identique au type de données déclaré dans notre table.

Nous relions donc nos deux composants et double cliquons sur notre composant conversion de données afin de le configurer. Nous sélectionnons donc nos trois colonnes et nous choisissons les types de correspondances par rapport à ceux de notre table Articles. Les correspondances sont données en cas d'erreur pas SQL Server qui fait lui-même ses suggestions.

II-C - Destination des données : la table Articles sur SQL Server 2005

Nous sélectionnons notre destination OLE DB. Puis, nous relions notre composant de conversion de données vers notre destination, car c'est un flux de données que nous manipulons. Nous ouvrons notre destination afin de la paramétrer.

Pour le paramétrage de notre destination, nous devons spécifier une base de données, la chaine de connexion, la table destination dans un premier temps.

Comme chaque type de connexion, une connexion à notre base de données s'est créée dans notre manager de connexions.

Au niveau de l'onglet mapping, nous choisissons les colonnes commençant par copy of. En effet, ce sont les colonnes en sorties de transformations, et donc avec le bon type de données que nous allons insérer.

Afin de lancer l'import, nous exécutons le package (F5). Tout apparait vert, avec 8 lignes (autant que dans notre fichier) insérées :

Histoire de vérifier, nous pouvons constater aussi l'import au niveau base de données, dans notre table article :

III - Export SQL Server 2005 vers Excel

Nous allons aborder la même démarche, de la source à la transformation jusqu'à notre destination de données. Cette fois-ci, nous utilisons notre exemple employés décrit précédemment.

III-A - Source SQL Server 2005

Nous reprenons les mêmes étapes que précédemment, soit la création d'un projet BI Integration Services, l'ajout d'une tâche de flux de données au niveau flux de contrôle. Nous ajoutons en source un composant source OLE DB auquel nous configurons notre base de données SQL Server en guise de source :

Nous gardons toutes les colonnes de notre table pour l'export Excel. Notre source de données a été ajoutée à notre manager de connexion.

III-B - Destination : Fichier Excel Employés

Nous n'appliquons pas de transformation dans ce cas-là. Mais si l'on souhaite mettre tout en majuscule, ou bien modifier le format de date, etc., il est possible d'effectuer une transformation soit par composant soit par script entre la source et la destination. Nous poursuivons en ajoutant le composant destination Excel que nous ouvrons.

Si l'on teste notre package, on constate que 6 lignes sont insérées :

Toujours pour vérifier nos dires, nous pouvons ouvrir notre fichier Employés pour constater l'export effectif :

Conclusion

Nous venons de manipuler l'import/export avec Excel et SSIS. Il est très facile d'obtenir un résultat sans développement ou très peu, car beaucoup de possibilités de transformations sont incluses dans SSIS. Il est possible d'exécuter localement ce genre de traitement de données ou de les automatiser avec SQL Server Agent. En général, ce type de traitement étant courant, tout est automatisé. Je vous laisse le soin d'observer, tester toutes les possibilités non encore présentées de SSIS.

Liens utiles

- Présentation de SQL Server Integration Services : SSIS

- Automatisation de l'exécution d'un package SSIS sur Microsoft SQL Server 2005

- Forum Microsoft BI

Remerciements

Je remercie Adrien Artero pour ses corrections.