Créer un data flow SSIS simple

Comment créer un data flow simple avec SSIS et Visual Studio 2019?

Ce tutoriel explique comment créer un flux de données SSIS, aussi appelé data flow SSIS en Anglais. Soit configurer un package pour extraire, transformer si besoin et enfin charger les données dans une autre base de données ou une autre table SQL. Un flux de données permet par exemple de copier des données d’une base de données MS SQL locale vers fichier plat ou inversement. Ou plus généralement d’une source de données vers une cible.

Créer un data flow SSIS basique pour transférer des données entre deux tables

SSIS est la brique ETL de la suite décisionnelle SQL Server BI, c’est à dire que les packages SSIS Microsoft servent à intégrer et traiter des données. Dans un package SSIS, les éléments de base sont les flux de contrôle et les flux de données. De plus, les suite logicielles d’intégration de données sont au cœur des projets de développement des entrepôts de données.

Un package SSIS est structuré comme suit:

Package SSIS (Package.dtsx par exemple)
- Flux de contrôle (Control Flow)
  - Flux de données (Data Flow)

C’est à dire qu’un package SSIS contient un ou plusieurs flux de contrôle, et les flux de contrôle contiennent eux-mêmes un ou plusieurs flux de données.

Prérequis pour le tutoriel est d’installer Visual Studio 2019 et les projets SSIS

Avant de démarrer ce tutoriel:

Commencer par installer Visual Studio 2019 et l’extension des projets SSIS.
Ensuite, créer un projet SSIS.
Créer une connexion à la base SQL Server depuis le gestionnaire de connexions SSIS.

1. Ajouter le flux de données au flux de contrôle et créer la table source

Pour commencer, depuis la fenêtre Visual Studio 2019, faire glisser sur le composant Data Flow Task sur l’espace de travail dans l’onglet Control Flow. Ou double-cliquer sur le composant depuis la boîte à outils SSIS. Puis double cliquer dessus pour l’éditer.

**Faire glisser le flux de données dans le flux de contrôle SSIS**

Ce tutoriel utilise cette table, créée dans ce tutoriel sur la création des tables MS SQL avec une clef primaire. Le code de création de la table source est disponible ci-dessous. Pour créer la table et insérer les données, se connecter à la base de données avec SSMS.

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

-- Insérer 4 lignes différentes
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 1, 'Ali','Ahmed','Cairo','Egypt');

INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 2, 'Johnny','John','Toronto','Canada');

INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 3, 'John','Doe','Mexico City','Mexico');

INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 4, 'Shu','Abbas','Paris','France');

2. Configurer la source de données OLE DB

Le curseur se trouve maintenant dans l’onglet Data Flow. Depuis cet onglet, sélectionner l’outil OLE DB Source et le faire glisser sur l’espace de travail.

Double cliquer sur le composant. Depuis le menu OLE DB Source editor, sur l’onglet Connection Manager. Puis sélectionner la base de données et la table source pour notre flux de données.

Configurer la table source des clients avec l'éditeur OLE DB et prévisualiser les données avec SSIS — Configurer la table source des clients avec l’éditeur OLE DB et prévisualiser les données

Puis aller dans l’onglet Columns et vérifier que les colonnes de la table sont présentes. Enfin sélectionner celles dont le contenu est à exporter. Effectuer le mapping en fonction des noms de colonnes. Par défaut les colonnes en sortie ont le même nom que les colonnes de la table source.

Vérifier le mappage des colonnes depuis l'onglet Columns — Vérifier le mappage des colonnes depuis l’onglet Columns

3. Créer une colonne dérivée SSIS

Créer une transformation avec le composant SSIS Derived Column, c’est à dire colonne dérivée. L’ajouter au flux de données, le relier à l’outil OLE DB Source puis double cliquer pour ouvrir le composant.

Ajouter puis relier et ouvrir le composant colonne dérivée SSIS

La modification des données se fait dans le menu Derived Column Transformation Editor. Insérer une colonne ou effectuer des modifications grâce aux différentes fonctions SSIS depuis la fenêtre de droite.

Ici le but est de transformer le nom de famille et le prénom dans une colonne cible avec les initiales de chaque client. Suivre les étapes suivantes:

Créer donc une nouvelle colonne appelée Initials.
Choisir ajouter comme une nouvelle colonne pour ne pas remplacer une colonne existante.
Remplir ensuite la formule
Ici le choix est d’ajouter une nouvelle colonne, avec une longueur de 8 caractères.

Formule utilisée dans le package récupère la première lettre du prénom, puis concatène le résultat avec la première lettre du nom de famille et enfin force le type avec une longueur de 8 caractères.

(DT_WSTR,8)(LEFT(FirstName,1) + LEFT(LastName,1))

L’équivalent du type de données NVARCHAR de SQL Server est le type DT_WSTR de SSIS.

4. Ajouter la destination OLE DB dans le data flow et créer la table cible

Pour stocker les données modifiées, commencer par créer la table cible avec le code SQL suivant. C’est donc la même structure de table avec une colonne supplémentaire pour contenir les initiales.

CREATE TABLE [dbo].[Customers_with_initials](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   [Initials] [nvarchar](8) NULL,
   CONSTRAINT [CustomersInitialsPKCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

Sélectionner ensuite le composant OLE DB Destination et le glisser sur l’espace de travail, dans le flux de données. Puis relier l’outil Derived Column à ce dernier. Enfin, double cliquer dessus pour l’éditer.

Ajouter la destination OLE DB au package SSIS

Ouvrir le menu OLE DB Destination Editor. Dans l’onglet connexion manager, choisir la table destinataire du chargement.

Configurer la table de destination pour contenir les données dans le data flow SSIS

Puis dans l’onglet Mappings, vérifier que les colonnes sont reliées correctement. Par défaut, l’éditeur de packages Integration Services permet par défaut de relier automatiquement les colonnes avec le même nom.

C’est une bonne pratique SSIS de renommer les colonnes en amont pour faciliter le mappage des données.

Mapper les colonnes à la main si nécessaire avec SSIS

5. Exécuter le package SSIS et vérifier les résultats avec SSMS

Maintenant, pour lancer le chargement, il faut exécuter le package depuis Visual Studio. Pour cela, cliquer sur le bouton Start ou directement avec le raccourci F5.

Exécuter le package SSIS avec un data flow basique

Pour vérifier les données dans les deux tables, il suffit d’exécuter ces requêtes de sélection depuis SSMS:

select * from [dbo].[Customers];
select * from [dbo].[Customers_with_initials];

Requête SSMS avec les deux tables et les initiales créent par le package SSIS

Voici donc comment créer un data flow SSIS basique pour copier des données d’une table à une autre et ajouter une colonne dérivée de deux colonnes existantes. Pour aller encore plus loin dans la manipulation de données avec SSIS, voici comment charger uniquement une plage de cellules depuis un fichier Excel vers une base de données SQL.