Pivoter les lignes en colonnes avec SSIS Pivot

Transformer les lignes d’une table SQL en colonnes dans un package avec la transformation SSIS Pivot.

SQL Server permet de pivoter les lignes d’une table SQL en colonnes, mais comment réaliser l’opération équivalente avec un package SSIS? Voici donc un tutoriel pour paramétrer le composant SSIS Pivot et transformer les lignes d’une table SQL en colonnes, soit une colonne cible par mois de l’année. Cet exemple simple utilise une seule colonne non pivotée car le composant ne permet pas de pivoter plus d’une colonne sans utiliser l’éditeur avancé et les numéros de colonnes internes à SSIS (LineageID).

Pivoter les lignes en colonnes dans un package SSIS

Le pivot de données est couramment utilisé dans les tableaux croisés dynamiques par exemple. Ce tutoriel Integration Services en 4 grandes étapes explique donc comment pivoter une table SQL basique depuis une structure verticale en lignes vers une structure horizontale en colonnes.

1. Préparer la table et les données sources en colonnes

Tout d’abord, créer les 2 tables utilisées, soit une table source en lignes et la table de destination en colonnes.

1.1 Créer la table source en lignes

En effet, créer la table des ventes avec les valeurs mensuelles en lignes.

CREATE TABLE [dbo].[Sales_Monthly_In_Lines]
(
  [YearID]      nvarchar(20) NOT NULL,
  [MonthName]   nvarchar(12) NOT NULL,
  [SalesAmount] numeric(10,2)  NULL
  CONSTRAINT [SalesMonthlyLinesPK] 
    PRIMARY KEY CLUSTERED (
      [YearID], [MonthName] ASC
    )
);
GO

1.2 Insérer le jeu de données

Insérer un jeu de données pour remplir la table sur 2 années.

INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'01_January', 221.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'02_February', 2658.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'03_March', 2485.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'04_April', 6302.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'05_May', 7605.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'06_June', 5288.50 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'07_July', 3335.20 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'08_August', 5216.40 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'09_September', 4372.20 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'10_October', 9815.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'11_November', 5479.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'12_December', 9212.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'01_January', 7575.90 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'02_February', 6662.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'03_March', 1464.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'04_April', 7011.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'05_May', 3978.30 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'06_June', 4101.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'07_July', 5964.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'08_August', 2619.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'09_September', 4482.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'10_October', 3874.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'11_November', 3171.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'12_December', 1610.70 );

1.3 Créer la table cible en colonnes

Créer enfin la table pour recevoir les données après le pivot qui contient chaque mois dans une colonne dédiée.

CREATE TABLE [dbo].[Sales_Monthly_In_Columns]
(
  [YearID]     nvarchar(20) NOT NULL,
  [01_January]    numeric(10,2)  NULL,
  [02_February]   numeric(10,2)  NULL,
  [03_March]      numeric(10,2)  NULL,
  [04_April]      numeric(10,2)  NULL,
  [05_May]        numeric(10,2)  NULL,
  [06_June]       numeric(10,2)  NULL,
  [07_July]       numeric(10,2)  NULL,
  [08_August]     numeric(10,2)  NULL,
  [09_September]  numeric(10,2)  NULL,
  [10_October]    numeric(10,2)  NULL,
  [11_November]   numeric(10,2)  NULL,
  [12_December]   numeric(10,2)  NULL
  CONSTRAINT [SalesMonthlyColumnsPK] 
    PRIMARY KEY CLUSTERED (
      [YearID] ASC
    )
);
GO

2. Configurer le composant SSIS Pivot

Cette partie du tutoriel est la partie au cœur du sujet, car il s’agit de paramétrer le flux de données. Au préalable, il faut bien sûr créer un package dans un projet SSIS et configurer une connexion à une base SQL Server.

2.1 Ajouter les composants au flux de données SSIS

Ajouter ces 3 composants dans un flux de données existant, ou en créer un nouveau.

  1. Une connexion de type OLE DB Source
  2. Une transformation Pivot
  3. Une connexion de type OLE DB Destination
