SSIS : SQL Server Integration Services pour maîtriser ses flux de données

#Microsoft#sql#ssis

SQL Server Integration Services (SSIS) est un outil de gestion de flux de données proposé aux possesseurs d’une licence SQL Server Standard ou Entreprise. Il permet le développement simplifié de scripts d’import / export, en mettant à la disposition de l’utilisateur, dans une interface d’édition graphique, un large éventail de fonctionnalités. Cet article offre un tour d’horizon de cet outil à travers un exemple simple, et pointe ses avantages et inconvénients.

Microsoft propose aux possesseurs d’une licence SQL Server Standard ou Entreprise un outil de gestion de flux de données nommé SQL Server Intégration Services (SSIS). Connu sous le nom de DTS (Data Transformation Services) dans ses versions antérieures à 2005, SSIS appartient à la famille des ETL (Extract-Transform-Load). Les outils de type ETL reposent sur l’utilisation de trois types d’éléments pour la gestion des flux de données :

  • Les « connecteurs » permettent l’importation et l’exportation de données dans une application. Ils sont directement reliés aux données.
  • Les « transformateurs » gèrent la manipulation des données (agrégations, conversions, filtres…).
  • Les « mappages » contrôlent la mise en correspondance des connecteurs et des transformateurs pour lier le tout.

Nous proposons ici de présenter brièvement SSIS sous forme d’un mini tutoriel. C’est la version 2005 de SSIS qui est utilisée. Une version 2008 est disponible et offre un certain nombre d’améliorations, mais la rétrocompatibilité avec la version 2005 n’est pas assurée. Il est important de souligner par ailleurs que les versions de SSIS et de Visual Studio doivent correspondre, Visual Studio 2008 ne permettant par exemple pas de développer des scripts d’import / export avec SSIS 2005. L’interface étant identique, cet article reste bien sûr valable pour SSIS 2008 et Visual Studio 2008, et le bref tutoriel qui est présenté ci-dessous peut être effectué sous Visual Studio 2008.

Présentation de l’interface de développement

La création d’un projet SSIS se fait avec Visual Studio, SSIS devant être installé sur la machine de développement. Les projets SSIS se trouvent dans la catégorie « Business intelligence projects ». Lors de la création d’un projet, un premier package SSIS, d’extension DTSX, est automatiquement ajouté. Il s’agit d’un script de traitement de données au format XML interprété par la brique Integration Services d’un SQL Server. Il contient les informations nécessaires à SSIS pour pouvoir traiter les données. L’interface de développement du package est assez intuitive. Elle est composée principalement des cinq éléments suivants (voir figure 1) :

1) « L’explorateur de solution », comme pour tout projet créé sous Visual Studio.
2) « La zone d’édition du package », sur laquelle on ajoute des éléments de traitement qui ont chacun une action à effectuer. Cette zone est constituée de différents onglets détaillés ci-dessous.
3) « La Boîte à outils » qui rassemble tous les éléments qui peuvent être ajoutés au projet par double clic ou Glisser – Déposer dans la zone d’édition du package. Les outils disponibles dépendent de l’onglet sélectionné dans la zone d’édition du package.
4) « Le gestionnaire de connecteurs » qui contrôle les différents liens avec les données. Il gère plus que la connexion en elle-même puisqu’il permet, entre autres, de sélectionner et de nommer les colonnes à traiter, de déterminer la structure d’un fichier plat de données, etc.
5) « Les propriétés » des objets qui offrent la possibilité d’une configuration approfondie. La valeur « expressions » est fréquemment utilisée pour rendre les scripts dynamiques car elle offre la possibilité de renseigner certaines valeurs grâce à des variables plutôt que de façon statique, comme c’est généralement le cas lorsque l’on passe par les boîtes de dialogue pour la configuration des objets.


Figure 1 : Interface de développement d’un package SSIS

