ssis.pdf

Présentation de SQL ServerIntegration Services : SSIS

par Fleur-Anne BLAIN (fablain.developpez.com)

Date de publication : 07/11/2007

Dernière mise à jour : 07/11/2007

Ce tutoriel a pour but de vous présenter les services d'intégration de données de MicrosoftSQL Server 2005, soit SSIS.

http://www.developpez.com

http://www.developpez.net/forums/member.php?u=107845

http://fablain.developpez.com/

Présentation de SQL Server Integration Services : SSIS par Fleur-Anne BLAIN (fablain.developpez.com)

- 2 -Copyright 2007 - Fleur-Anne BLAIN. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes,documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 Ede dommages et intérets. Droits de diffusion permanents accordés à developpez LLC.

http://fablain.developpez.com/tutoriel/ssis/

Introduction.................................................................................................................................................................. 3I - Qu'est-ce que SSIS?..............................................................................................................................................3II - L'environnement SSIS........................................................................................................................................... 3

II-A - Création d'un projet SSIS.............................................................................................................................3II-B - L'espace de travail SSIS.............................................................................................................................. 4II-C - La boite à outils SSIS.................................................................................................................................. 5

III - Notre exemple de projet SSIS............................................................................................................................. 6III-A - Objectifs....................................................................................................................................................... 6III-B - Notre source de données : un fichier plat...................................................................................................7III-C - Destination de l'import : Base de données MS SQL Server....................................................................... 7

IV - Gestion des sources de données........................................................................................................................7IV-A - Création d'une tâche...................................................................................................................................7IV-B - Gestion de la source de données...............................................................................................................8

V - Transformation des données.............................................................................................................................. 12V-A - Principes de notre transformation de données..........................................................................................12V-B - Script de transformation.............................................................................................................................13

VI - Destination des données................................................................................................................................... 18VI-A - Configuration de la destination des données : Base de données MS SQL Server...................................18VI-B - Import des données et vérifications..........................................................................................................22

VII - Déploiement du package SSIS.........................................................................................................................23Conclusion................................................................................................................................................................. 27Liens utiles.................................................................................................................................................................27Remerciements..........................................................................................................................................................27








Introduction

Afin de garder un système d'information cohérent et de simplifier la vie des informaticiens, Microsoft SQL Server2005 est doté d'un nouvel outil d'intégration de données (d'une source en passant par une transformation optionnelledes données, vers une destination) appellé SSIS. Il permet de communiquer des données de systèmes d'informationdifférents ce qui représente bien les besoins actuels en terme de système d'information, de transfert de données etpermet de garder une cohérence.

Prenons le cas d'une entreprise qui dispose d'une application. Celle-ci me génère des données, dans notre cassous forme de fichier plat, que nous souhaitons intégrer dans notre base de données. Notre problématique étant derécupérer ces informations, de vérifier leurs formats s'ils correspondent ou non aux formats de données de notredestination et enfin de les intégrer.

Bien entendu, si notre application nous avait généré un autre type de source de données, cela fonctionne aussicar SSIS gère plusieurs types de sources de données tels que les fichiers plats, les fichiers Excel, XML mais nousdécouvrirons tout cela plus tard.

Nous allons explorer SSIS tout d'abord à travers une présentation. Nous nous familiariserons ensuite avecl'environnement de conception et d'utilisation de SSIS. Enfin, nous réaliserons un exemple de projet SSIS complet.

I - Qu'est-ce que SSIS?

SSIS est un outil d'extraction, de transformation et de chargement de données, en bref ce que l'on appelle un ETL.On extrait d'une source de données, puis suit la transformation si besoin, pour ensuite injecter ces données vers MSSQL Server ou encore d'autres destinations.

SSIS est l'amélioration de la version 2005 d'un outil souvent plus connu, dans les versions antérieures à 2005, sousle nom de Data Transformation Services, soit DTS.

Pré-requis :

• Microsoft Visual Studio Developper/Enterprise Edition

