Importer un fichier Excel avec SSIS dans une base SQL Server

Comment importer un fichier Excel dans une base de données SQL Server avec un package SSIS ?

Tutoriel pour importer un fichier Excel avec SSIS, soit les données depuis un fichier xslx dans une base de données SQL Server, étape par étape. Ici, la cible est une base de données SQL Server. Depuis Visual Studio, utiliser le composant natif Excel disponible dans les connexions sources du flux de données SSIS.

Importer un fichier Excel vers une base SQL avec SSIS

Pour charger un ou plusieurs fichiers Excel dans une base de données SQL Server avec Visual Studio, créer tout d’abord un package SSIS. Tout d’abord, ouvrir Visual Studio et créer un nouveau projet SSIS et un nouveau package comme expliqué dans le tutoriel dédié.

1. Préparer la table SQL et le fichier Excel à importer

La table de destination est la table des clients avec une structure basique, voici le code pour la créer.

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

Le fichier des clients à importer dans la table utilisée dans ce tutoriel est un fichier avec 48 lignes. Le fichier a exactement la même structure que la table cible, c’est à dire 5 colonnes. Télécharger le fichier exemple ici:

2. Créer un flux de données SSIS du fichier vers la base

Dans Visual Studio 2019, depuis l’espace de travail, dans la boîte à outils qui se trouve à gauche. Sélectionner Data flow Task. Ou tâche de flux de données en Français. Et faire glisser le composant sur l’espace de travail. Double cliquer pour l’éditer.

3. Se connecter au fichier avec le composant Excel Source

Depuis l’onglet Data Flow, sélectionner dans la boîte à outils le composant Excel Source et le faire glisser dans l’espace de travail.

Créer une nouvelle connexion au fichier Excel depuis le gestionnaire de connexions SSIS.

Choisir le gestionnaire de connexions pour les fichiers Excel depuis la liste.

Renseigner maintenant les éléments suivants:

1. Chemin du fichier Excel à importer dans la base SQL, c’est à dire: C:\data\Customers_Data.xlsx
2. La version d’Excel, choisir Excel 2007-2010.
3. Activer l’option pour les noms de colonnes dans la première ligne.

4. Configurer les pilotes et la connexion SSIS au fichier

Depuis le composant Excel Source, choisir l’onglet depuis la liste, comme ci-dessous. C’est le nom de l’onglet par défaut qui est affiché, c’est à dire Sheet1$. Il est aussi possible d’utiliser une requête SQL directement, notamment pour charger une plage spécifique de cellules et non le tableau entier.

La requête SQL pour Excel est de ce type. Elle sélectionne toutes les données stockées de manière contiguë dans le fichier. C’est à dire une plage de données Excel. Il suffit d’indiquer le nom de l’onglet suivi de la plage.

SELECT * FROM [Sheet1$A1:E]

Vérifier maintenant le mapping afin que toutes les colonnes soient détectées dans le package SSIS.

Utiliser le bouton Preview pour prévisualiser les données et valider le contenu.

4.1 Erreur SSIS avec les drivers 32-bits

A la première connexion au fichier, si l’erreur suivante s’affiche:

Could not retrieve the table information for the connection manager ‘Customers_Data.xlsx’.
Failed to connect to the source using the connection manager ‘Customers_Data.xlsx’

Ou encore ce message plus long mais explicite:

Exception from HRESULT: 0xC020801C
Error at PackageImportExcelFile [Connection manager « Customers_Data.xlsx »]: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: « Microsoft OLE DB Service Components » Hresult: 0x80040154 Description: « Class not registered ».

Error at Data Flow Task [Excel Source [2]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager « Customers_Data.xlsx » failed with error code 0xC0209302.

Alors vérifier à nouveau si les pilotes Excel sont bien installés et dans la bonne version.

4.2 Les versions de driver Excel 32-bit et 64-bit

Visual Studio fonctionne en 32-bits et SSIS fonctionne en 32-bit ou en 64-bit. Il faut donc installer les drivers correspondants au projet. C’est un vaste sujet très complexe car il n’est pas possible d’installer les versions 32-bit et 64-bit en parallèle des drivers en version 2016. Un article plus détaillé sur cette configuration spécifique sera écrit.

Si la version d’Office est installée en 64-bit, il est préférable d’éviter alors de réinstaller toutes les applications Office 365. Utiliser donc cette solution pour exécuter le package et charger le fichier Excel sans erreurs:

1. Installer le driver Excel 2010 en version 32-bits.
2. Installer les pilotes Excel Access Database Engine 2016 pour SSIS en version 64-bit.

Charger des cellules Excel spécifiques dans une table avec SSIS

5. Adapter le type des colonnes dans le composant SSIS

Par défaut, le type d’une colonne texte Excel est en DT_WSTR avec une longueur de 255 caractères. Depuis l’éditeur avancé pour Excel. Choisir le type de données DT_I8 pour la colonne CustomerID qui est de type entier dans la table.

• Ouvrir l’éditeur avec un clic-droit sur le composant Excel Source.
• Modifier uniquement la section Output Columns.

Choisir le type de données DT_WSTR pour toutes les autres colonnes et ajuster la longueur aux longueurs dans la table des clients.

Ouvrir le composant Excel Source Editor et changer la section Error Output come ceci pour ignorer les erreurs de troncations des données.

6. Configurer la table cible et importer le fichier Excel

Maintenant, dans la boîte à outils SSIS, sélectionner le type de destination dans lequel charger le fichier Excel. Depuis la partie Other Destinations, choisir OLE DB Destination. Et faire glisser le composant dans le data flow. Ensuite relier les deux composants, Excel Source à OLE DB Destination avec la flèche.

Ensuite, double cliquer sur OLE DB destination et choisir la base de données dans laquelle charger le fichier. Créer la connexion à la base de données au préalable. Le mapping des données est automatique car basé sur les noms des colonnes qui sont exactement identiques entre le fichier Excel et la table SQL Server.

7. Exécuter le package SSIS et contrôler la table

Enfin, cliquer sur le bouton exécution qui se trouve en haut de page. Ou appuyer sur F5, pour exécuter le package. L’exécution SSIS démarre pour importer les données du fichier Excel dans la base de données SQL Server. Les composants SSIS Excel Source et OLE DB Destination deviennent verts, c’est donc une exécution réussie.

Par ailleurs, le nombre de lignes utilisées est visible à l’écran de Visual Studio. Le signe d’avertissement en jaune indique une potentielle troncation des caractères. Pour supprimer le warning, vérifier à nouveau que les colonnes sources et cibles ont la même longueur dans les métadonnées. Par exemple NVARCHAR(20) pour SQL Server et un type de données compatible comme (DT_WSTR,20) pour SSIS.

Vérifier enfin que les 48 lignes présentes dans le fichier Excel sont chargées sans erreur avec deux requêtes exécutée depuis SSMS.

SELECT COUNT(*) AS [LINES]
  FROM [dbo].[Customers];

SELECT * 
  FROM [dbo].[Customers];

Conclusion sur l’intégration de fichiers Excel avec SSIS

Pour conclure, importer un fichier Excel avec SSIS n’est pas difficile en soi. Mais la configuration de l’environnement de développement et notamment la gestion des pilotes Excel avec les versions 32-bit et 64-bit rend la tâche plus complexe. Voici enfin un autre tutoriel pour charger un fichier texte au format CSV dans une base de données SQL Server avec un package SSIS.

Importer un fichier texte dans une base SQL avec SSIS