La zone d’édition du package, initialement vierge, est composée de différents onglets :

  • L’onglet « Control Flow » est consacré à la mise en place des flux de données. Il offre la possibilité de mettre en place un certain nombre de tâches, parmi celles disponibles dans la boîte à outils, à effectuer avant ou après le traitement du flux de données en lui-même. On y définit par exemple une boucle sur les fichiers d’un répertoire, ou sur les résultats d’une requête. C’est également dans cet onglet que sont placées les tâches de traitement de flux de données, ou « Data Flow Tasks », mais leur fonctionnement se définit dans le second onglet.
  • L’onglet « Data Flow » est utilisé pour détailler les « Data Flow Tasks » de l’onglet Control Flow. On y définit les différentes transformations à appliquer au flux de données entre la source et la destination. La mise en place du traitement du flux se fait également de façon graphique, en plaçant dans la zone d’édition les connecteurs source et destination, ainsi que les différents transformateurs, choisis dans la boîte à outils.
  • L’onglet « Event Handlers » est utilisé pour la gestion des évènements. Il autorise la définition d’une chaîne de traitements particulière au cas où une erreur, ou tout autre évènement répertorié, se produit à un endroit précis de l’exécution. On y retrouve donc les mêmes outils que pour l’onglet Control Flow.
  • L’onglet « Package Explorer » offre une visualisation des différents éléments du package sous forme arborescente, comme le montre la figure 2. Il liste les variables, les connecteurs et les exécutables. Ces derniers représentent les différentes tâches, énumérateurs, …
  • L’onglet « Progress » apparaît lors de l’exécution du script en mode Debug depuis Visual Studio. Il détaille l’exécution du script, comme les messages d’erreur, les messages d’alerte, les temps d’exécution… Une fois l’exécution stoppée, il laisse sa place à l’onglet « Execution results » qui regroupe les informations relatives à la dernière exécution.


Figure 2 : Explorateur de package

Le contenu de la boîte à outils est réparti par catégories et dépend de l’onglet qui est sélectionné. Ainsi, sur l’onglet Control Flow, on retrouve une liste des éléments de flux de contrôle, avec notamment les énumérateurs de boucles, l’exécution de requêtes SQL, les tâches liées au système de fichier, l’exécution de scripts C#, … ainsi qu’une liste de tâches du plan de maintenance dans laquelle on retrouve par exemple le nettoyage d’historique, les tâches de sauvegarde, la vérification de l’intégrité des données. Des exemples d’éléments de flux de contrôles sont visibles en Figure 1 (cadre numéroté 3). Sur l’onglet Data Flow, on retrouve les sources et destinations des flux de données, ainsi que les transformateurs. Parmi ces derniers, on retrouve par exemple l’agrégation / copie / modification de colonne, le tri, la conversion… Les différentes sources et destinations de données de la version 2005 sont montrées en Figure 3. La version 2008 de SSIS offre plus de choix pour les connecteurs, avec notamment la possibilité d’utiliser un connecteur de type ADO.NET.


Figure 3 : Sources et Destinations des flux de données

Aux éléments précédemment décrits vient s’ajouter l’utilisation de variables dont la portée se définit de façon précise. En effet, une variable est accessible à l’exécutable choisi ainsi qu’à tous les exécutables inclus (voir le Package Explorer pour une vue arborescente des exécutables). Par exemple, une variable ayant pour portée un énumérateur est accessible à tous les éléments contenus dans l’enumérateur. La portée d’une variable peut également se définir à tout le package SSIS. Les variables s’utilisent par exemple pour le mappage de paramètres en entrée et en sortie des différentes tâches du package, pour récupérer le nom du fichier courant dans le cas d’un énumérateur de fichiers,… Pour consulter, créer, éditer ou supprimer des variables, un outil est disponible dans le menu SSIS => Variables.

Les premiers pas avec SSIS

