02 • 34 • 09 • 31 • 70
Découvrez SSIS, et comment mesurer les possibilités d’utilisation par des utilisateurs métiers, via SSIS seul ou via la capacité de couplage avec Power Query, et évoquons des premières pistes autour de la migration de programmes SAS.
Présentation de SSIS / Interface de développement
Microsoft SSIS (SQL Server Integration Services) est la solution ETL on-premise de Microsoft.
Intégré à SQL Server depuis la version 2005, SSIS a régulièrement évolué pour être à présent un outil robuste, stable, complet, et largement déployé en entreprise.
En attendant la sortie de Microsoft SQL Server 2025, la version actuelle est 2022.
Comme tout ETL qui se respecte, SSIS permet de se connecter à toutes les sources de données du marché (Excel, fichier plat, XML, base de données, etc…) pour en extraire les données, y appliquer des séries de transformation (tri, fusion, agrégation, filtre, qualité de donnée, etc…), et charger cette donnée dans des destinations aussi variées.
L’interface de développement de SSIS est à la fois accessible et très complète. En effet, le développement se fait essentiellement par glisser-déposer de composants graphiques organisés entre eux par des flèches, ce qui permet d’avoir rapidement une vision claire du parcours de la donnée sans pour autant être un expert de la solution. SSIS est calirement une solution Low-Code.
Les possibilités de paramétrage, d’injection de code (SQL, C#, script Power Query) de Microsoft SQL Server Integration Services permettent également pour un utilisateur plus technique d’aller plus loin dans le développement, pour des raisons de performance ou de réutilisations de code existant, dans le cadre d’une migration depuis un autre outil par exemple.
Sa proximité avec SQL Server, bien que tous les autres systèmes de bases de données soient pris en charge, permet de bénéficier de nombreux avantages parmi lesquels :
- Le déploiement des développements de manière simple et sécurisée, avec une gestion d’environnements (développement, qualification, production) paramétrable directement depuis SSMS (SQL Server Management Studio). Une base de données « catalogue » hébergée au sein de SQL Server contient toutes les informations nécessaires.
- La planification et le séquencement des packages via SQL Agent, largement utilisé et ayant fait ses preuves par sa simplicité, sa robustesse et son ouverture
- Le coût – compris avec la plupart des éditions SQL Server – donc « gratuit » pour les clients SQL Server
- Un système de log très puissant et très efficace pour identifier très rapidement les problèmes de développement ou d’environnement
Le développement des packages se fait sous un environnement bien connu des développeurs, à savoir Visual Studio, sur lequel il est nécessaire d’installer l’extension SSIS.
Une fois cette installation faite, une boîte à outils SSIS est disponible :
On voit également que l’interface est assez clairement et logiquement découpée :
- À gauche cette fameuse boîte à outils, par le biais de laquelle on va glisser-déposer les différents composants.
- À droite notre solution, avec en particulier les paramètres du projet si nécessaire, les informations de connexions aux différentes sources, et les différents packages
- Au centre la partie « développement » qui consiste en un ensemble de composants reliés entre eux. La lisibilité est optimale car les noms des composants sont à notre main et des textes explicatifs peuvent être ajoutés.
Les capacités métier de SSIS
Pour illustrer plus concrètement comment se développe un package classique, nous allons nous appuyer sur un exemple simple.
Nous disposons de :
- deux fichiers plats avec de la donnée brute issue d’une extraction depuis un outil de gestion
- nos clients
- nos factures
Pour des raisons d’analyse, notre client nous demande de lui fournir un seul fichier plat contenant les clients, leurs factures, et ceci pour les hommes majeurs dont le revenu est supérieur à 30 k€ annuel.
Voici le package final correspondant :
Nous voyons graphiquement les différentes étapes.
Pour aller un peu plus dans le détail, nous pouvons cliquer sur chaque composant pour les éditer.
La configuration pour extraire les données d’un fichier CSV consiste à fournir le chemin d’accès à ce fichier. La structure et le nom des colonnes est ensuite analysée, et un aperçu est disponible pour pouvoir vérifier rapidement que les données sont correctement récupérées :
La transformation suivante consiste à calculer l’âge du client, ne disposant que de sa date de naissance :
Le code est assez abordable, et l’assistant en haut à droite accompagne les utilisateurs les moins aguerris pour donner la syntaxe des différentes fonctions.
Le composant suivant va permettre de filtrer avec la règle de gestion donnée par notre client :
De la même manière que pour le calcul de l’âge, la syntaxe reste abordable, mais on mesure bien que la combinaison de toutes les fonctions disponibles permet de faire toutes les transformations nécessaires dans les cas les plus poussés en entreprise.
À tout moment du développement, il est possible de lancer l’exécution du package, de manière à vérifier par exemple le résultat de nos transformations avant d’aller plus loin.
Dans notre exemple, nous souhaitons vérifier le calcul de l’âge. Il suffit de poser une « loupe » sur la bonne flèche, donc après le composant de calcul de l’âge, et de lancer l’exécution du package.
On constate que le package est en pause au niveau de la « loupe », une visionneuse s’ouvre alors et nous pouvons vérifier notre transformation puis fermer la visionneuse pour continuer l’exécution.
On remarque aussi qu’à chaque sortie de composant, le nombre de lignes est indiqué, ce qui est très utile pour détecter rapidement des anomalies.
Déploiement / Automatisation / Planification dans SQL Server Management Studio
Une fois développé, notre package peut être déployé de manière à être lancé simplement à partir de SSMS, ou bien planifié.
Le déploiement se fait en quelques clics. Il s’agit essentiellement de nommer l’instance SQL Server qui doit accueillir nos packages.
SSMS est l’outil de gestion de SQL Server. Il permet d’administrer notre serveur de bases de données, mais donc aussi d’administrer nos packages SSIS
On note par exemple sur la capture ci-dessous, que le package est bien déployé au niveau du catalogue SSIS, et qu’un rapport d’exécution est disponible pour avoir toutes les infos nécessaires à l’exploitation :
On voit aussi qu’il est possible et recommandé d’utiliser différents environnements, ici Développement et Production. L’exécution se fait donc en sélectionnant un package et un environnement.
Dans un but d’automatisation et de planification des exécutions de packages et de chaînes de traitement de données en général, l’utilisation du SQL Server Agent est recommandée :
Nous voyons donc que SSIS est un outil à la fois très puissant et complet, mais également abordable car son interface graphique est low-code.
Couplage SSIS avec Power Query
Pour améliorer encore ce côté abordable par exemple, ou bien dans une logique de réutilisation, il y a une fonctionnalité qui nous semble intéressante à évoquer : la possibilité d’intégrer rapidement et facilement des scripts Power Query.
En effet, avec l’essor de Power BI auprès des utilisateurs métiers, Power Query est largement utilisée dans un contexte de Data Visualisation ou bien dans le traitement de données directement dans Excel.
Il devient donc intéressant de pouvoir réutiliser les développements Power Query dans des cas d’usages du type : "Mes utilisateurs métiers sont à l’aise avec Power Query mais ont besoin d’automatiser leurs traitements ou la diffusion des données."
Sur la base de notre exemple précédent, notre utilisateur métier maîtrisant Power Query a réalisé les opérations suivantes :
- chargement des 2 fichiers Excel
- calcul de l’âge
- filtre sur l’âge
- le sexe
- le revenu annuel
- fusion dans une table résultat
On peut voir dans la capture de Power Query ci-dessus qu’il est très simple d’ajouter une colonne "Âge" dans "écrire une ligne de code".
Les manipulations des utilisateurs génèrent un script que nous allons tout simplement récupérer pour l’injecter dans un nouveau package SSIS. Ce script se récupère directement par le bouton Éditeur avancé :
Voici l’illustration de notre package SSIS :
Nous voyons que le package est encore plus simple que dans l’exemple précédent, ce qui s’explique par le fait que les règles de calculs des transformations sont embarquées dans les composants sources Power Query :
Le composant est très simple à paramétrer, il suffit de copier-coller le code récupéré dans Power Query.
On voit par ce cas d’usage qu’il est très simple de récupérer des transformations depuis Power Query, de les encapsuler dans SSIS pour ensuite déployer, automatiser et planifier nos traitements de données.
Migration de programmes SAS sous SSIS
À date et à notre connaissance il n’y a pas d’outil automatique efficace pour migrer des programmes SAS en SSIS, ce qui n’est pas grave en soit car ce type de projet est souvent l’occasion de rationaliser les développements.
L’approche est donc la reconstruction des programmes SAS en packages SSIS.
On passera alors par une phase d’analyse pour identifier les sources, les transformations et les cibles des programmes SAS. La complexité est directement corrélée à celle qu’on va trouver dans les transformations, avec un bémol : les transformations qui seraient portées par des procédures de type PROC SQL devraient être assez rapidement récupérables en l’état dans SSIS.
On utiliserait alors le composant Tâche d’exécution de requêtes SQL comme illustré ci-dessous, qui permet de passer des paramètres au SQL :
Pour donner des exemples de temps de développement, qui restent à affiner en fonction de ce qui est entendu par complexe :
- Package simple : 1 j
- Package moyen : 2 à 3 j
- Package complexe : 5 à 10 j
On pourra affiner avec une première analyse rapide de quelques programmes SAS représentatifs. On note généralement une courbe d’apprentissage assez rapide sur ce type de projet, en fonction du nombre de programmes à migrer, ce qui permet de réduire ce chiffrage unitaire.
Vous cherchez des consultants pour vous accompagner ou une formation sur Microsoft SSIS, Contactez-nous !