• Microsoft SQL Server 2005 Standard/Enterprise Edition

De plus, savoir manipuler Visual Studio et connaître quelques notions de base en base de données (en particulierles types de données existants) est un plus.

II - L'environnement SSIS

II-A - Création d'un projet SSIS

Comme il est stipulé dans les pré-requis, pour travailler avec SSIS il vous faut un Microsoft Visual Studio(fonctionnalité non disponible malheureusement avec les versions express). L'environnement de conception d'unpackage SSIS est donc Visual Studio avec, si possible, un accès à votre serveur de données. Ceci en vue au minimumde contrôler que l'import s'est bien effectué (en plus des logs de progression disponiblent avec votre package SSIS).

Pour créer votre projet, sélectionnez Business Intelligence :





http://msdn2.microsoft.com/fr-fr/vstudio/default.aspx

http://msdn2.microsoft.com/fr-fr/sql/aa336342.aspx




Créez alors votre nouveau projet de type Integration Services. Découvrons alors notre environnement de travail SSIS,que nous allons détailler :

Comme nous pouvons le constater dans l'explorateur de solution, notre projet contient dès sa création un package :package.dtx. C'est dans ce package que nous déroulerons de manière ordonnée la sélection, la transformation etl'importation des données.

II-B - L'espace de travail SSIS

L'espace de travail (central) est divisé en 4 parties sous forme d'onglet :








• Le Control Flow ou flux de contrôle : permet de contrôler, d'ordonner et dissocier les tâches à réaliser parle package.

• Le Data Flow ou flux de données : permet de contrôler, d'ordonner et dissocier les flux de données à traiter.C'est à cette étape que la sélection, la transformation et l'insertion des données sont réalisées.

• L'Event Handlers ou gestionnaire d'évènements : Des évènements peuvent être associés aux élémentsdu package. Cet onglet permet donc de gérer les évènements comme par exemple un traitement spécifiquesuite à une gestion d'erreurs.

• Le Package Explorer ou explorateur de package : permet par définition d'explorer le package à traversune arborescence. Celle-ci est très utile en particulier lors d'importants projets SSIS, ou si le package secompose de nombreux éléments.

II-C - La boite à outils SSIS

Tous nos composants de package se trouvent dans cette boite à outils. Celle-ci propose une série de composantsen fonction de l'onglet sur lequel nous sommes, logique. Regardons de plus près ces quelques extraits de notreboite à outils :

Au niveau flux de contrôles, celle-ci se trouve organisée en 2 parties comme nous pouvons le voir ci-dessous :

Divers contrôles ou tâches sont pré-existants comme les tâches de flux de données, de nettoyage d'historique ouencore de sauvegarde de base de données, etc.

Au niveau flux de données, celle-ci se trouve organisée en 3 parties : les composants source, les composantstransformation et enfin les composants destination.








Nous avons à notre disposition divers types de composants source comme les fichiers Excel, les fichiers plats,XML, etc. De même nous avons plusieurs composants transformations que nous verrons ultérieurement et enfin lescomposants destinations tels que des fichiers plats, excel ou encore SQL Server.

III - Notre exemple de projet SSIS

III-A - Objectifs

Notre objectif principal est d'importer les données d'un fichier plat généré par l'application de notre entreprise, dansnotre base de données. Pour cela, il nous faudra spécifier dans un premier temps la source de données : notre fichierplat. Puis dans un second temps, il nous faudra réaliser le script de transformation de données afin que les formatsde celles-ci soient compatibles pour l'insertion comme les dates ou encore le nombre d'enfant. Enfin, en destinationnous identifierons la base de données, plus précisément la table souhaitée que nous découvrirons plus tard.

SSIS, à travers tous ces composants, nous permet de créer un flux de données cohérent et organisé. D'ailleurs, nouspouvons voir que ce flux de données est modélisé par une flèche verte qui permet d'effectuer le lien entre chaquecomposant. Et donc, entre chacune de nos étapes avant l'import :