Après cette brève présentation de l’interface de création d’un package SSIS, nous présentons l’élaboration d’un package SSIS à l’aide d’un exemple simple. Nous allons remplir une table d’une base de données SQL Server 2005 avec les départements français à partir d’un fichier plat (fichier texte avec les colonnes séparées par des tabulations). Cette table contient le numéro du département, le nom du département, le nom de la préfecture et le nom de la région administrative. Dans notre exemple, nous considérons que la table de la base de données a déjà été créée. Sur l’onglet Control Flow du package SSIS, nous déposons un élément de type Data Flow Task (voir la figure 7). C’est la seule tâche dont nous ayons besoin pour le moment sur cet onglet. Chaque élément du script peut être renommé, ce qui est conseillé pour plus de clarté lorsque les éléments commencent à se multiplier. Une fois cet élément créé, un clic sur l’onglet Data Flow Task nous permet de le configurer. Ici nous allons simplement indiquer le fichier source et la table de destination. Pour cela nous ajoutons une source « Flat File Source », disponible dans la liste des outils Data Flow Sources. Par double clic sur ce nouvel élément nous pouvons le configurer et lui indiquer le connecteur à utiliser. Comme il n’en existe pas encore nous allons le créer en cliquant sur « new ». La configuration du connecteur est décomposée en quatre onglets, assez explicites, où nous allons pouvoir indiquer le fichier source et configurer les colonnes. La figure 4 montre par exemple comment se configurent les colonnes.


Figure 4 : Configuration du connecteur de fichier plat

Dans la configuration de l’élément Flat File Source apparaissent maintenant les colonnes disponibles et nous pouvons sélectionner celles qui nous intéressent et les renommer (voir figure 5).


Figure 5 : Connecteur source, sélection de colonne et renommage

La configuration de la connexion à la base de données se fait de façon similaire, en utilisant cette fois ci une destination de type « OLE DB Destination », ou « ADO.NET Destination » disponible uniquement avec la version 2008. La connexion peut se faire à une base de données de type SQL Server, Oracle, voire à peu près tout SGBD du marché tant qu’il dispose de drivers ODBC. Après avoir configuré la connexion à la base de données et renseigné la table à utiliser, les correspondances entre la source et la destination se font en deux étapes. D’abord on étire, à l’aide de la souris, la flèche verte de la source jusqu’à la destination. Ensuite, on édite la destination et on effectue simplement le mappage à l’aide de flèches, comme le montre la figure 6.


Figure 6 : Mappage de la source sur la destination

On observe en figure 7 le flux de contrôle et le flux de données simplifiés à l’extrême qui viennent d’être créés. La chaîne de traitements est maintenant prête, elle offre une bonne base de départ pour prendre en main l’interface. Nous allons maintenant donner quelques exemples des possibilités de SSIS avant de passer à la phase d’exécution et de gestion les erreurs.


Figure 7 : Exemple simplifié à l’extrême de la gestion d’un flux

Pour aller un peu plus loin

Ce cas d’école permet de prendre en main SSIS en douceur. En général, pour le développement d’un script d’import export, d’autres éléments sont nécessaires. Selon les besoins, des transformateurs sont ajoutés à la tâche de traitement du flux (onglet Data Flow), et des exécutables au flux de contrôle (onglet Control Flow), avant ou après la tâche de gestion du flux de données. A la façon des sources et des destinations, l’ajout de tâches de flux de contrôle (onglet Control Flow) avant ou après la tâche de gestion du flux de données est effectué de façon graphique. On peut par exemple faire appel à des procédures stockées et mapper simplement les paramètres nécessaires en entrée et sortie avec les variables définies. On peut également mettre en place des énumérateurs pour boucler sur le résultat d’une requête ou les fichiers d’un répertoire par exemple (voir figure 8 ci-dessous). On détermine l’ordre d’exécution des tâches en les reliant par des flèches, une tâche n’étant exécutée que si la ou les tâches précédentes ont été exécutées avec succès.


Figure 8 : Exemple de script plus complexe

