Comment importer un fichier texte ou CSV dans une base de données SQL Server avec un package SSIS ?
Les packages SSIS permettent d’importer différents types de fichiers, comme un fichier plat ou CSV dans une base SQL Server par exemple. 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. Ce tutoriel SSIS en Français explique pas à pas comment charger un fichier plat dans une base de données SQL Server. Le chargement se fait en deux parties composées de plusieurs étapes:
- Paramétrer le flux de données et la connexion au fichier
- Paramétrer la table de destination SQL Server
En effet, la première partie consiste à paramétrer la connexion au fichier texte, c’est é dire pointer son emplacement sur le disque. La deuxième partie est de créer et configurer la table de destination et la connexion à la base cible pour intégrer les lignes contenues dans le fichier.
Il faut bien sûr au préalable remplir certains prérequis, c’est à dire :
- Préparer le fichier source
- Se connecter à une base de données SQL Server avec SSMS
- Installer Visual Studio et les projets SSIS pour le développement du package SSIS.
Table des matières
1. Préparer le fichier source à importer dans la base
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.
2. Créer la table SQL Server cible depuis SSMS
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.
-- Creé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
3. Paramétrer la connexion au fichier texte
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.
3.1 Ajouter un flux de données
- 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.
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.
3.2 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.
3.3 Créer une nouvelle connexion au fichier texte
Créer et configurer une nouvelle connexion de type fichier plat, pour cela choisir les éléments suivants:
- Le nom de la connexion
- Le chemin du fichier
- L’encodage du fichier
- Le format, dans notre cas c’est délimité
- Le délimiteur de fin d’entête
- Si le nom des colonnes se trouvent dans la première ligne du fichier
3.4 Configurer les colonnes du fichier texte et vérifier les données
Il faut paramétrer les colonnes et vérifier les données:
- Valider ou modifier le délimiteur de lignes
- Choisir le délimiteur de colonne, c’est le caractère qui se trouve à la fin de chaque ligne de données
- 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.
3.5 Choisir le bon encodage pour le fichier 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.
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.
3.6 Valider et adapter le type et la longueur les colonnes
Une fois le bon encodage sélectionné, cliquer à nouveau sur l’onglet Columns et vérifier à nouveau les données.
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.
- CustomerID : int devient un DT_WSTR(8) car la colonne sera utilisée dans une colonne dérivée.
- FirstName : nvarchar(20)
- LastName : nvarchar(20)
- City : nvarchar(20)
- Country : nvarchar(50)
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.
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:
4. Configurer le composant destination OLE DB vers la base SQL Server
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.
4.1 Ajouter le composant colonne dérivée au flux de données
- Dans la boîte à outil SSIS, depuis la partie Général ou Common. Faire glisser la transformation Derived Column dans le data flow.
- Puis relier le composant Flat File Source à Derived Column comme sur l’image ci-dessous.
4.2 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).
5. Configurer le composant 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.
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.
6. Exécuter le package SSIS pour importer le fichier et vérifier les données dans la table
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.
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];
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).
7. Comment gérer les erreurs pendant l’import de fichiers plats avec SSIS
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.
Conclusion sur l’import de fichier texte avec SSIS
Pour conclure, ce tutoriel explique pas à pas comment importer un fichier texte dans une base SQL Server avec SSIS, à travers quelques étapes 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 aller encore plus loin, il est possible de la même manière de charger un fichier Excel dans une table avec SSIS.