III-B - Notre source de données : un fichier plat

Notre source de données à intégrer est un fichier plat, contenant diverses données dont celles que nous souhaitonsintégrer. Il se présente de la manière suivante :

Fichier clients à intégrer

code|NomClient|PrenomClient|DateNaiss|NbEnfant|DateInterview

00000|Houm|Benoit|19840822|1-|200709azerty|Dupont|Nicolas|19700513|4|20070100001|Tapis|Yan|19690911|1-|20070200011|Posinello|Marisa|19801223|4|20060200002|Champ||19740228|1-|200703III22|Fiore|Sophie|19820330|2|20070300001|Tapis|Yan|19690911|1-|20071100011|Luc|Charlie|19701103|0|200703II022|Bazin|Jean-Charles|17820420|5|20070500001|Tapis|Yan|19690911|1-|20070499011|Masel|Fanny|19790108|1|200706

Ce fichier plat a ce qu'on appelle des données d'entête, ici le libellé des colonnes des données générées. Ilpossède des données que nous allons ignorer, comme la première colonne, car elle n'est pas à prendre en comptedans l'insertion. De plus, certaines données devront subir des transformations avant d'être importées car elles necorrespondent pas au format de destination. Enfin, notons que les données générées ont un séparateur "|".

III-C - Destination de l'import : Base de données MS SQL Server

Nous avons à notre disposition une base de données Microsoft SQL Server 2005 où nous allons importer les données.Ce fichier sera importé dans une table client dont voici le script de création :

Script de création de table

USE [TestSSIS]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Client]( [IdClient] [int] IDENTITY(1,1) NOT NULL, [NomClient] [nvarchar](50) COLLATE French_CI_AS NOT NULL, [PrenomClient] [nvarchar](50) COLLATE French_CI_AS NULL, [DateNaiss] [datetime] NOT NULL, [NbreEnfant] [int] NOT NULL, [DateInterview] [datetime] NOT NULL) ON [PRIMARY]

IV - Gestion des sources de données

La gestion des sources de données se compose de la création d'un tâche spécifique à notre traitement puis du choixde notre source de données et du contenu de celle-ci.

IV-A - Création d'une tâche

La première étape est de créer une nouvelle tâche pour notre traitement. C'est de l'ordre du flux de contrôle. Doncdans l'onglet flux de contrôle, nous choisissons le composant de la boite à outils Data Flow Task ou tâche de fluxde données :








Une tâche permet d'encapsuler tout un traitement de données de la source, en passant par la transformation jusqu'à ladestination. Il est intéressant de diviser en tâches chaque traitement pour une question d'organisation dans un premiertemps, mais aussi de rapidité d'exécution car l'on peut exécuter une tâche parmi d'autres de manière spécifique.Enfin, diviser en tâches est aussi intéressant lors de l'exécution du package car lorsqu'une erreur survient, cela nebloque pas l'exécution des autres tâches si celles-ci n'ont aucune erreur spécifique.

Pour créer une nouvelle tâche, faites glisser le composant sur l'espace de travail. Nous la renommerons traitementclient :

Comme dit précédemment, nous pouvons constater qu'il est possible (clic droit) d'exécuterune tâche voulue sans que cela n'influe sur les autres.

IV-B - Gestion de la source de données

Dans un premier temps, ouvrons la tâche en double cliquant dessus. Celle-ci va nous conduire dans l'onglet fluxde données comme suit :








Ensuite, il nous faut créer une source de données. Dans notre cas c'est un fichier plat donc nous allons faire glisserle composant Flat file Source ou source fichier plat sur notre espace de travail. Bien entendu si notre source dedonnées est différente, nous choisirons le composant correspondant.

Nous renommerons notre source de données en TestSSIS. Ensuite, il nous faut ouvrir la source de données afin delui affecter notre fichier et nos conditions en double cliquant sur celle-ci. Cela nous amène à cette fenêtre ou bienentendu nous allons cliquer sur new.