L’ajout de transformateurs (onglet Data Flow) repose sur le même principe. Les transformateurs sont reliés entre eux et ordonnés à l’aide de flèches. À la sortie d’un transformateur, une flèche verte redirige les lignes de données qui ont été traitées avec succès tandis qu’une flèche rouge redirige les lignes de données qui n’ont pas pu être traitées. Il est donc possible d’effectuer un traitement particulier sur des lignes qui ont posé problème. Il existe également des transformateurs d’un type un peu particulier qui autorisent une scission dans le flux de données. Il est alors possible de traiter, dans des flux de données séparés, les lignes qui demandent un traitement particulier (voir l’exemple en figure 8). On peut par exemple traiter différemment les valeurs positives et négatives. Tous ces éléments permettent de générer des chaînes de traitements complexes et de s’adapter à tous les cas de figure. Par ailleurs, dans de nombreux cas, la fenêtre de configuration des différents blocs n’offre pas assez de souplesse pour la réalisation des scripts. En effet, les paramètres y sont renseignés de façon statique. C’est le cas par exemple lorsque l’on crée un connecteur de fichier : l’interface de configuration nous invite à sélectionner le fichier voulu dans un navigateur de fichiers, ce qui a pour effet de renseigner de façon statique le chemin vers ce fichier dans la configuration du connecteur (voir le champ ConnectionString dans la fenêtre de propriétés de la figure 9). Or si l’on ajoute un énumérateur de fichiers pour boucler sur les fichiers d’un répertoire, la propriété du connecteur devra se mettre à jour de façon dynamique à chaque itération. Lorsque des paramètres ont besoin de s’adapter dynamiquement au cours du script, on renseigne les paramètres voulus grâce à un constructeur d’expression complet, accessible depuis les propriétés du bloc voulu sous le nom « expressions » (voir figure 9 ci-dessous). Dans le cas de l’énumérateur de fichiers par exemple, on mappe la sortie de l’énumérateur sur une variable et on renseigne la propriété « ConnectionString » du connecteur de fichier plat grâce à cette variable.


Figure 9 : Le constructeur d’expressions

Enfin, un autre aspect intéressant de SSIS concerne la gestion particulièrement pointue des évènements. Il est possible de mettre en place une chaîne de traitements spécifiques lorsqu’un évènement particulier se produit (erreur, warning, …). L’onglet Event Handlers se présente donc comme l’onglet Data Flow. On y définit des transformations particulières qui sont appliquées au flux de données quand ou si l’évènement ciblé se produit. Toutes les spécificités des différents outils ne peuvent pas être passées en revue ici, mais la présentation des principaux types d’objets ainsi que de l’environnement de développement qui vient d’être faite permet de prendre un bon départ. Ce système de création de script n’est bien sûr pas exempt de défaut. SSIS se révèle par exemple assez sensible à la modification des connecteurs une fois la chaîne de traitements du flux mise en place, et ce, même si le nom des colonnes ne change pas. Cet outil favorise néanmoins le développement rapide des scripts d’import / export.

Exécution des packages SSIS

