Introduction à Sql Server Integration Services (SSIS)

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.

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.

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.

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.

Voici un fichier extrait de fichier de log IIS, le tout début pour être exact.

#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;+.NET+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 - - -

Ce petit exemple va nous servir à comprendre sa structure. Tout d'abord, vous remarquerez les quatre 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 ligne nous renseigne sur la version … de quoi je ne sais pas, mais de toute façon, elle ne nous intéresse pas ici.

La troisième ligne nous retourne la date et l'heure de création du fichier. Elle n'a pas une grande utilité pour le traitement qui nous intéresse.

La quatrième ligne, contrairement aux trois 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.

Revenons sur la 4e ligne. 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 les besoins de l'administrateur. Il est donc important de savoir ce que nous allons récupérer comme information.

Il faut savoir aussi que cette section de quatre lignes d'en-tête peut se répéter plusieurs fois dans un même fichier (changements de configuration, redémarrage).

Donc, à la suite de ces quatre lignes, nous récupérons les lignes de données qui nous intéressent réellement. Dans ce didacticiel, je ferai 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. Afin aussi de ne pas compliquer trop les choses, nous utiliserons un fichier unique dont nous connaissons le nom à l'avance.

Notre package d'exemple ne sera pas très compliqué. Il effectuera les actions suivantes :

• copier le fichier de log dans un dossier temporaire ;
• lire le contenu du fichier ;
• effectuer quelques opérations de transformation sur les données récupérées ;
• écrire ces données dans la base.

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] [int] IDENTITY(1,1) NOT NULL,
    [dateandtime] [datetime] NULL,
    [ipclient] [nvarchar](15) NULL,
    [username] [nvarchar](50) NULL,
    [sitename] [nvarchar](50) NULL,
    [computername] [nvarchar](50) NULL,
    [ipserver] [nvarchar](15) NULL,
    [portserver] [int] NULL,
    [cliservmethod] [nvarchar](5) NULL,
    [cliservuristem] [nvarchar](512) NULL,
    [cliservuriquery] [nvarchar](512) NULL,
    [servclistatus] [int] NULL,
    [servcliwinstatus] [int] NULL,
    [servclibytes] [int] NULL,
    [cliservbytes] [int] NULL,
    [timetaken] [int] NULL,
    [cliservversion] [nvarchar](50) NULL,
    [cliservhost] [nvarchar](512) NULL,
    [cliservuseragent] [nvarchar](512) NULL,
    [cliservcookie] [nvarchar](512) NULL,
    [cliservreferer] [nvarchar](512) NULL
) ON [PRIMARY]

Comme je vous en ai déjà parlé pour la copie du fichier, SSIS travaille avec des connexions pour accéder aux supports "physiques". La première chose à faire est donc d'ajouter une connexion à notre fichier de log.

5-1-1. La connexion au fichier de log▲

Si vous jetez un œil en bas du panneau Data Flow, vous verrez un espace nommé Connection Managers dans lequel vous retrouverez les deux connexions utilisées pour la copie du fichier de log :

Un petit clic droit laissera apparaître le menu suivant dans lequel vous choisirez New Flat File Connection… :

L'écran suivant apparaît dans lequel nous allons configurer notre connexion :

Je ne vais pas tout détailler, je pense que l'écran est assez clair pour que vous puissiez le remplir seul. Voici quand même à quoi il doit ressembler :

Passez maintenant à la section Columns, c'est la que nous allons enfin voir le contenu de notre fichier de log en le configurant comme suit :

Pour Column Delimiter, spécifiez bien un espace (désolé, ce n’est pas très visible sur la capture !).

Dans la section Advanced, il est possible de définir les options avancées des colonnes que nous venons de voir :

Il est donc possible de modifier chaque colonne, de lui donner un nom compréhensible - par la suite, vous verrez que ça peut se révéler très utile - son type de sortie, très pratique. Voici à quoi cette section ressemble après configuration :

Pour la taille des colonnes de sortie, donnez la même taille que pour la taille du champ correspondant dans la table de la base. Validez : notre connexion est prête. Il ne nous reste plus qu'à visualiser nos données dans le Data Flow.

5-1-2. Flat File Source▲

Maintenant que nous avons configuré notre connexion au fichier de log, il faut rendre accessibles ces données au package. C'est la qu'intervient le composant Flat File Source. Comme son nom l'indique, c'est une source de données.

Comme vous pouvez le constater, l'objet est en erreur. Si on y réfléchi deux secondes, c'est logique : son rôle est de rendre accessible des données au reste des composants du Data Flow. Mais quelles données ? Comme il n'est pas configuré, il ne sait pas lui-même où sont les données qu'il doit présenter. Un double clic sur le composant vous permettra d'accéder à son écran de configuration :

L'assistant détecte automatiquement la connexion appropriée au type de notre composant. Un composant Flat File Source prend logiquement une connexion de type Flat File. Si vous regardez la section Columns de cet écran de configuration, vous verrez qu'il intègre bien les colonnes définies dans la connexion :

Terminons la configuration, sans rien modifier, en cliquant sur OK.

Notre composant n'est plus en erreur. Passons à la suite.

