Importer un fichier texte dans une base SQL avec SSIS

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

C’est possible avec la suite Microsoft Business Intelligence et SSIS en particulier. 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

Importer un fichier texte dans une base SQL Server avec SSIS

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.

1. Paramétrer le fichier, la table cible, le flux de données et la connexion au fichier plat

Il faut bien sûr au préalable remplir les prérequis, c’est à dire préparer le fichier source, se connecter à une base de données SQL Server avec SSMS et avoir installer Visual Studio et les projets SSIS pour le développement du package SSIS.

Préparer le fichier source et la table cible

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.

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

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.
Nouveau flux de données SSIS pour charger un fichier texte dans une base SQL
Nouveau flux de données SSIS pour charger un fichier texte dans une base SQL

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 de fichier plat dans le flux de données SSIS (Flat File Source)
Ajouter la source de fichier plat dans le flux de données SSIS

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.

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 CSV dans le package SSIS
Configurer une nouvelle connexion au fichier CSV dans le package SSIS

Configurer les colonnes 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

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 d'un fichier texte avec Notepad++
Vérifier l’encodage d’un fichier texte 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.

Le fichier est encodé en UTF-8
Le fichier est encodé en UTF-8

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
Les données et les accents s’affichent maintenant correctement

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

2. Configurer le composant destination OLE DB et la connexion à la base depuis SSIS

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

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

Configurer maintenant le composant 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.

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

Eviter les erreurs d’importation 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.

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.

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

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*