Ci-dessous, nous affectons notre fichier TestSSIS à notre composant source. Notons que nous avons mis à 1 lenombre de lignes d'entête à ignorer, car le libellé des colonnes (en première ligne du fichier) n'est pas une donnéeà prendre en compte :








Passons dans l'onglet colums ou colonnes de notre fenêtre. Nous pouvons constater qu'un premier aperçu desdonnées est disponible et que notre ligne d'entête a bien été ignorée. Notons aussi que SSIS est intelligent, il areconnu de lui même le séparateur de données présent dans le fichier source. Bien entendu il y en a d'autres possiblesdans la liste de sélection comme ";", tabulation ou autres.

Passons dans l'onglet advanced ou avancé de notre fenêtre. A cette étape, nous renommons, affectons un typeet une taille à nos colonnes. Nous n'effectuons cette action que pour les colonnes que nous garderons par la suite.Souvenez-vous que la première colonne du fichier (colonne 0 ici) est un code que nous ne souhaitons pas importer.Vos types de colonnes doivent être identiques aux types des colonnes de votre table si vous insérez vosdonnées dans une base de données.








Anticipons la transformation. Toutes les colonnes à transformer, dans notre cas,resteront de type String afin de les manipuler (les dates et les entiers signés) entant que chaînes de caractères dans notre script de transformation.

Nous validons le traitement du fichier puis nous retournons à la fenêtre initiale au niveau de l'onglet colums oucolonne. C'est ici que nous sélectionnons les colonnes qui nous intéressent. Par conséquent, nous décocheronsuniquement la colonne 0 :








Nous validons entièrement la connexion à notre source de données. A noter que la petite croix rouge présenteen cas d'erreur (normal à la création de la source de données) a disparu une fois celle-ci attribuée. De même,notre manager de connexion (en bas) nous indique bien que le fichier TestSSIS a été pris en compte. En cas demodification, nous pouvons accéder directement à notre fichier plat en cliquant sur le fichier du manager de connexion.

V - Transformation des données

V-A - Principes de notre transformation de données

Notre transformation consiste à passer la date de naissance du format aaaa/mm/jj au format de notre colonneDateNaiss soit jj/mm/aaaa. Nous transformerons aussi les données de notre fichier au niveau du nombre d'enfants. Le1- devra passer en entier signé -1, celui-ci signifiant que le client n'a pas répondu à cette question lors de l'interview.Enfin, nous transformerons les données concernant les dates d'interview du format aaaa/mm au format de notrebase de données à savoir jj/mm/aaaa. Le jour n'étant pas précisé mais nécessaire, nous mettrons 01 en guise dejour à toutes nos dates d'interview.

Nous réaliserons toutes ces transformations dans un même fichier de script.

Le principe de la transformation de données est d'utiliser les données du fichier en entrée de transformation, desimuler une colonne intermédiaire de sortie après la transformation. Cette dernière fera office de colonne d'entréeà l'étape de l'import des données :

Ceci sera réalisé pour chaque transformation.








V-B - Script de transformation

Dans notre cas, nous passerons par un script pour réaliser cette transformation de données. Nous glisserons lecomposant Script Component sur notre espace de travail :

Avant de commencer la transformation, nous devons relier notre fichier source de données à notre composant Script.Pour cela il suffit de faire glisser la flèche verte jusqu'a notre composant Script comme décrit ci-dessous :

Débutons la transformation de données en double cliquant sur notre composant. Nous commencerons par l'ongletcolonnes d'entrées où nous sélectionnons les colonnes à transformer et uniquement celles-ci :








Dans l'onglet Inputs and Outputs, nous pouvons retrouver en entrée les colonnes sélectionnées précédemment :

Nous ajoutons une colonne intermédiaire pour chaque colonne à transformer. Nos colonnes transformées porterontle nom T_nomdecolonne afin de les dissocier :








