Importer un fichier texte dans une base SQL Server avec SSIS

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:

  1. Paramétrer le flux de données et la connexion au fichier
  2. 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 :

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.

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

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:

  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

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:

  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

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

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.

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.

  1. CustomerID : int devient un DT_WSTR(8) car la colonne sera utilisée dans une colonne dérivée.
  2. FirstName : nvarchar(20)
  3. LastName : nvarchar(20)
  4. City : nvarchar(20)
  5. 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.

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

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

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

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.

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.

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.

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

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

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.

Laisser un commentaire

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

Retour en haut