Importer un fichier CSV dans une base SQL Server avec SSIS

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

Visual Studio 2022 permet entre autres d’importer un fichier CSV dans une base SQL Server avec SSIS. Souvent, les projets d’intégration de données nécessitent de charger des données depuis des fichiers plats. Soit des fichiers CSV, TXT, bruts ou encore sous d’autres formats moins courants, comme les fichiers à largeur fixe.

CSV signifie Comma-Separated Values (valeurs séparées par des virgules). Il s’agit d’un format de texte brut utilisé pour le stockage et l’échange de données tabulaires. Chaque ligne représente une ligne et les valeurs de chaque ligne sont séparées par des virgules. Ce tutoriel pratique SSIS explique comment importer des données CSV dans une table SQL Server ayant la même structure. à l’aide de Visual Studio 2019 ou 2022.

Dans ce tutoriel SSIS, nous suivrons 10 étapes simples
  1. Préparer le fichier source à importer avec SSIS
  2. Préparer la table SQL Server cible avec SSMS
  3. Créer le paquet SSIS avec un flux de données et le composant Flat File Source
  4. Créer une connexion au fichier source de données CSV
  5. Configurer les colonnes à importer et vérifier l’encodage à l’aide de Notepad++.
  6. Ajuster le type et la longueur des colonnes dans le fichier texte
  7. Ajouter et configurer la colonne dérivée SSIS pour convertir l’ID du client
  8. Configurez le composant SSIS OLE DB Destination pour qu’il pointe vers la base de données cible.
  9. Exécutez le paquet SSIS pour importer le fichier csv.
  10. Vérifier les données importées dans la table cible avec SSMS

Prérequis pour importer des données CSV dans une table SQL Server avec SSIS

Tout d’abord, pour importer des fichiers de données CSV dans une table SQL Server à l’aide d’un package Integration Services, vérifions quelques prérequis obligatoires :

1. Préparer le fichier source CSV à importer avec SSIS (Premium)

Commencer par télécharger le fichier utilisé dans l’exemple et créer la table cible des clients. Le fichier Customers_Data.csv contient 49 lignes, soit une ligne d’entête et 48 lignes de données générées automatiquement.

Pour télécharger les ressources : le fichier CSV et la requête de création SQL: Inscrivez-vous à notre formation vidéo SSIS complète (en Anglais).

SSIS Training Full Course (SQL Server Integration Services)
Formation complète SSIS en Anglais (SQL Server Integration Services)

2. Créer la table SQL Server cible depuis SSMS (Premium)

Le but de ce package est d’importer les 48 lignes sans erreurs dans la table des clients, dont le code de création est disponible ici. Exécuter le code dans une base d’exercices ou de développement avec SSMS.

Pour télécharger les ressources : le fichier CSV et la requête de création SQL: Inscrivez-vous à notre formation vidéo SSIS complète (en Anglais).

3. Créer le package et le flux de données pour importer le fichier CSV avec SSIS

Cette étape permet de gérer la connexion au fichier plat à importer dans la base SQL Server. Cette étape se fait dans un data flow SSIS.

  • Ouvrir Microsoft Visual studio 2019 et créer un nouveau projet SSIS ou ouvrir un projet existant.
  • Ouvrir un package existant à modifier. Sinon, faire un clic droit et ajouter un nouveau package au projet SSIS.
  • Depuis l’espace de travail, repérer la boîte à outils qui se trouve à gauche. Sélectionner Data flow Task et le glisser sur l’espace de travail puis double-cliquer dessus pour l’ouvrir.
Créer un flux de données pour importer un fichier texte dans une base SQL Server avec SSIS
Créer un flux de données pour importer un fichier texte dans une base SQL Server avec SSIS

Dans la boîte à outils SSIS, sélectionner maintenant Flat File Source, source de fichier plat et faire glisser le composant sur l’espace de travail. Puis double-cliquer dessus pour l’ouvrir et le paramétrer.

Ajouter la source SSIS de type fichier plat pour importer le fichier CSV dans la base SQL Server
Ajouter la source SSIS de type fichier plat pour importer le fichier CSV dans la base SQL Server

Utiliser des variables SSIS pour gérer les chemins des fichiers