Pendant la phase de Debug, l’exécution du package SSIS se fait depuis Visual Studio, tout comme pour un projet de développement classique (en C# ou VB). On peut visualiser le déroulement du traitement en temps réel grâce à un code de couleurs appliqué à chacun des éléments du package SSIS :

  • le jaune pour un élément en cours d’exécution
  • le vert pour un élément dont l’exécution s’est terminée par un succès
  • le rouge pour un élément dont l’exécution s’est terminée par un échec


Figure 10 : Exécution en mode Debug d’un package SSIS

La couleur des différents éléments du package évolue en temps réel au cours de l’exécution. De plus les données sont traitées par ligne, et on observe le nombre de lignes traitées par chaque bloc. Ces informations donnent un aperçu global de la répartition des lignes de données entre la source et la destination dans le cas de scripts complexes. Si une erreur est rencontrée, l’exécution du package échoue, et le bloc n’ayant pu finir son exécution est indiqué en rouge, comme c’est le cas dans la figure 10. L’onglet Progress détaille alors le message d’erreur, ainsi que de nombreuses informations sur l’exécution du package. Il est également possible d’ajouter des points d’arrêt sur les éléments du Control Flow, permettant alors d’ « espionner » le contenu des variables.

Configuration des packages

Par défaut, lorsque l’on développe un package SSIS, tous les éléments de configuration, notamment ceux des connecteurs, sont enregistrés de façon statique dans le fichier DTSX. Pour que le package SSIS puisse être utilisé sur un autre serveur que le serveur de développement, sa configuration peut être déportée dans un fichier XML d’extension « dtsConfig », une base de données ou encore une variable d’environnement. Pour cela il faut modifier, dans les propriétés du package SSIS, la valeur du champ « Configurations ». Un simple clic sur les « … » à droite de ce champ ouvre une fenêtre de dialogue qui guide l’utilisateur dans le choix de la méthode et des paramètres à exporter. Attention, pour des raisons de sécurité, certains éléments comme les mots de passe de connexion aux bases de données ne sont pas enregistrés en clair. Le mode d’enregistrement de ces données sensibles se règle grâce à la propriété « Protection Level » du package SSIS. Parmi les différentes options d’enregistrement de la configuration, le fichier XML offre l’avantage d’une meilleure portabilité du package mais révèle un défaut : le chemin absolu vers celui-ci est renseigné dans le fichier DTSX. Il n’est pas possible d’indiquer un chemin relatif par rapport au package SSIS, ce qui aurait été plus aisé pour le déploiement du package (voir ci-dessous). Le choix se fera donc en fonction du mode d’utilisation du package.

Déploiement des packages SSIS

Une fois le développement du package SSIS terminé, il ne reste qu’à l’installer sur le serveur de déploiement, nécessairement équipé de la même version de SSIS que le poste de développement. Il est possible d’activer la génération d’un utilitaire de déploiement dans les propriétés de la solution. Cet utilitaire nous guide pour l’installation du package et configure automatiquement le package SSIS en fonction du répertoire choisi, ce qui évite l’édition manuelle du fichier DTSX pour corriger le chemin vers le fichier de configuration. Attention cependant, cet utilitaire de déploiement nous a posé problème dans le cas d’une installation de packages SSIS pour la création de Jobs planifiés dans SQL Server 2005. En effet, les packages, une fois installés par l’utilitaire de déploiement, ne peuvent pas être chargés par SQL Server, alors que tout fonctionne normalement dans le cas d’un simple copier / coller des fichiers DTSX. Pour le rendre accessible à tous les ordinateurs d’un réseau équipés de Microsoft SQL Server Management Studio, qui propose une connexion de type « Integration Services », le package SSIS doit être installé dans un répertoire précis de l’installation de SQL Server.

Points forts et points faibles

Points forts :

  • Développement rapide de scripts d’import / export
  • Richesse des outils (tâches, connecteurs, transformateurs…)
  • Visualisation des flux de données et utilisation de points d’arrêts lors de l’exécution dans Visual Studio facilitant la phase de Debug
  • Facilité de déploiement et d’utilisation.

Points faibles :

  • Problèmes de rétrocompatibilité, Visual Studio 2008 ne permettant pas le développement de scripts compatibles SSIS 2005
  • Pas de licence SSIS individuelle (nécessite une licence SQL Server)
  • Petits « bugs » pénibles : chemin absolu vers le fichier de configuration indiqué de façon statique dans le DTSX ; packages non utilisables dans les jobs SQL Servers si installés par l’utilitaire de déploiement ; problèmes rencontrés avec le mappage de paramètres pour l’appel à une procédure stockée avec la version 2005
  • Approche « tout graphique » provoquant quelques surprises (on ne contrôle pas tout).
Conclusion

SSIS est un outil ETL complet qui offre de nombreuses options de traitement des flux de données. Les exports et imports des données sont simplifiés. Le développement de scripts est rapidement accessible à des non expérimentés, grâce à une interface simple et intuitive. De plus, Integration Services s’intègre très bien aux services de la plateforme .Net. Nous regrettons cependant qu’il soit impossible de développer des scripts SSIS compatibles avec la version 2005 sous Visual Studio 2008. Cela oblige les développeurs à continuer à utiliser Visual Studio 2005, sans pouvoir profiter des améliorations de la version 2008, ou à migrer vers la version 2008 de SSIS et donc de SQL Server.