SSIS & ETL PS: TRES IMPORTANT: dans l’exercice 2 de l’atelier pratique, taper pour variable dans les propriétés du gestionnaire “$COMPUTERNAME”, mais il faut ensuite aller dans le menu “outils” cliquer sur SSIS pour la configuration de packages et sélectionner dans les variables d’environnement, “COMPUTERNAME”, puis “Servername” SQL Server Integration Services Microsoft SQL Server 2005 Integration Services (SSIS) est une plateforme qui permet de créer des solutions d'intégration de données hautement performantes, notamment des packages d'extraction, de transformation et de chargement (ETL) pour le Data Warehouse. Integration Services contient des outils graphiques et des Assistants pour la création et le débogage des packages ; des tâches pour la réalisation de fonctions de flux de travail telles que des opérations FTP, pour l'exécution d'instructions SQL ou pour l'envoi de messages électroniques ; des sources et des destinations de données pour l'extraction et le chargement de données ; des transformations pour le nettoyage, l'agrégation, la fusion et la copie de données ; un service de gestion (le service Integration Services) pour administrer Integration Services et des interfaces de programmation d'application (API) pour programmer le modèle objet Integration Services. Integration Services remplace les services DTS (Data Transformation Services), qui ont tout d'abord été intégrés sous la forme d'un composant de SQL Server 7.0.
Utilisations courantes d'Integration Services
SQL Server 2005 Integration Services (SSIS) met à votre disposition un ensemble complet de tâches, de conteneurs, de transformations et d'adaptateurs de données intégrés qui prennent en charge le développement d'applications métiers. Sans écrire une seule ligne de code, vous pouvez créer des solutions SSIS qui résolvent les problèmes complexes rencontrés dans l'entreprise à l'aide d'ETL et de Business Intelligence, gèrent les bases de données SQL Server et copient les objets SQL Server d'une instance à l'autre de SQL Server.
Les scénarios suivants décrivent les utilisations courantes des packages SSIS.
Fusion de données à partir de banques de données hétérogènes
Les données sont généralement stockées dans de nombreux systèmes de stockage de données différents ; leur extraction de toutes les sources et leur fusion en un même ensemble de données cohérent sont des tâches complexes. Cette situation peut se présenter pour plusieurs raisons.
Par exemple :
De nombreuses organisations archivent les informations stockées dans des systèmes de stockage de données de générations antérieures. Ces données ne sont pas forcément importantes pour les opérations quotidiennes, mais elles peuvent s'avérer précieuses pour effectuer des analyses de tendances supposant la collecte de données sur une longue période de temps.
Les succursales d'une organisation peuvent utiliser différentes technologies de stockage de données pour stocker les données opérationnelles. Le package peut être amené à extraire des données de feuilles de calcul ainsi que de bases de données relationnelles avant de les fusionner.
Les données peuvent être stockées dans des bases de données qui utilisent différents schémas pour les mêmes données. Le package peut être amené à modifier le type de données d'une colonne ou à combiner en une seule colonne des données de plusieurs colonnes avant de les fusionner.
SSIS peut se connecter à une grande variété de sources de données, notamment à plusieurs sources dans un même package. Un package peut se connecter aux bases de données relationnelles à l'aide de fournisseurs .NET et OLE DB, et à de nombreuses bases de données existantes à l'aide de pilotes ODBC. Il peut également se connecter aux fichiers plats, aux fichiers Excel et aux projets Analysis Services.
SSIS comprend des composants source qui, à partir de la source de données à laquelle le package se connecte, réalisent des tâches d'extraction de données de fichiers plats, de feuilles de calcul Excel, de documents XML ainsi que de tables et de vues dans des bases de données relationnelles.
Ensuite, les données sont généralement transformées à l'aide des transformations incluses dans Integration Services. Une fois les données transformées dans des formats compatibles, elles peuvent être fusionnées physiquement en un même ensemble de données.
Une fois les données transformées et correctement fusionnées, elles sont généralement chargées vers une ou plusieurs destinations. Integration Services permet de charger les données dans des fichiers plats, des fichiers bruts et des bases de données relationnelles. Les données peuvent également être chargées dans un ensemble d'enregistrements en mémoire et être accessibles à d'autres éléments de package.
Remplissage des entrepôts de données et des mini-Data Warehouses
En règle générale, les données stockées dans les entrepôts de données et dans les Data Warehouses sont fréquemment mises à jour et les chargements de données sont très volumineux.
Integration Services comprend une tâche qui charge les données en bloc directement à partir d'un fichier plat vers les tables et vues SQL Server et un composant de destination qui charge les données en bloc dans une base de données SQL Server (cette opération représente la dernière étape d'un processus de transformation de données).
Un package SSIS peut être configuré de manière à pouvoir être redémarré. Cela signifie que vous pouvez réexécuter le package à partir d'un point de contrôle prédéterminé, en l'occurrence une tâche ou un conteneur du package. La possibilité de redémarrer un package permet d'économiser beaucoup de temps, notamment si les données traitées par celui-ci proviennent de nombreuses sources.
Vous pouvez utiliser des packages SSIS pour charger les tables de dimensions et de faits dans la base de données. Si les données sources d'une table de dimensions sont stockées dans plusieurs sources de données, le package peut fusionner les données en un même ensemble de données et charger la table de dimensions dans un même processus, au lieu d'utiliser un processus distinct pour chaque source de données.
La mise à jour des données des entrepôts de données et des Data Warehouses peut être complexe, car ces deux types de banques de données comprennent généralement des dimensions à variation lente dont la gestion peut s'avérer difficile par le biais d'un processus de transformation de données. L'Assistant Dimension à variation lente automatise la prise en charge des dimensions à variation lente en créant dynamiquement des instructions SQL qui insèrent et mettent à jour des enregistrements, actualisent les enregistrements correspondants et ajoutent de nouvelles colonnes aux tables.
En outre, les tâches et les transformations des packages Integration Services peuvent traiter les cubes et les dimensions Analysis Services. Lorsque le package met à jour les tables de la base de données sur laquelle est basé un cube, vous pouvez utiliser des tâches et des dimensions Integration Services pour traiter automatiquement le cube ainsi que pour traiter les dimensions. Le traitement automatique des cubes et des dimensions permet de maintenir les données à jour pour les utilisateurs dans les deux environnements : les utilisateurs qui accèdent aux informations des cubes et des dimensions et ceux qui accèdent aux données d'une base de données relationnelle.
SSIS peut également calculer des fonctions avant que les données soient chargées à leur emplacement de destination. Si vos entrepôts de données et Data Warehouses stockent des informations agrégées, le package SSIS peut calculer des fonctions telles que SUM, AVERAGE et COUNT. Une transformation SSIS peut également convertir les données relationnelles et les transformer en un format moins normalisé, mieux adapté à la structure des tables de l'entrepôt de données.
Nettoyage et standardisation des données
Que les données soient chargées dans une base de données OLTP (Online Transaction Processing) ou OLAP (Online Analytic Processing), dans une feuille de calcul Excel ou dans un fichier, elles doivent être préalablement nettoyées et standardisées. Il peut s'avérer nécessaire de mettre à jour les données pour les raisons suivantes :
Les données émanent de plusieurs succursales d'une organisation, chacune utilisant ses propres conventions et standards. Il peut être nécessaire de modifier le format des données avant de les utiliser.
Par exemple, vous pouvez être amené à combiner le prénom et le nom dans une même colonne.
Les données sont louées ou achetées. Avant de les utiliser, il peut être nécessaire de les standardiser et de les nettoyer afin de les adapter.
Par exemple, une organisation souhaite vérifier que tous les enregistrements utilisent le même ensemble d'abréviations de nom d'état ou le même ensemble de noms de produit.
Les données sont spécifiques à des critères régionaux.
Par exemple, les données peuvent utiliser divers formats numériques et date/heure.
Si vous fusionnez des données répondant à différents paramètres régionaux, vous devez, avant de les charger, les convertir en un même paramètre régional afin qu'elles ne soient pas endommagées.
Integration Services comprend des transformations intégrées que vous pouvez ajouter aux packages afin de nettoyer et de standardiser les données, de modifier la casse, le type ou le format des données ou de créer de nouvelles valeurs de colonne basées sur des expressions.
Par exemple, le package peut concaténer les colonnes des prénoms et des noms en une même colonne de noms complets, puis mettre les caractères en majuscules.
Un package Integration Services peut également nettoyer les données en remplaçant les valeurs des colonnes par des valeurs issues d'une table de référence, à l'aide d'une recherche exacte ou floue permettant de localiser les valeurs dans une table de référence. En règle générale, un package applique la recherche exacte puis, si celle-ci échoue, il applique la recherche floue.
Par exemple, le package recherche d'abord un nom de produit dans la table de référence à partir de la valeur de clé primaire du produit. Si cette recherche ne parvient pas à renvoyer le nom de produit, le package effectue une recherche floue sur le nom du produit.
Une autre transformation nettoie les données en regroupant les valeurs similaires d'un ensemble de données. Cette opération permet d'identifier les enregistrements susceptibles d'être des doublons et qui doivent donc subir une évaluation approfondie avant d'être éventuellement insérés dans la base de données.
Par exemple, en comparant les adresses stockées dans les enregistrements des clients, vous pouvez identifier une série de doublons de client.
Intégration de la Business Intelligence à un processus de transformation de données
Un processus de transformation de données requiert une logique intégrée pour répondre dynamiquement aux données auxquelles il accède et qu'il traite.
Il peut s'avérer nécessaire de résumer, de convertir et de distribuer les données en fonction de valeurs de données. Le processus peut même être amené à rejeter des données, suite à l'évaluation de valeurs de colonne.
Pour tenir compte de cette contrainte, la logique du package SSIS peut être amenée à réaliser les types de tâches suivants :
1 -Fusion de données issues de plusieurs sources de données
2 -Évaluation des données et application de conversions de données
3 -Fractionnement d'un ensemble de données en plusieurs ensembles de données en fonction de valeurs de données
4 -Application de différentes agrégations à différents sous-ensembles d'un ensemble de données
5 -Chargement de sous-ensembles des données dans différentes destinations ou dans plusieurs destinations.
Integration Services met à votre disposition des conteneurs, des tâches et des transformations qui permettent d'intégrer la Business Intelligence aux packages SSIS.
Les conteneurs prennent en charge la répétition des flux de travail en passant en revue tous les fichiers ou objets et en évaluant les expressions. Un package peut évaluer les données et répéter les flux de travail en fonction des résultats. Par exemple, si la date est comprise dans le mois actuel, le package effectue un ensemble de tâches, sinon il exécute un autre ensemble de tâches.
Les tâches qui utilisent des paramètres d'entrée peuvent également intégrer la Business Intelligence aux packages.
Par exemple, la valeur d'un paramètre d'entrée peut filtrer les données extraites par une tâche.
Les transformations peuvent évaluer les expressions puis, en fonction des résultats, envoyer les lignes d'un ensemble de données à différentes destinations. Une fois les données fractionnées, le package peut appliquer différentes transformations à chaque sous-ensemble de l'ensemble de données.
Par exemple, une expression peut évaluer une colonne de dates, ajouter les données de vente relatives à la période appropriée, puis stocker uniquement les informations de résumé.
Il est également possible d'envoyer un ensemble de données à plusieurs destinations, puis d'appliquer différents ensembles de transformations aux mêmes données.
Par exemple, un ensemble de transformations peut résumer les données, tandis qu'un autre développe celles-ci en recherchant des valeurs dans des tables de référence et en ajoutant des données issues d'autres sources.
Automatisation des fonctions d'administration et du chargement des données
Les administrateurs souhaitent fréquemment automatiser des fonctions d'administration telles que la sauvegarde et la restauration des bases de données, la copie des bases de données SQL Server et des objets qu'elles contiennent, la copie des objets SQL Server et le chargement des données. Les packages Integration Services peuvent réaliser ces fonctions.
Integration Services comprend des tâches spécifiquement conçues pour copier les objets de base de données SQL Server tels que les tables, les vues et les procédures stockées, pour copier les objets SQL Server tels que les bases de données, les connexions et les statistiques, ainsi que pour ajouter, modifier et supprimer les données et les objets SQL Server à l'aide d'instructions Transact-SQL.
L'administration d'un environnement de bases de données OLTP ou OLAP implique fréquemment le chargement de données. Integration Services possède plusieurs tâches qui facilitent le chargement de données en bloc. Vous pouvez utiliser une tâche pour charger des données à partir de fichiers texte directement dans des tables et des vues SQL Server ou utiliser un composant de destination pour charger des données dans des tables et des vues SQL Server après avoir appliqué des transformations aux données des colonnes.
Un package Integration Services peut exécuter d'autres packages. Une solution de transformation de données comprenant de nombreuses fonctions d'administration peut être subdivisée en plusieurs packages afin de faciliter la gestion et la réutilisation des packages.
Si vous devez réaliser les mêmes fonctions d'administration sur différents serveurs, vous pouvez utiliser des packages. Un package peut utiliser le bouclage pour passer en revue tous les serveurs et réaliser les mêmes fonctions sur plusieurs ordinateurs. Pour prendre en charge l'administration de SQL Server, Integration Services fournit un énumérateur qui passe en revue les objets SMO (SQL Server Management Object).
Par exemple, un package peut utiliser l'énumérateur SMO pour réaliser les mêmes fonctions d'administration sur chaque travail de la collection Travaux d'une installation SQL Server.
Création d'un package SSIS Créer un projet SSIS
Cliquez sur Démarrer, pointez sur Tous les programmes, sur Microsoft SQL Server 2005, puis cliquez sur SQL Server Business Intelligence Development Studio.
Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Projet.
Dans la boîte de dialogue Nouveau projet, spécifiez les valeurs du tableau suivant, puis cliquez sur OK.
-
Propriété
|
Valeur ou action
|
Modèles
|
Projet Integration Services
|
Nom
|
Exporter le projet Clients
|
Emplacement
|
D:\Democode
|
Créer le répertoire pour la solution
|
Option désactivée
|
Créer une source de données pour la base de données AdventureWorks
Dans l'Explorateur de solutions, cliquez avec le bouton droit sur Sources de données, puis cliquez sur Nouvelle source de données.
Dans la page Assistant Source de données, cliquez sur Suivant.
Dans la page Sélectionner la méthode de définition de la connexion, cliquez sur Nouveau.
Dans la boîte de dialogue Gestionnaire de connexions, spécifiez les valeurs du tableau suivant, puis cliquez sur OK.
Propriété
|
Valeur
|
Nom du serveur
|
MIAMI
|
Connexion au serveur
|
Utiliser l'authentification Windows
|
Sélectionner ou entrer un nom de base de données
|
AdventureWorks
|
Dans la page Sélectionner la méthode de définition de la connexion, cliquez sur Suivant.
Dans la page Fin de l'Assistant, dans la zone Nom de la source de données, tapez Adventure Works puis cliquez sur Terminer.
Créer un gestionnaire de connexions pour la source de données AdventureWorks
Cliquez avec le bouton droit n'importe où dans le volet Gestionnaires de connexions (en bas de la surface de dessin), puis cliquez sur Nouvelle connexion de source de données.
Dans la boîte de dialogue Sélectionner une source de données, dans la liste Sources de données disponibles, cliquez sur AdventureWorks, puis cliquez sur OK.
Créer une tâche de flux de données
Dans l'espace de travail du concepteur, cliquez sur l'onglet Flux de contrôle.
Dans le menu Affichage, cliquez sur Boîte à outils.
Dans la Boîte à outils, faites glisser l'objet Tâche de flux de données vers l'espace de travail du concepteur.
Dans l'espace de travail du concepteur, cliquez sur l'onglet Flux de données.
Dans la boîte à outils, dans la section Sources de flux de données, faites glisser l'objet Source OLE DB vers l'espace de travail du concepteur.
Cliquez avec le bouton droit sur l'objet Source OLE DB, puis cliquez sur Modifier.
Dans la boîte de dialogue Éditeur de source OLE DB, spécifiez les valeurs du tableau suivant.
Propriété
|
Valeur
|
Gestionnaire de connexions OLE DB
|
AdventureWorks
|
Mode d'accès aux données
|
Table ou vue
|
Nom de la table ou de la vue
|
[Sales].[vIndividualCustomer]
|
Dans le volet gauche, cliquez sur Colonnes.
Effacez toutes les colonnes à l'exception des colonnes FirstName, LastName et City, puis cliquez sur OK.
Dans la boîte à outils, dans la section Transformations du flux de données, faites glisser l'objet Colonne dérivée vers l'espace de travail du concepteur, sous l'objet Source OLE DB.
Dans l'espace de travail du concepteur, cliquez sur l'objet Source OLE DB, puis faites glisser la connexion verte sur l'objet de script Colonne dérivée.
Cliquez avec le bouton droit sur l'objet Colonne dérivée, puis cliquez sur Modifier.
Dans la boîte de dialogue Éditeur de transformation de colonne dérivée, dans le volet inférieur, spécifiez les valeurs du tableau suivant, puis cliquez sur OK.
Propriété
|
Valeur
|
Nom de la colonne dérivée
|
FullName
|
Colonne dérivée
|
|
Expression
|
[FirstName] + " " + [LastName]
|
Type de données
|
Chaîne Unicode [DT_WSTR]
|
Longueur
|
100
|
Dans la boîte à outils, dans la section Destinations du flux de données, faites glisser l'objet Destination de fichier plat vers l'espace de travail du concepteur, sous l'objet Colonne dérivée.
Cliquez sur l'objet Colonne dérivée, puis faites glisser la connexion verte vers l'objet Destination de fichier plat.
Cliquez avec le bouton droit sur l'objet Destination de fichier plat, puis cliquez sur Modifier.
Dans la boîte de dialogue Éditeur de destination de fichier plat, cliquez sur Nouveau.
Dans la boîte de dialogue Format de fichier plat, cliquez sur Délimité, puis sur OK.
Dans la boîte de dialogue Éditeur du gestionnaire de connexions de fichiers plats, dans la zone Nom de fichier, tapez D:\Democode\Customers.txt, puis cliquez sur OK.
Dans la boîte de dialogue Éditeur de destination de fichier plat, dans le volet gauche, cliquez sur Mappages, puis assurez-vous que les mappages de colonnes font correspondre les colonnes d'entrée aux colonnes de destination du même nom, puis cliquez sur OK.
Ajouter une tâche de script après la tâche de flux de données
Dans l'espace de travail du concepteur, cliquez sur l'onglet Flux de contrôle.
Dans la Boîte à outils, dans la section Éléments de flux de contrôle, faites glisser l'objet Tâche de script vers l'espace de travail du concepteur, sous l'objet Tâche de flux de données.
Cliquez avec le bouton droit sur l'objet Tâche de script, puis cliquez sur Modifier.
Dans la boîte de dialogue Éditeur de tâche de script :
Dans la zone Name, tapez MsgDone.
Dans la zone Description, tapez Message de confirmation.
Dans le volet gauche, cliquez sur la page Script, puis sur Créer un script.
Remarque : Microsoft Visual Studio for Applications démarre et génère un script réduit.
Dans le volet ScriptMain, faites défiler la fenêtre jusqu'à la méthode Public Sub Main(), puis remplacez le commentaire Add your code here par le code suivant, en veillant à supprimer les apostrophes existantes.
MsgBox("Terminé")
Dans le menu Fichier, cliquez sur Fermer et revenir.
Dans la boîte de dialogue Éditeur de tâche de script, cliquez sur OK.
Dans l'espace de travail du concepteur, cliquez sur l'objet Tâche de flux de données, puis faites glisser la connexion verte sur l'objet de script MsgDone.
Exécuter le package et afficher les données obtenues
Dans le menu Déboguer, cliquez sur Démarrer le débogage pour exécuter le projet et exporter les données.
Dans la boîte de message Tâche de script affichant le message Terminé, cliquez sur OK.
Dans le menu Déboguer, cliquez sur Arrêter le débogage.
Cliquez sur Démarrer, puis sur Explorateur Windows.
Dans l'Explorateur Windows, recherchez le dossier D:\Democode,
puis double-cliquez sur le fichier Customers.txt.
Remarque : le Bloc-notes s'ouvre et affiche les données du fichier.
Dans le Bloc-notes, consultez les données et vérifiez qu'elles contiennent le prénom, le nom, la ville et le nom complet de chaque client en tant que collection de champs séparés par des virgules.
Fermez le Bloc-notes.
Fermez SQL Server Business Intelligence Development Studio.
|