Ajouter les composants OLE DB avec le Pivot SSIS pour transformer les lignes en colonnes
Ajouter les composants OLE DB avec le Pivot SSIS

2.2 Sélectionner la table source du flux SSIS

Sélectionner la table Sales_Monthly_In_Lines comme source du composant OLE DB.

Choisir la table source avec les données en lignes créée à la première étape
Choisir la table source créée à la première étape

2.3 Configurer le Pivot SSIS

Configurer maintenant le PIvot SSIS comme suit:

  1. Choisir la clef de pivot dont les valeurs deviennent le nom des Nouvelles colonnes : MonthName
  2. Choisir la colonne clef qui restera présente en sortie : YearID
  3. Sélectionner la colonne source qui contient les valeurs : SalesAmount
  4. Entrer la liste des colonnes créées par le Pivot, séparées par une virgule : 01_Janvier à 12_Décembre
  5. Générer les colonnes pour remplir la zone de droite avec les nouvelles colonnes pivotées.

La liste des noms de colonnes est la suivante, sans espaces car le composant SSIS Pivot utilise les espaces dans les noms de colonnes générés automatiquement.

01_January,02_February,03_March,04_April,05_May,06_June,07_July,08_August,09_September,10_October,11_November,12_December 
Configurer le Pivot SSIS pour transformer les lignes en colonnes
Configurer le Pivot SSIS pour transformer les lignes en colonnes

Il n’est possible de renseigner une seule colonne Clef pour pivoter les colonnes. Pour pivoter une table avec plus de colonnes de regroupement, utiliser l’éditeur avancé et les identifiants de colonnes SSIS, ceci fera l’objet d’un autre tutoriel dédié au Pivot avec plusieurs colonnes fixes.

2.4 Configurer la table destination

Sélectionner la table de destination pour stocker dans la base SQL Server les données après le Pivot. C’est la table dbo.Sales_Monthly_In_Columns crée à l’étape 1.3 du tutoriel.

Choisir la table SQL pour stocker les données après le pivot SSIS
Choisir la table SQL pour stocker les données après le pivot SSIS

Depuis l’onglet Mappings, relier chaque colonne source avec la colonne cible correspondante, soit les 12 colonnes générées pour chaque mois de données.

Relier les colonnes pivotées avec les colonnes dans la table SQL Server
Relier les colonnes pivotées avec les colonnes dans la table SQL Server

Une fois tous les composants configurés, Visual Studio n’affiche aucune erreur ni avertissement, dans le cas contraire, vérifier les liaisons et les type de données.

Package SSIS avec le Pivot pour changer les lignes en colonnes
Package SSIS avec le Pivot pour changer les lignes en colonnes

3. Exécuter le package SSIS

Exécuter maintenant le package pour transférer et pivoter les données SQL Server du format en lignes vers un format en colonnes. Les 24 lignes du jeu de données, soit 24 mois, sont pivotées en seulement 2 lignes, soit 2 années.

Exécution du Pivot SSIS avec succès
Exécution du Pivot SSIS avec succès

4. Vérifier les données depuis SSMS

Une fois le package exécuter, se connecter à la base de données avec SSMS (SQL Server Management Studio) et exécuter ces 2 requêtes de sélection de données. Le résultat est cohérent avec pour la première table les données pour les années 2021 et 2022 en ligne. Et pour la deuxième, les mêmes montants avec 2021 sur une seule ligne et l’année 2020 sur une autre ligne.

select * from [dbo].[Sales_Monthly_In_Lines];

select * from [dbo].[Sales_Monthly_In_Columns];

Conclusion sur l’utilisation du Pivot SSIS dans un cas simple

Pour conclure sur ce tutoriel sur le Pivot SSIS, le composant natif permet facilement de pivoter les lignes d’une table en colonnes, mais avec une structure simple. C’est à dire qu’il gère une seule colonne de regroupement, statique. Pour des cas plus complexes, il est recommandé de passer par une requête T-SQL PIVOT plus flexible et plus facile à maintenir.

Tutoriels sur le Pivot ou Unpivot avec les technologies Microsoft

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*