Avec SSIS, à peu près tous les composants et propriétés sont paramétrables avec des variables. Il est une bonne pratique d’utiliser des variables pour gérer les chemins, les chaines de connexions et les paramètres SSIS changeants de manière efficace. Comme les changements d’environnements du développement vers le test ou la production par exemple.

4. Créer une connexion au fichier CSV à importer dans SQL Server

Créer et configurer une nouvelle connexion de type fichier plat, pour cela choisir les éléments suivants:

  1. Le nom de la connexion
  2. Le chemin du fichier
  3. L’encodage du fichier
  4. Le format, dans notre cas c’est délimité
  5. Le délimiteur de fin d’entête
  6. Si le nom des colonnes se trouvent dans la première ligne du fichier
Configurer une nouvelle connexion au fichier texte à importer avec le package SSIS
Configurer une nouvelle connexion au fichier texte à importer avec le package SSIS

5. Configurer les colonnes à importer dans SQL Server et vérifier l’encodage du fichier CSV

Il faut paramétrer les colonnes et vérifier les données:

  1. Valider ou modifier le délimiteur de lignes
  2. Choisir le délimiteur de colonne, c’est le caractère qui se trouve à la fin de chaque ligne de données
  3. Vérifier si les données et notamment les accents ou caractères spéciaux s’affichent correctement. Les lignes 29 et 32 ont une erreur car les accents sont mal interprétés. Il faut changer l’encodage.
Choisir le délimiteur de lignes et de colonnes puis vérifier les données
Choisir le délimiteur de lignes et de colonnes puis vérifier les données

Choisir le bon encodage pour le fichier CSV source avec Notepad++

Changer l’encodage du fichier source jusqu’à ce que les données s’affichent correctement. Pour trouver l’encodage du fichier, utiliser simplement Notepad++ et ouvrir le fichier. Il existe 2 possibilités pour vérifier l’encodage :

  • Depuis le menu Encodage (Encoding en Anglais)
  • En bas à droite du logiciel ou des informations sur le fichier sont affichées.
Vérifier l'encodage du fichier texte à importer depuis avec Notepad++
Vérifier l’encodage du fichier texte à importer depuis avec Notepad++

Pour charger le fichier texte sans erreurs, changer l’encodage et utiliser 65001 (UTF-8) au lieu de 1252 (ANSI – Latin I) proposé par défaut par SSIS.

Configurer la connection en UTF-8 comme le fichier source
Configurer la connection en UTF-8 comme le fichier source

6. Ajuster le type de fichier CSV et la longueur des colonnes dans SSIS

Une fois le bon encodage sélectionné, cliquer à nouveau sur l’onglet Columns et vérifier à nouveau les données.

Les données et les accents s'affichent maintenant correctement dans l'apercu du fichier texte
Les données et les accents s’affichent maintenant correctement dans l’apercu du fichier texte

Cliquer ensuite sur l’onglet Advanced. Ensuite, renommer éventuellement les colonnes et leur attribuer un type de données cohérent. Par défaut, SSIS utilise le type DT_STR avec une longueur de 50 caractères. Adapter donc le type de chaque colonne avec la longueur des champs de notre table des clients.

Pour télécharger les ressources et voir les types de données : Fichier plat, requête et types de données, inscrivez-vous à notre formation vidéo SSIS complète.

Utiliser donc le type DT_WSTR avec la bonne longueur pour éviter les erreurs de conversions plus tard dans le développement du package SSIS.

Utiliser le type DT_WSTR dès la connexion au fichier plat pour faciliter l'intégration
Utiliser le type DT_WSTR dès la connexion au fichier plat pour faciliter l’intégration

Note importante: les noms et les types de données de vos colonnes doivent correspondre aux noms et aux types de données de vos colonnes dans la base de données, ce qui facilitera grandement le mapping des données dans SSIS.

Vérifier maintenant les colonnes dans le composant Flat File Source:

Vérifier que les colonnes sont bien mappées dans le composant source
Vérifier que les colonnes sont bien mappées dans le composant SSIS source

7. Ajouter le composant SSIS Colonne dérivée et le lier à la source

