Exporter une table SQL Server vers un fichier Excel avec SSIS

Tutoriel pour développer un package SSIS et exporter les données SQL Server dans un fichier Excel.

Tutoriel pour exporter une table SQL Server vers un fichier Excel depuis un package SSIS. Pour gérer le contenu de cette table dans un fichier Excel, commencer par créer une solution, un projet et un package SSIS. Ensuite, suivre les étapes ci-dessous pas à pas pour exporter les lignes de la table dans le fichier Excel.

1. Vérifier ou installer la présence des drivers Excel 2010 ou 2016

Télécharger et installer au préalable le driver OLEDB Access Database Engin 2016 pour Excel. Avec cette configuration : Excel et le pack Office 365 installé en 64-bits et Visual Studio 2019, cette astuce fonctionne.

• Installer d’abord le driver Excel 2016 en 64-bit
• Puis installer le driver Excel 2010 en 32-bit

2. Préparer la table SQL à exporter et le fichier Excel

Avec SSMS, pour créer la table des clients, récupérer le code depuis ce tutoriel pour exporter une table dans un fichier CSV.

-- Créer la table des clients
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

-- Insérer 24 lignes avec les données des clients
INSERT INTO [dbo].[Customers] VALUES (1, N'Ali', N'Ahmed', N'Cairo', N'Egypt');
INSERT INTO [dbo].[Customers] VALUES (2, N'Johnny', N'John', N'Toronto', N'Canada');
INSERT INTO [dbo].[Customers] VALUES (3, N'John', N'Doe', N'Mexico City', N'Mexico');
INSERT INTO [dbo].[Customers] VALUES (4, N'Shu', N'Abbas', N'Paris', N'France');
INSERT INTO [dbo].[Customers] VALUES (5, N'Jeane', N'Raffin', N'Liushutun', N'China');
INSERT INTO [dbo].[Customers] VALUES (6, N'Legra', N'Leate', N'Błaszki', N'Poland');
INSERT INTO [dbo].[Customers] VALUES (7, N'Sullivan', N'Goadby', N'Xiaoguwei', N'China');
INSERT INTO [dbo].[Customers] VALUES (8, N'Tom', N'Ellams', N'Lop Buri', N'Thailand');
INSERT INTO [dbo].[Customers] VALUES (9, N'Trace', N'Fowell', N'Sriwing', N'Indonesia');
INSERT INTO [dbo].[Customers] VALUES (10, N'Christoffer', N'Rochford', N'Mburukullu', N'Indonesia');
INSERT INTO [dbo].[Customers] VALUES (11, N'Dru', N'Dunridge', N'Mistrató', N'Colombia');
INSERT INTO [dbo].[Customers] VALUES (12, N'Dud', N'Gravenor', N'Sandaogou', N'China');
INSERT INTO [dbo].[Customers] VALUES (13, N'Lori', N'Garshore', N'Jeziora Wielkie', N'Poland');
INSERT INTO [dbo].[Customers] VALUES (14, N'Cordy', N'Kentish', N'Vydreno', N'Russia');
INSERT INTO [dbo].[Customers] VALUES (15, N'Ancell', N'Wileman', N'Claresholm', N'Canada');
INSERT INTO [dbo].[Customers] VALUES (16, N'Elsworth', N'Chantrell', N'Sumberjo', N'Indonesia');
INSERT INTO [dbo].[Customers] VALUES (17, N'Beverly', N'Giraudy', N'Cigembong', N'Indonesia');
INSERT INTO [dbo].[Customers] VALUES (18, N'Tamas', N'Zavattiero', N'Jangkat', N'Indonesia');
INSERT INTO [dbo].[Customers] VALUES (19, N'Benedick', N'Schaffler', N'Itaparica', N'Brazil');
INSERT INTO [dbo].[Customers] VALUES (20, N'Leonard', N'Brimman', N'Moscow', N'Russia');
INSERT INTO [dbo].[Customers] VALUES (21, N'Morton', N'Capelle', N'Uk', N'Russia');
INSERT INTO [dbo].[Customers] VALUES (22, N'Larissa', N'Rawle', N'Tembayangan Barat', N'Indonesia');
INSERT INTO [dbo].[Customers] VALUES (23, N'Karalee', N'Wall', N'Clifden', N'Ireland');
INSERT INTO [dbo].[Customers] VALUES (24, N'Miller', N'Shakesbye', N'Gongnong', N'China');

Puis insérer les lignes suivantes dans la table des clients pour l’export. Ce jeu de données d’exemple sert à faire fonctionner le package et à obtenir un résultat concret dans le fichier Excel.

Pour faciliter le mapping des données, préparer un fichier Excel au format XLSX et ajouter les noms de colonnes au préalable. Ici les noms de colonnes sont les suivants:

1. CustomerID
2. FirstName
3. LastName
4. City
5. Country

Le fichier présenté ici est stocké ici : C:\data\Customers_Export.xlsx

3. Créer et configurer le flux de données de la table vers Excel

Depuis l’onglet Data Flow, ajouter les composants OLE DB Source et Excel Destination sur l’espace de travail. Double cliquer ensuite sur le premier pour le configurer.

Une fois dans la fenêtre des options OLE DB Source Editor, sélectionner la connexion à la base de données et la table des clients à exporter dans le fichier Excel. Enfin, valider pour fermer la fenêtre.

Les étapes suivantes permettent de configure le lien vers le fichier Excel exporté.

4. Configurer l’export vers le fichier Excel

Après avoir configurer le composant OLE DB Source, utiliser le fichier Excel vide avec les entêtes pour recevoir les données. Pour exporter des données vers le fichier Excel. Relier le composant OLE DB Source au composant Excel Destination via la flèche verte. Créer ensuite une connexion Excel comme suit, depuis le gestionnaire de connexions SSIS.

Puis double cliquer sur le composant Excel. Dans Excel Destination Editor choisir la connexion au fichier Excel créée auparavant. Choisir également dans quel onglet exporter les données, ici c’est l’onglet par défaut en Anglais, c’est à dire Sheet1$.

Cliquer ensuite sur Mappings, si nécessaire, faire correspondre manuellement les colonnes. Pour cela, cliquer sur Ignore et puis choisir colonne adéquate. Valider pour fermer la fenêtre.

5. Exécuter le package SSIS pour exporter la table et vérifier les données dans Excel

Enfin, exécuter le chargement. Pour cela, appuyer sur la touche F5, les différents composants, c’est à dire le flux de contrôle, le flux de données, la source et la cible deviennent verts. Le nombre de lignes chargées s’affiche et il est correct, soit 24 lignes. Le chargement s’est donc effectué correctement.

Ouvrir enfin le fichier Excel exporté et contrôler le contenu qui doit être identique à celui de la table des clients, c’est à dire dbo.Customers.

Conclusion sur l’export depuis SQL Server vers Excel avec SSIS

Voici donc comment exporter une table depuis SQL Server dans un fichier Excel avec un package SSIS. L’export de données basique est relativement rapide et facile à mettre en place. Pour gérer au mieux les types de données il est conseillé d’utiliser des modèles de fichiers à copier avant l’exécution de la tâche d’export. Il est également possible d’exporter une table vers un fichier avec l’assistant import et export SQL Server.

PilotesAccess Database Engine 2016 et import Excel avec SSIS