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.
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
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.
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
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.
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.
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.
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.
Soyez le premier à commenter