Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Introduction àà Sql Server Integràtion Services (SSIS) Plus connu avant sous le

Introduction àà Sql Server Integràtion Services (SSIS) Plus connu avant sous le nom de Data Transformation Services (DTS), SSIS est la nouvelle version ce cet outil de SQL Server apparu avec la version 2005 du SGBD de Microsoft. Nous allons essayer de découvrir ensemble ce que peut nous apporter SSIS. Sommaire Présentation générale............................................................................................................................3 Comment travailler avec SSIS ?...............................................................................................................4 ControlFlow........................................................................................................................................7 DataFlow............................................................................................................................................7 Event Handlers....................................................................................................................................7 Package Explorer................................................................................................................................7 Présentation de l’exemple......................................................................................................................7 Structure d’un fichier de log de IIS.....................................................................................................7 Tâches à réaliser par notre package....................................................................................................9 Stockage.............................................................................................................................................9 La copie du fichier................................................................................................................................10 Traitement du fichier de log.................................................................................................................13 Lecture du fichier de log...................................................................................................................13 La connexion au fichier de log......................................................................................................14 Flat File Source.............................................................................................................................19 Création de la colonne DateTime.....................................................................................................21 Conversion des données...................................................................................................................24 Ecriture en base................................................................................................................................27 Une connexion à la base...............................................................................................................28 Liaison de nos données à la base..................................................................................................30 Première tentative d’exécution du Data Flow.......................................................................................32 Dernier ajustement sur le Control Flow................................................................................................37 Déploiement du package......................................................................................................................38 Vérification du déploiement et exécution............................................................................................43 Conclusion............................................................................................................................................47 Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 1 sur 47 Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 2 sur 47 Présentation générale Tout d’abord, pour pouvoir disposer de SSIS, vous devez utiliser SQL Server 2005 Standard ou Entreprise Edition (Developper Edition aussi, mais pour le développement). Les versions Express et autres Standard et Workgroup Edition ne sont malheureusement pas dotées de cette fonctionnalité. Seules les versions Entreprise et Developper proposent toutes les fonctionnalités de SSIS. Reportez-vous à cette page pour plus de détails sur les possibilités offertes par chaque version : http://msdn2.microsoft.com/en-us/library/ms143761.aspx Maintenant que nous avons vu quelles versions permettent d’exploiter SSIS, voyons maintenant à quoi peut servir cette nouvelle fonctionnalité. On ne va pas faire très compliqué, mais en gros, SSIS vous permet de transformer vos donner, de les importer ou de les exporter vers ou depuis SQL Server. Prenons un exemple concret. Vous avez un serveur Web, au hasard Internet Information Service (IIS) et vous voulez pouvoir obtenir des statistiques de son utilisation. Si vous connaissez un peu IIS, vous savez sans doute qu’il ne permet de ne générer qu’un fichier de log au format texte. #Software: Microsoft Internet Information Services 5.1 #Version: 1.0 #Date: 2007-05-21 17:03:20 #Fields: time c-ip cs-method cs-uri-stem sc-status 17:03:20 127.0.0.1 GET /EspacePerso/~/images/baniereperso.png 404 17:06:52 127.0.0.1 GET /EspacePerso/net/test/Default.aspx 404 17:08:07 127.0.0.1 DEBUG /EspacePerso/ViewArticle.aspx 401 17:08:13 127.0.0.1 DEBUG /EspacePerso/ViewArticle.aspx 200 17:08:17 127.0.0.1 GET /EspacePerso/ViewArticle.aspx 200 Exemple de fichier de log IIS Pour réaliser des statistiques, avec Sql Server Reporting Services par exemple, ce n’est pas ce qu’il y a de plus efficace. La solution est de transformer ce fichier de log et d’en intégrer les données en base. C’est la que SSIS intervient. Il est capable de traiter se fichier de log et d’en transformer le contenu pour l’intégrer en base. Figure 1:Procéssus de traitement des fichiers de log IIS Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 3 sur 47 Ce que va faire SSIS, c’est ouvrir le fichier de log, le découper comme il faut, et insérer en base. Nous nous servirons de cet exemple pour mieux comprendre un peu mieux comment SSIS fonctionne. Comment travailler avec SSIS ? Comme pour tout ce qui touche au développement pour SQL Server, c’est Visual Studio qui sera utilisé. Figure 2:Intégration de SSIS à Visual Studio Pour travailler avec SSIS, il vous faut créer un projet Integration Services Project classé dans la catégorie Business Intelligence Projects. Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 4 sur 47 Figure 3:Création du projet SSIS Tout d’abord, que contient le projet. Figure 4:Apperçu du Solution Explorer A sa création, le projet ne contient qu’un package : Package.dtsx. Un package est, si on peut dire, un script pour SSIS. Il contient une liste d’actions à effectuer par SSIS. Les actions disponibles se situent au niveau de la Toolbox : Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 5 sur 47 Les actions disponibles se situent au niveau de la Toolbox : Figure 5:Toolbox de Visual Studio pour un package Si vous regardez toute la liste, vous verrez que les possibilités offertes par SSIS sont déjà très nombreuses. Je ne vais pas les détailler toutes, juste celles que nous utiliserons, la liste serait beaucoup trop longue. Enfin, le dernier élément, l’espace d’édition du package : Figure 6:Espace de travail d'un Package Il se divise en 4 parties :  ControlFlow  DataFlow  Event Handlers  Package Explorer Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 6 sur 47 ControlFlow Comme tout programme ou script, un package SSIS doit suivre une certaine logique de fonctionnement. Le package doit avoir un début et une fin, exécuter des tâches dans un ordre bien particulier (lire les données avant de les traiter par exemple, ça peut aider). C’est donc dans le ControlFlow que nous allons décrire les différentes tâches d’un package. Il est possible de ne pas attribuer un ordre précis à tous les travaux, mais il est quand même bon de garder une certaine logique. DataFlow Le DataFlow est comme le ControlFlow, mais il est dédié aux données. Typiquement, dans un DataFlow, nous retrouverons, dans cet ordre, des étapes de lecture, de modification et enfin d’écriture des données. Je ne vous en dis pas plus, nous verrons cela plus en détail plus loin. Event Handlers Il est possible de gérer des évènements qui peuvent survenir dans les différents composants qui constituent un package SSIS et d’effectuer des actions en fonction de leur nature. C’est via le panneau Event Handlers que nous gèrerons ces évènements. Package Explorer Le Package Explorer est comme le Solution Explorer. Il décrit, sous forme d’arborescence, tous les éléments qui constituent le package et nous permet d’accéder en quelques clics à tous les éléments du package et à leurs propriétés. Présentation de l’exemple Comme je vous l’ai dit plus haut, nous allons automatiser l’analyse des fichiers de log de IIS (seulement la partie serveur http, ce sera largement suffisant). Structure d’un fichier de log de IIS #Software: Microsoft Internet Information Services 5.1 #Version: 1.0 #Date: 2007-05-27 07:38:39 #Fields: date time c-ip cs-username s-sitename s-computername s-ip s-port cs-method cs-uri-stem cs-uri-query sc-status sc-win32-status sc-bytes cs-bytes time-taken cs-version cs-host cs(User-Agent) cs(Cookie) cs(Referer) 2007-05-27 07:38:39 127.0.0.1 - W3SVC1 DTCBPXP 127.0.0.1 80 GET /EspacePerso/~/images/baniereperso.png - 404 3 4383 305 300 HTTP/1.1 localhost Mozilla/4.0+(compatible; +MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.N ET+CLR+3.0.04506.30) - - 2007-05-27 07:40:26 127.0.0.1 - W3SVC1 DTCBPXP 127.0.0.1 80 DEBUG /EspacePerso/DebugAttach.aspx - 401 0 351 406 3675 HTTP/1.1 localhost - - - Voici un fichier extrait de fichier de log IIS, le tout début pour être exact. Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 7 sur 47 Ce petit exemple va nous servir à comprendre sa structure. Tout d’abord, vous remarquerez les 4 premières lignes qui sont un peu particulières. Elles sont ici à titre d’information. La première ligne nous donne des informations sur la version de IIS que nous utilisons. Je suis sous Windows XP, j’ai donc IIS 5.1. La deuxième la version … de quoi je ne sais pas, mais de toutes façons, elle ne nous intéresse pas ici. La troisième ligne nous donne la date et l’heure de création du fichier. Elle n’a pas une grande utilité ici, passons. La quatrième ligne, contrairement aux 3 précédentes, est une source d’information vitale pour le travail que nous allons réaliser. Elle contient en effet une description de la formation des lignes de log. Une ligne de log à proprement parler contient toutes les valeurs que nous souhaitons tracer séparées par des espaces. IIS nous offre la possibilité, via la console d’administration, de choisir les informations sur l’activité du serveur Web que nous souhaitons conserver. Comme vous pouvez le constater, ces informations sont très complètes. Dans notre exemple, elles seront toutes utilisées. Pourquoi cette ligne est-elle si importante ? Comme vous l’aurez sans doute compris, il est possible que le log de l’activité de IIS ne nous renvoie pas toujours les mêmes informations, selon l’humeur et les besoin de l’administrateur. Il est donc important de savoir ce que nous allons récupérer comme informations. Olivier Delmotte http://odelmotte.developpez.com Copyright © 2007 Page 8 sur 47 Il faut savoir aussi que cette section de 4 lignes d’en-têtes peut se répéter plusieurs fois dans un même fichier (changements de configuration, redémarrage). Donc, à la suite de ces 4 lignes, nous récupérons les lignes de données qui nous intéressent réellement. Dans ce tutoriel, je ferais comme si la structure du fichier de log n’était pas modifiable et qu’il n’y avait qu’une seule section d’en-tête par fichier afin de ne pas rentrer dans les détails, ce n’est qu’une introduction. Afin aussi de ne pas compliquer trop les choses, nous utiliserons un unique fichier, dont nous connaissons le nom à l’avance. Tâches à réaliser par notre package Notre package d’exemple ne sera pas très compliqué. Il package effectuera les actions suivantes : 1. Copier le fichier de log dans un dossier temporaire 2. Lire le contenu du fichier 3. Effectuer quelques opérations de transformation sur les données récupérées 4. Ecrire ces données en base Stockage Pour stocker les informations de notre fichier de log, nous utiliserons une table SQL Server 2005 dont voici le script de création : CREATE TABLE [dbo].[logiis]( [id] uploads/Litterature/ introduction-a-ssis.pdf