En effet la configuration de la base cible permet de charger les données avec le bon format. C’est à dire un format compatible avec les données du fichier plat.

  • Dans la boîte à outil SSIS, depuis la partie Général ou Common. Faire glisser la transformation Derived Column dans le flux de données.
  • Puis relier le composant Flat File Source à Derived Column comme sur l’image ci-dessous.
Ajouter le composant colonne dérivée et le relier à la source du fichier plat
Ajouter le composant colonne dérivée et le relier à la source du fichier plat

Configurer une nouvelle colonne pour changer le type de l’identifiant du client

La colonne CustomerID est de type entier, il faut donc en créer une nouvelle basée sur celle du fichier. Pour la convertir en une nouvelle colonne SSIS de type Entier (DT_I8).

Créer une colonne dérivée pour convertir Customer ID en entier
Créer une colonne dérivée pour convertir Customer ID en entier

8. Configurer le composant SSIS cible OLE DB Destination

Pour ce faire, il faut au préalable créer une connexion à la base de données cible depuis le gestionnaire de connexions SSIS. Paramétrer ainsi le mappage des données dans l’onglet Mappings pour vérifier que les colonnes du fichier plat et de la table SQL Server correspondent bien. Utiliser la colonne Derived Column.CustomerID et non la colonne originale du fichier plat.

Utiliser la nouvelle colonne pour le numéro de client
Utiliser la nouvelle colonne pour le numéro de client

Mapping automatique avec SSIS

Note: Il est possible de mapper automatiquement les colonnes SSIS à l’étape suivante. S’assurer de nommer toutes les colonnes sources et cibles exactement de de la même manière.

9. Exécuter le package SSIS pour importer le fichier CSV

Une fois le flux de données complètement configurer, exécuter le package SSIS pour insérer les lignes dans la table des clients. Les 48 lignes sont traitées et intégrées dans la base.

Appuyer sur F5 ou cliquer sur Exécuter pour lancer le package SSIS
Appuyer sur F5 ou cliquer sur Exécuter pour lancer le package SSIS

10. Vérifier les données CSV dans la table SQL Server

Depuis SSMS, vérifier les données avec deux requêtes SQL. La table comporte bien 48 lignes, identiques au contenu du fichier.

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

SELECT *  
FROM   [dbo].[Customers];
Exécuter les 2 requêtes de sélection pour compter et afficher les lignes
Exécuter les 2 requêtes de sélection pour compter et afficher les lignes

Pour finir, exécuter une requête SELECT pour vérifier les données chargées dans la base. Cet exemple permet donc d’importer facilement un fichier texte dans une base SQL Server avec SSIS. Voici comment charger plusieurs fichiers plats dans une base avec une boucle SSIS (composant For Each File).

Conseil d’expert : Comment éviter les erreurs d’importation de fichiers csv avec SQL Server Integration Services ?

Une erreur courante est le code SSIS 0x80019002 qui signale que le nombre maximum d’erreur autorisées a été atteint. Il est possible en effet de paramétrer le nombre d’erreur tolérée via le paramètre MaximumErrorCount. Toutefois ce n’est pas recommandé sauf en cas de gestion automatique des erreurs et des reprises de chargement. Il y a trois éléments clefs à vérifier pour réussir l’intégration de fichiers et éviter les erreurs classiques:

  • Le séparateur de colonnes
  • Le séparateur de lignes
  • L’encodage du fichier texte

Ainsi, l’intégration des fichiers plats avec Integration Services et Visual Studio est puissante. Toutefois les erreurs sont courantes, à moins de préparer et vérifier en amont les types de données autant dans le fichier source que dans la table cible.

A propos d’importer un fichier CSV dans une base SQL Server avec SSIS

Pour conclure, ce tutoriel explique pas à pas comment importer un fichier CSV ou plat dans une base de données SQL Server avec un package SSIS. Et ceci à travers quelques étapes relativement simples. Une étape clef et parfois négligée est de bien préparer le nommage des colonnes depuis le fichier source vers le fichier cible. Pour facilier le mappage des colonnes dans le flux de données.

Pour aller encore plus loin, il est possible de la même manière de charger un fichier Excel dans une table avec SSIS.

Importer un fichier Excel avec SSIS dans une base SQL Server

Laisser un commentaire

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