5-1-3. Création de la colonne DateTime▲

Si vous avez été bien attentif, vous avez sans doute remarqué que pour les informations de date et d'heure, la structure du fichier de log nous renvoie 2 colonnes et que la structure de notre table nous propose une unique colonne de type DateTime.

Nous allons donc devoir transformer les informations fournies pas notre source afin de créer une nouvelle colonne de type DateTime à partir de deux colonnes de type Texte.

L'opération est la suivante.

Concaténer "jj/mm/aaaa" et "hh :mm :ss" en une chaine "jj/mm/aaaa hh :mm :ss" et la convertir en date.

Rassurez-vous, rien de bien compliqué là-dedans.

Ce genre d'opération est réalisé par un composant de type Data Flow Transformation : Derived Column. Déposez donc un composant Derived Column sur l'espace de travail.

Pour pouvoir créer une colonne à partir d'un autre, il faut bien sûr qu'il sache les colonnes qu'il a à sa disposition. Pour cela, nous allons relier notre composant Flat File Source à notre composant Derived Column.

Cette opération est très simple et valable pour tous les composants qu'utilise SSIS, aussi bien dans un Control Flow que dans un Data Flow. Commencez par sélectionner le composant Flat File Source. Un flèche verte apparaît en dessous, c'est celle-là qui nous intéresse. Elle représente le flux de données. La rouge représente le flux d'erreur. Nous ne nous en servirons pas ici. Pour connecter deux composants donc, il faut faire glisser la flèche verte du composant source sur le composant de destination. Vous devriez obtenir quelque chose qui ressemble à ceci dans notre cas :

Dans un Data Flow, cette flèche signifie que le composant d'où part la flèche fournit des données au composant pointé par la flèche. Après ces quelques petites précisions, voyons maintenant comment créer notre date pour l'exploiter par la suite. Double cliquez sur le composant Derived Column. L'écran de configuration suivant apparaît :

Il se divise en trois parties :

• les sources de données (nous mettrons de côté Variables qui dépassent le cadre de ce didacticiel) ;
• les opérations : mathématiques, sur les dates et … oh ben tiens, de conversion ;
• les colonnes dérivées.

Nous allons créer une colonne dérivée qui sera le résultat de Expression. Notez que vous pouvez créer plusieurs colonnes dérivées en une seule fois.

Ici nous allons créer la colonne dérivée "dateandtime" qui sera le résultat de l'expression suivante "(DT_DBTIMESTAMP)(DT_DATE)(date +" "+ time)".

L'expression reprend la syntaxe du C#, on concatène les colonnes date et time avec un espace entre les deux, et on converti successivement le résultat en type DT_DATE puis DT_DBTIMESTAMP.

Nous disposons maintenant d'une colonne supplémentaire dans nos données, appelée dateandtime et de type DT_DBTIMESTAMP qui sera parfaite pour la colonne dateandtime de notre table en base.

Passons à la conversion des données.

5-1-4. Conversion des données▲

Pourquoi convertir les données ?

Si vous regardez bien la connexion au fichier de log, vous apercevrez une option Unicode, qui est décochée. Si maintenant vous regardez bien la structure de notre table, vous verrez que les champs de type text sont de type nvarchar, donc Unicode. Comme SSIS est assez pointilleux au niveau des types de données, nous allons devoir convertir les données non Unicodes de notre fichier texte en données Unicodes pour pouvoir les insérer en base. Mais encore une fois, SSIS nous propose un composant qui va se charger de réaliser la conversion pour nous, c'est le composant Data Conversion qui intervient :

Comme pour le composant Derived Column, pour savoir ce qu'il peut convertir, nous devons lui fournir des données. Nous allons donc relier la sortie de notre composant Derived Columns (flèche verte) à notre composant Data Conversion :

Comme d'habitude, ouvrez l'éditeur du composant Data Conversion :

Toutes les colonnes qui seront affectées par cette conversion sont les colonnes de type texte. Sélectionnez-les dans les colonnes Available Input Columns : elles apparaîtront dans la liste en dessous. Modifiez le Data Type en Unicode String [DT_WSTR]. Faites bien attention que la longueur de la chaine corresponde bien à la taille du champ dans la table de la base. Une fois terminé, vous devriez obtenir ceci :

Terminez l'édition en validant.

Nous allons maintenant passer à l'insertion en base.

Comme nous utilisons une base SQL Server, nous utiliserons le composant SQL Server Destination. Vous pouvez bien sûr utiliser d'autres destinations, comme un fichier texte, ou un autre SGBD, mais ceci n'est pas notre cas de figure actuel.

Le composant est en erreur. Normal, il ne sait pas où il doit se connecter pour écrire. Qu'à cela ne tienne, on va lui fournir une connexion. Mais avant, nous allons quand même lui fournir les colonnes de sortie du composant de conversion :

5-2-1. Une connexion à la base▲

Dans le Connexion Manager que nous avons vu tout à l'heure, ajoutez une nouvelle connexion ADO.Net.

L'assistant vous propose une liste des connexions disponibles. Si vous n'en avez pas, ce qui est sûrement le cas, créez-en une nouvelle :

