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.

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.

Créer la table à exporter depuis SSMS
Exécuter le code pour créer la table des clients

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
Préparer la ligne d'entête du fichier Excel cible pour mapper les colonnes depuis SSIS
Préparer la ligne d’entête du fichier Excel cible pour mapper les colonnes depuis SSIS

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.

Ajouter les composants OLE DB Source et Excel Destination au flux de données SSIS
Ajouter les composants OLE DB Source et Excel Destination au flux de données SSIS

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.

Sélectionner la connexion et la table source à exporter vers Excel
Sélectionner la connexion et la table source à exporter vers Excel

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.

Ajouter une connexion au fichier Excel depuis le gestionnaire de connexions
Ajouter une connexion au fichier Excel depuis le gestionnaire de connexions

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$.

Configurer le composant SSIS pour exporter les données dans le fichier Excel
Configurer le composant SSIS pour exporter les données dans le fichier Excel

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.

Vérifier le mapping SSIS avant d'exporter la table dans le fichier Excel
Vérifier le mapping SSIS avant d’exporter la table dans le fichier Excel

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.

Exécuter le package SSIS pour exporter la table dans un fichier Excel
Exécuter le package SSIS pour exporter la table dans un fichier Excel

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.

Le contenu du fichier Excel est conforme à la table source
Le contenu du fichier Excel est conforme à la table source

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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *