Présentation de SQL Server Integration Services : 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'un package SSIS est donc Visual Studio avec, si possible, un accès à votre serveur de données. Ceci en vue au minimum de contrôler que l'import s'est bien effectué (en plus des logs de progression disponibles avec votre package SSIS).

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

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 et l'importation des données.

L'espace de travail (central) est divisé en quatre 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 par le 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éments du package. Cet onglet permet donc de gérer les évènements par exemple un traitement spécifique suite à une gestion d'erreurs.

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

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

Au niveau flux de contrôles, celle-ci se trouve organisée en deux 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 ou encore de sauvegarde de base de données, etc.

Au niveau flux de données, celle-ci se trouve organisée en trois parties : les composants source, les composants transformation 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 les composants destinations tels que des fichiers plats, Excel ou encore SQL Server.

Notre objectif principal est d'importer les données d'un fichier plat généré par l'application de notre entreprise, dans notre base de données. Pour cela, il nous faudra spécifier dans un premier temps la source de données : notre fichier plat. Puis dans un second temps, il nous faudra réaliser le script de transformation de données afin que les formats de celles-ci soient compatibles pour l'insertion comme les dates ou encore le nombre d'enfants. Enfin, en destination nous 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, nous pouvons voir que ce flux de données est modélisé par une flèche verte qui permet d'effectuer le lien entre chaque composant. Et donc, entre chacune de nos étapes avant l'import :

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

code|NomClient|PrenomClient|DateNaiss|NbEnfant|DateInterview

00000|Houm|Benoit|19840822|1-|200709
azerty|Dupont|Nicolas|19700513|4|200701
00001|Tapis|Yan|19690911|1-|200702
00011|Posinello|Marisa|19801223|4|200602
00002|Champ||19740228|1-|200703
III22|Fiore|Sophie|19820330|2|200703
00001|Tapis|Yan|19690911|1-|200711
00011|Luc|Charlie|19701103|0|200703
II022|Bazin|Jean-Charles|17820420|5|200705
00001|Tapis|Yan|19690911|1-|200704
99011|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. Il possède des données que nous allons ignorer, comme la première colonne, car elle n'est pas à prendre en compte dans l'insertion. De plus, certaines données devront subir des transformations avant d'être importées, car elles ne correspondent pas au format de destination. Enfin, notons que les données générées ont un séparateur « | ».

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 :

USE [TestSSIS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]

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. Donc dans l'onglet flux de contrôle, nous choisissons le composant de la boite à outils Data Flow Task ou tâche de flux de données :

Une tâche permet d'encapsuler tout un traitement de données de la source, en passant par la transformation jusqu'à la destination. Il est intéressant de diviser en tâches chaque traitement pour une question d'organisation dans un premier temps, 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 ne bloque 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 traitement client :

Dans un premier temps, ouvrons la tâche en double cliquant dessus. Celle-ci va nous conduire dans l'onglet flux de 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 glisser le composant Flat file Source ou source fichier plat sur notre espace de travail. Bien entendu si notre source de donné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 de lui affecter notre fichier et nos conditions en double cliquant sur celle-ci. Cela nous amène à cette fenêtre ou bien entendu nous allons cliquer sur new.

Ci-dessous, nous affectons notre fichier TestSSIS à notre composant source. Notons que nous avons mis à 1 le nombre 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 des données est disponible et que notre ligne d'entête a bien été ignorée. Notons aussi que SSIS est intelligent, il a reconnu de lui-même le séparateur de données présent dans le fichier source. Bien entendu il y en a d'autres possibles dans la liste de sélection comme « ; », tabulation ou autres.

Passons dans l'onglet advanced ou avancé de notre fenêtre. À cette étape, nous renommons, affectons un type et 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 vos données dans une base de données.

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

Nous validons entièrement la connexion à notre source de données. À noter que la petite croix rouge présente en 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 de modification, nous pouvons accéder directement à notre fichier plat en cliquant sur le fichier du manager de connexion.

Notre transformation consiste à passer la date de naissance du format aaaa/mm/jj au format de notre colonne DateNaiss soit jj/mm/aaaa. Nous transformerons aussi les données de notre fichier au niveau du nombre d'enfants. Le 1- 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 notre base de données à savoir jj/mm/aaaa. Le jour n'étant pas précisé, mais nécessaire, nous mettrons 01 en guise de jour à 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, de simuler 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.

Dans notre cas, nous passerons par un script pour réaliser cette transformation de données. Nous glisserons le composant 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'onglet colonnes 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 porteront le nom T_nomdecolonne afin de les dissocier :

Après cela, nous pouvons passer concrètement au script en nous 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 de transformation. À noter que ce script est en VB.NET.

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

' 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 System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports 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'enfants

        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

Nous avons géré notre source de données, nous avons programmé notre script de transformation, maintenant nous allons configurer l'import. Notre cible est une table client d'une base de données MS SQL Server 2005. Nous utiliserons, 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 nous allons 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 de votre 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 notre base 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 colonnes transformées, il faut les spécifier nous même comme ci-dessous :

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

Pour l'import des données, soit il nous faut cliquer droit sur la tâche de l'onglet flux 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 le package (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 erreur dans un nouvel onglet qui est apparu lors de l'exécution : l'onglet Progress. Cet onglet déroule étape par étape l'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 nous pouvons aussi vérifier dans notre base de données si l'insertion a bien été effective. Voici l'état de notre table avant exécution de notre package SSIS :

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