Validez le tout. Nous avons donc maintenant notre connexion à notre serveur SQL Server.

5-2-2. Liaison de nos données à la base▲

Maintenant que nous avons tout préparé, nous allons associer notre connexion à notre composant et vérifier que tout est bon. Double cliquez sur le composant SQL Server Destination :

Si vous avez plusieurs connexions, choisissez celle qui correspond. Spécifiez ensuite la table de destination, ici logiis. Nous allons maintenant mapper les colonnes fournies depuis le fichier avec les colonnes de la table :

L'assistant est un minimum intelligent. Il associe par défaut les colonnes sources et les colonnes de destination ayant le même nom. À nous de faire le reste.

Id n'est associé à aucune colonne source puisque c'est une colonne auto incrémentée. Attention aussi à bien utiliser les colonnes résultant de la conversion de données et non pas celles d'origine, leur format n'est pas correct.

Une fois le mappage réalisé sur toutes les colonnes de destination … on va tenter d'exécuter notre Data Flow.

Cette étape va nous permettre de faire une petite analyse de notre Data Flow et de voir si tout se déroule correctement.

Toujours dans le panneau de travail du Data Flow, ouvrez le menu contextuel du Data Flow et choisissez la commande Execute Task.

Et là, drame. Erreur dès la première étape. Ne vous inquiétez pas, c'est normal. Quand je vous disais que SSIS était pointilleux … Si vous regardez dans l'onglet Progress, vous obtiendrez une liste très détaillée de ce qu'à fait SSIS lors de l'exécution du package et surtout les erreurs qu'il a rencontrées :

Vous voyez qu'il y a quelques erreurs. Rien de dramatique, et surtout pas réellement une erreur.

Le souci est que lors de la lecture du fichier, SSIS suspecte un troncage des données. Comme nous avons bien étudié la question avant, nous savons que c'est peu probable. Nous allons donc forcer SSIS à passer outre cette erreur. Éditez le composant Flat File Source (double clic), et rendez-vous dans la section Error Output :

Cette section de configuration va spécifier le comportement du composant en cas d'erreur. Ici, vous voyez qu'en cas de tentative de troncage des données, SSIS considère que le composant est en erreur et que l'exécution ne peut pas continuer. Nous allons donc dire à SSIS d'ignore l'erreur dans ce cas et pour toutes les colonnes :

Validez et retentez d'exécuter le Data Flow. Cette fois-ci une erreur lors de la création de la colonne dateandtime dans le composant Derived Column. Même opération que pour le Flat File Source. Nous allons ignorer l'erreur.

Cette fois-ci, tout se passe bien :

Nous avons lu et écrit 472 lignes de log. Petite vérification des données dans la table :

Notre Data Flow est maintenant prêt, il ne nous reste plus qu'à terminer notre Control Flow.

Nous avons vu qu'il était possible d'exécuter le package depuis Visual Studio. C'est bien, mais ce n'est pas très pratique de devoir lancer Visual Studio pour lancer notre traitement. Il nous faut donc maintenant déployer notre package dans SQL Server.

Ne vous faites pas de soucis, c'est encore Visual Studio qui va travailler pour nous et va tout faire tout seul ou presque.

La première étape du déploiement est d'activer la création de l'utilitaire de déploiement du package. Allez dans les propriétés du projet, onglet Deployment Utility :

Vous devez activer l'option CreateDeploymentUtility. Validez et régénérez le projet. Si vous vous rendez dans le dossier de sortie du projet, vous devriez avoir quelque chose qui ressemble à cela :

Il y a bien notre package, IISLogImport.dtsx. Le deuxième est le Manifeste de déploiement, IISStatistics.SSISDeploymentManifest. Ouvrez-le, un assistant de déploiement démarre.

Comme beaucoup d'assistants chez Microsoft, il y a une petite page d'accueil, passons.

L'assistant nous propose deux modes d'installation pour notre package. Si vous regardez la description de chacun des modes, vous comprendrez que pour notre exemple, le déploiement dans le système de fichier est bien suffisant.

Choisissez l'endroit où sera copié le package. L'emplacement par défaut ira très bien dans notre cas.

Petite confirmation avant l'installation, puis bouton Suivant.

Le déploiement s'est bien passé, nous avons fini. Vérifions maintenant notre travail.

Pour vérifier notre travail, lancez SQL Server Management Studio et connectez vous à Integration Services, pas au serveur de base de données comme vous le faîte sans doute d'habitude :

Si vous développez l'arborescence du serveur, vous retrouverez bien notre package.

Nous allons maintenant l'exécuter :

Dans le menu contextuel du package, choisissez la commande Run Package.

L'assistant qui s'ouvre nous propose différentes options. Je ne détaillerai rien, les options par défaut sont correctes. Nous allons nous contenter de lancer l'exécution du package. La fenêtre suivante s'ouvre et vous pouvez suivre l'exécution du package :

Vous pouvez voir que 472 lignes de log ont été écrites en base. Notre exécution s'est bien déroulée.

Merci à Fadace pour la correction ainsi qu'à toute l'équipe de developpez.com.