Comment Pivoter Des Lignes En Colonnes Avec SSIS ? PIVOT

Transformer les données d’une table SQL Server de lignes en colonnes dans un package SSIS en utilisant la transformation PIVOT.

La suite Microsoft BI permet nativement de pivoter des lignes en colonnes avec SSIS dans un flux de données, depuis une table ou un fichier avec le composant Pivot. Mais comment réaliser l’opération en pratique avec un package? Voici donc un tutoriel pas à pas pour paramétrer le composant SSIS Pivot et transformer des données depuis des lignes vers un format en colonnes. Dans notre exemple 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, appelées LineageID. Le pivot de données est couramment utilisé dans les tableaux croisés dynamiques par exemple.

Table of Contents

1. Créer la table source à Pivoter depuis SSIS (Premium)

Première étape : depuis SSMS, créer la table des ventes avec les valeurs mensuelles stockées en lignes, il s’agit d’une instruction SQL Server Create Table de base avec une clé primaire.

Contenu PREMIUM : Suivre la Formation SSIS en Vidéo

2. Insérer les lignes à Pivoter avec SSIS (Premium)

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

Les ressources sont disponibles dans la formation SSIS.

3. Créer la table cible pour les données pivotées en colonnes (Premium)

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

Les ressources sont disponibles dans la formation SSIS.

4. Créer un flux de données avec le Pivot SSIS

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. Ajouter ces 3 composants dans un flux de données existant, ou en créer un nouveau.

Une connexion de type OLE DB Source
Une transformation Pivot
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*

5. Configurer la source de données SSIS pour le Pivot

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

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*

6. Configurer le composant SSIS pour pivoter les lignes en colonnes

Configurer maintenant le Pivot SSIS comme suit:

Choisir la clef de pivot dont les valeurs deviennent le nom des Nouvelles colonnes : MonthName
Choisir la colonne clef qui restera présente en sortie : YearID
Sélectionner la colonne source qui contient les valeurs : SalesAmount
Entrer la liste des colonnes créées par le Pivot, séparées par une virgule : 01_Janvier à 12_Décembre
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*

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.

7. Configurer la table de destination avec les mois en colonnes

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*

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*

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 — *Pivot SSIS pour transformer les lignes en colonnes*

8. Exécuter le package SSIS pour Pivoter les lignes

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.

9. Vérifier les données après le Pivot 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];

Une manière simple de pivoter des lignes en colonnes avec SSIS

Pour conclure sur cet article qui explique comment pivoter les lignes en colonnes avec SSIS Pivot, le composant natif permet facilement de pivoter les lignes d’une table SQL, mais cette dernière doit avoir 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 la requête T-SQL PIVOT plus flexible et plus facile à maintenir.