En plus du nom, il nous faut modifier le type de chaque colonne transformée. Celui-ci doit correspondre au type de nos colonnes de base de données (datetime, int,etc.).

Après cela, nous pouvons passer concrètement au script en se rendant sur l'onglet Script puis Design Script :








Une nouvelle fenêtre Visual Studio For Applications s'ouvre. C'est la où nous allons programmer notre script detransformation. A noter que ce script est en VB.NET.

Voici le code, par exemple, pour manipuler nos données dans notre cas :








Script de transformation

' Microsoft SQL Server Integration Services user script component' This is your new script component in Microsoft Visual Basic .NET' ScriptMain is the entrypoint class for script components

Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' ' Add your code here ' 'Transformation de la date de naissance

If Not Row.DateNaiss_IsNull Then

Dim annee As Integer = Integer.Parse(Row.DateNaiss.Substring(0, 4)) Dim mois As Integer = Integer.Parse(Row.DateNaiss.Substring(4, 2)) Dim jour As Integer = Integer.Parse(Row.DateNaiss.Substring(6, 2))

Row.TDateNaiss = New DateTime(annee, mois, jour)

End If

'Transformation du nombre d'enfant

If Not Row.NombreEnfant_IsNull Then Dim isNegativ As Boolean = Row.NombreEnfant.Contains("-") Dim nb As String = Row.NombreEnfant.Replace("-", String.Empty) Dim res As Integer = Integer.Parse(nb) If isNegativ Then res = -res End If Row.TNombreEnfant = res End If

'Transformation de la date d'interview If Not Row.DateInterview_IsNull Then

Dim annee As Integer = Integer.Parse(Row.DateInterview.Substring(0, 4)) Dim mois As Integer = Integer.Parse(Row.DateInterview.Substring(4, 2)) Dim jour As Integer = 1

Row.TDateInterview = New DateTime(annee, mois, jour)

End If

End Sub

End Class

Bien entendu, ce type de transformation reste un exemple, même sur le principe. En effet, ilest aussi possible selon les circonstances d'utiliser ce qu'on appelle les colonnes dérivées,avec lequel est fourni un assistant de manipulation de données si vous n'êtes pas trèsà l'aise avec le VB.net. Cet assistant regroupe les principales fonctions connues sur lesdates, les chaînes de caractères ou autres.








VI - Destination des données

VI-A - Configuration de la destination des données : Base de données MS SQL Server

Nous avons géré notre source de données, nous avons programmé notre script de transformation, maintenantnous allons configurer l'import. Notre cible est une table client d'une base de données MS SQL Server 2005. Nousutiliserons, du fait de la base de données Microsoft SQL Server, le composant SQL Server Destination.

Nous faisons donc glisser le composant SQL Server Destination lui même relié à notre script (flèche verte), que nousallons configurer en double cliquant sur celui-ci :








Remplir les éléments demandés reste assez intuitif. Cependant, si vous ne disposez pas au premier lancement devotre chaîne de connexion, il vous est possible d'en créer une comme suit :

Il nous faut sélectionner le serveur, le type d'authentification choisie ainsi que la base de données voulue.








Dans l'onglet manager de connexion, nous spécifions la connexion à utiliser ainsi que la table destination de notrebase de données. Dans notre cas, la table client :

Au niveau de l'onglet Mapping, SSIS arrive à associer les bonnes colonnes entre elles. Mais concernant les colonnestransformées, il faut les spécifier nous même comme ci-dessous :








D'autres possibilités sont offertes dans l'onglet Advanced :








A noter que notre manager de connexion, en plus de notre fichier nous a ajouté (en bas)notre connexion à notre base de données. Il suffit de modifier cette donnée pour que lamodification de destination s'effectue dans tout le package.

VI-B - Import des données et vérifications

Pour l'import des données, soit il nous faut cliquer droit sur la tâche de l'onglet fulx de contrôle afin de l'exécuter(en particulier si nous ne souhaitons pas l'exécution d'autres tâches en même temps), soit nous exécutons tout lepackage (F5). Si l'exécution se déroule correctement, tout s'affiche en vert et le nombre de lignes insérées est visible :

En cas d'erreur, le composant en erreur ou bloquant s'affiche en rouge. Il est possible de voir le détail de cette erreurdans un nouvel onglet qui est apparu lors de l'exécution : l'onglet Progress. Cet onglet déroule étape par étapel'exécution et donne les messages d'erreurs.

La première vérification possible dans Visual Studio est donc de voir tous nos composants s'orner de vert. Mais nouspouvons aussi vérifier dans notre base de données si l'insertion a bien été effective. Voici l'état de notre table avantexécution de notre package SSIS :

Voici notre table client après exécution de notre package SSIS :








VII - Déploiement du package SSIS

Comme nous l'avons fait précédemment, l'exécution du package est possible via Visual Studio (F5). Cependant, undéploiement du package sur le serveur est plus avantageux, en particulier pour automatiser l'exécution.

La première étape étant l'activation de la création de l'utilitaire de déploiement de notre package. Pour cela, nousallons cliquer droit afin d'atteindre les propriétés du projet. Dans l'onglet Deployement Utility, mettre la propriétéCreatDeploiementUtility à vrai comme suit :

Nous validons donc cette modification. Ensuite, il nous faut regénérer le projet. Nous vérifions que la modification abien été prise en compte dans notre projet Visual Studio comme suit :








Il y a bien un dossier deployement, qui lui-même contient ce qui nous intéresse, à savoir le fichierSSIS_tutoriel.SSISDeploymentManifest dans notre cas. Afin de débuter le déploiement, nous allons ouvrir ce fichierqui va nous diriger vers un assistant de déploiement de package.

Dans notre cas, nous choisirons File System Deployement. Selon vos besoins et suivant la description qui peut vousaider, vous devrez choisir l'un ou l'autre.

Continuons l'assistant. Nous laissons le chemin par défaut pour le déploiement de notre package. Puis nouscontinuons.








Nous arrivons sur une fenêtre intermédiaire nous demandant de confirmer. Bien entendu nous confirmons :

Nous arrivons sur une fenêtre récapitulative avec diverses informations :








Voilà, grâce à cet assistant, nous venons de deployer notre package. Vérifions que tout s'est correctement déroulé.Nous nous connecterons à notre serveur à l'aide d'SQL Server Management Studio en sélectionnant IntegrationServices en type de serveur :

Notre package existe bien dans notre arborescence. C'est déjà une bonne nouvelle. Nous allons l'exécuter (clic droitRun Package) afin de constater que nous avons le même résultat :








Après exécution, tout s'est déroulé comme prévu, les 11 lignes de notre fichier à importer sont correctement arrivéesà destination. Et si nous ne sommes toujours pas certains, il suffit de vérifier les données dans notre table.

Conclusion

Nous venons de parcourir quelques fonctionnalités vitales pour la création d'un package SSIS. Bien entendu, il suffitde regarder de plus près la boîte à outils pour constater qu'il en existe de nombreuses autres.

Nous avons traité de SSIS et de son déploiement en "manuel". Cependant, si vous souhaitez automatiser l'exécutiondu package (en batch), il vous faudra vous pencher davantage sur les fonctionnalités du service SQL Server Agent,qui permet de régler très facilement une fréquence, une période, etc. d'exécution.

Liens utiles

• Microsoft Visual Studio Developper/Enterprise Edition

• Microsoft SQL Server 2005 Standard/Enterprise Edition

• Microsoft TechNet SQL Server Integraiton Services

Remerciements

Je remercie Dut et Pedro pour leurs corrections ainsi que pcaboche et Ditch pour leurs remarques constructives.





http://msdn2.microsoft.com/fr-fr/vstudio/default.aspx

http://msdn2.microsoft.com/fr-fr/sql/aa336342.aspx

http://technet.microsoft.com/fr-fr/sqlserver/bb331782.aspx





Documents

ssis.pdf